如何在VBA中的引号之间提取字符串 [英] How to extract strings in between quotes in VBA
问题描述
<预风格="填充:5px的;边界:0像素;字体大小:13.63636302947998px;垂直对齐:基线;背景色:#eeeeee;字体家庭:索拉,万络,摩纳哥,‘龙力控制台’ ,'Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif; width:auto; max-height:600px;自动换行:正常;行高:17.804800033569336px ">
<代码风格="保证金:0像素;填充:0像素;边界:0像素;字体大小:13.63636302947998px;垂直对齐:基线;字体家庭:索拉,万络,摩纳哥,"龙力控制台", '解放单声道', '幻觉记忆国界单', '档案维拉国界单', '宋体',等宽,衬线;空格:继承">字段
DLBL GPFGBSM,"PHGP。 GPFGBSM.GBS.KSDS',, VSAM,CAT = PCAT2
DLBL GPFGGIM,'PHGP.GPFGGIM.GGI.KSDS',VSAM,CAT = PCAT2
36 DLBL GPFEXT1,'PHGP.GPFPWO4.PWO .ESDS',, VSAM,CAT = PCAT3
DLBL FILE01,'PHGP。 GPFPWO4.PWO.ESDS" ,, VSAM,CAT = pCAT3启动
DLBL FILE1 'PHGP.GPFPWO4.PWO.ESDS' ,, VSAM,DISP =(NEW,DELETE)
我有一个电子表格,其中包含A列下每个单元格内的许多文件名
(如这里所示,还有其他的文件名以及)跨度>的现在我必须只写''之间的部分。
例如:我要替换仅文件名即PHGP.GPFGBSM.GBS.KSDS 跨度>
<跨度风格=每个细胞"。字体家庭:Arial,'Liberation Sans','DejaVu Sans',sans-serif;字体大小:13.63636302947998px "?>这是可能的跨度>
<跨度风格=" 字体-family:宋体,"解放三世," dejavu的三世,无衬线;字体大小:13.63636302947998px;行高:17.804800033569336px ">到目前为止,我已经尝试过这样的:
的
Sub Search3()Dim MatchString As String
Dim matchstrin2 As String
Dim counter As Variant
Dim Name As String
Dim Datain As String
MatchString =" DLBL"
MatchString2 =" DLBL "
for counter = 1 To Range(" A:A" )。数量
数据=范围("A"和"计数器".Value
如果(InStr(1,Datain,MatchString)> 0或InStr(1,Datain,MatchString2)> 0 )然后
数据=拆分(Datain,vbLf)
cnt = UBound(数据)
对于I = 0到cnt
如果数据(I)喜欢"* DLBL *",那么 openPos = InStr(数据,"")
closePos = InStr(数据,"'")
midbit = Mid(数据,openPos + 1,closePos - openPos - 1)
; 范围("B"和&柜台).Value = midbit
'下一个 &n BSP; &NBSP; &NBSP;结束如果
&NBSP; &NBSP; &NBSP;&NBSP;
&NBSP; &NBSP; &NBSP; &NBSP;接着
&NBSP; &NBSP; midbit =""
结束如果
下一个计数器终止子
它给我类型不匹配错误。请帮助
我不认为我会这样做,但是如果你的语法正确并消除了拼写错误就应该有效
Sub Search3()
$
Dim MatchString As String
Dim MatchString2 As String
Dim counter As Long,i As Long
Dim openPos As Long
Dim closePos As Long
Dim midbit As String
Dim Name As String
Dim DataIn As String
Dim vData As Variant
MatchString =" DLBL"
MatchString2 =" DLBL "
$
对于counter = 1 To Range("A"& Rows.Count).End(-4162).Row
DataIn =范围("A"和&柜台).Value
如果InStr(1,DataIn,MatchString)> 0或InStr(1,DataIn,MatchString2)> 0然后
vData =拆分(DataIn,vbLf)
对于i = 0到UBound(vData)
如果vData(i)喜欢"* DLBL *",然后是
openPos = InStr函数(1,VDATA(i)中,"'")
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; closePos = InStr函数(openPos + 1,VDATA(i)中,"'")
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; midbit =中(VDATA(i)中,openPos + 1,closePos - openPos - 1)
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;范围(" B"&安培;计数器)。价值= midbit
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;结束如果
下一个
midbit =""
结束如果是
下一个柜台
结束子
COLUMN A DLBL GPFGBSM,'PHGP.GPFGBSM.GBS.KSDS',,VSAM,CAT=PCAT2 DLBL GPFGGIM,'PHGP.GPFGGIM.GGI.KSDS',,VSAM,CAT=PCAT2 36 DLBL GPFEXT1,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,CAT=PCAT3 DLBL FILE01,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,CAT=PCAT3 DLBL FILE1,'PHGP.GPFPWO4.PWO.ESDS',,VSAM,DISP=(NEW,DELETE)
I have a spreadsheet which contains many file names inside each single cell under column A
(as shown here,there are other file names as well).Now I must write only the part between ' '.
for example : I want to replace each cells with only the file name i.e. PHGP.GPFGBSM.GBS.KSDS.
Is that possible?
So far I have tried this:
Sub Search3()
Dim MatchString As String
Dim matchstrin2 As String
Dim counter As Variant
Dim Name As String
Dim Datain As String
MatchString = "DLBL"
MatchString2 = "DLBL "
For counter = 1 To Range("A:A").Count
Datain = Range("A" & counter).Value
If (InStr(1, Datain, MatchString) > 0 Or InStr(1, Datain, MatchString2) > 0) Then
Data = Split(Datain, vbLf)
cnt = UBound(Data)
For I = 0 To cnt
If Data(I) Like "*DLBL*" Then
openPos = InStr(Data, "")
closePos = InStr(Data, "'")
midbit = Mid(Data, openPos + 1, closePos - openPos - 1)
Range("B" & counter).Value = midbit
'Next
End If
Next
midbit = ""
End If
Next counter
End SubIts giving me type mismatch error.Please help
I don't think I would have done it that way, but if you get the syntax right and eliminate the typos it should work
Sub Search3()
Dim MatchString As String
Dim MatchString2 As String
Dim counter As Long, i As Long
Dim openPos As Long
Dim closePos As Long
Dim midbit As String
Dim Name As String
Dim DataIn As String
Dim vData As Variant
MatchString = "DLBL"
MatchString2 = "DLBL "
For counter = 1 To Range("A" & Rows.Count).End(-4162).Row
DataIn = Range("A" & counter).Value
If InStr(1, DataIn, MatchString) > 0 Or InStr(1, DataIn, MatchString2) > 0 Then
vData = Split(DataIn, vbLf)
For i = 0 To UBound(vData)
If vData(i) Like "*DLBL*" Then
openPos = InStr(1, vData(i), "'")
closePos = InStr(openPos + 1, vData(i), "'")
midbit = Mid(vData(i), openPos + 1, closePos - openPos - 1)
Range("B" & counter).Value = midbit
End If
Next
midbit = ""
End If
Next counter
End Sub
这篇关于如何在VBA中的引号之间提取字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!