say this is the text: TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q)The requirement is not clear to me from that description and one example. Could you give, say, 5 varied examples and the expected result for each and add any further clarification that you can?
To me, with your last example, removing the "stuff" in the bracket, including 0Q would leave:remove the stuff in the bracket including 0Q
21 10 22.xlsm | ||||
---|---|---|---|---|
A | B | |||
1 | AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) | ||
2 | TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | ||
Text Replacement |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1:B2 | B1 | =LEFT(A1,FIND(";",A1))&TRIM(RIGHT(SUBSTITUTE(A1,";",REPT(" ",50)),50)) |
Function NoZeroQ(S As String) As String
Dim Arr As Variant
Arr = Split(Replace(S, "(", ")"), ")")
Arr(1) = Join(Filter(Split(Arr(1), ";"), "0Q", False), ";")
NoZeroQ = Replace(Join(Arr, ")"), ")", "(", , 1)
End Function
Hi, with your formula, it works on the text: TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q)One more example is hardly 5.
Your written description is still not clear
To me, with your last example, removing the "stuff" in the bracket, including 0Q would leave:
TOTAL111Q()
yet that is obviously not what you want.
Looking at your two examples, is this your requirement?
"Remove everything in the bracket apart from the first term, the last term and one semicolon"?
If so, try these (but I cannot see that it has anything to do with "including "0Q" )
21 10 22.xlsm
A B 1 AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) AA111Q(BB222Q;EE333Q) 2 TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) TOTAL111Q(PETER222Q;LILLY333Q) Text Replacement
Cell Formulas Range Formula B1:B2 B1 =LEFT(A1,FIND(";",A1))&TRIM(RIGHT(SUBSTITUTE(A1,";",REPT(" ",50)),50))
AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) |
TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) |
TOTAL111Q(PETER222Q;BILL0Q;LILLY333Q;MIKE0Q) | TOTAL111Q(PETER222Q;LILLY333Q) |
total110Q(ALICE55Q;JIM0Q;WOOD55Q) | total110Q(ALICE55Q;WOOD55Q) |
total110Q(ALICE0Q;JIM0Q;WOOD110Q) | total110Q(WOOD110Q) |
Which is exactly why I asked for ..Hi, with your formula, it works on the text: TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q)
but not working on the text: TOTAL111Q(PETER222Q;BILL0Q;LILLY333Q;MIKE0Q)
.. since both of your original given examples had exactly the same pattern of 4 items with the middle 2 to be removed.5 varied examples and the expected result for each
Function Remove0Q(S As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = ";\w+?0Q(?=;|\))"
Remove0Q = Replace(.Replace(Replace(S, "(", "(;", 1, 1), ""), "(;", "(", 1, 1)
End With
End Function
ivill.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) | AA111Q(BB222Q;EE333Q) | ||
2 | TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | ||
3 | TOTAL1110Q(KEN0Q;PETER222Q;MIKE0Q;BILL0Q;LILLY333Q;ANN0Q) | TOTAL1110Q(PETER222Q;LILLY333Q) | TOTAL1110Q(PETER222Q;LILLY333Q) | ||
4 | AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) | AA111Q(BB222Q;EE333Q) | ||
5 | TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | ||
6 | TOTAL111Q(PETER222Q;BILL0Q;LILLY333Q;MIKE0Q) | TOTAL111Q(PETER222Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | ||
7 | total110Q(ALICE55Q;JIM0Q;WOOD55Q) | total110Q(ALICE55Q;WOOD55Q) | total110Q(ALICE55Q;WOOD55Q) | ||
8 | total110Q(ALICE0Q;JIM0Q;WOOD110Q) | total110Q() | total110Q() | ||
Text Replacement |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1:B8 | B1 | =LEFT(A1,FIND("(",A1))&TEXTJOIN(";",1,IF(ISNUMBER(FIND("0Q",MID(SUBSTITUTE(";"&MID(A1,FIND("(",A1)+1,LEN(A1)),";",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100,100))),"",TRIM(MID(SUBSTITUTE(";"&MID(A1,FIND("(",A1)+1,LEN(A1)),";",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100,100))))&IF(RIGHT(A1,3)="0Q)",")","") |
C1:C8 | C1 | =Remove0Q(A1) |
Function NoZeroQ(S As String) As String
Dim V As Variant, Arr As Variant, Txt As String
Arr = Split(Replace(S, "(", ")"), ")")
For Each V In Split(Arr(1), ";")
If Not V Like "*[!0-9]0[Qq]" Then Txt = Txt & ";" & V
Next
NoZeroQ = Arr(0) & "(" & Mid(Txt, 2) & ")" & Arr(2)
End Function
I assumed it was because the number in front of the "Q" is not 0, it is 110.I note your examples in post #7, thanks, but I don't understand the final result. Why is WOOD110Q left in the brackets?