string replace question

[ivill]

Hi, newbie here, AA111Q(BB222Q;CC0Q;DD0Q;EE333Q)
how do i replace the stuff in the bracket including 0Q
my expect result in the below:
AA111Q(BB222Q;EE333Q)
please help, thanks?

 

[Peter_SSs]

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?

 

[ivill]

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?

say this is the text: TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q)
how do i remove the stuff in the bracket including 0Q
my expect result in the below:
TOTAL111Q(PETER222Q;LILLY333Q)
thanks

 

[Peter_SSs]

One more example is hardly 5.

Your written description is still not clear

remove the stuff in the bracket including 0Q

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))

 

[Rick Rothstein]

If I understand your request correctly (and there is no guarantee of that), I think you want to find all items delimited by semi-colons inside the single parentheses that contain "0Q", no matter where inside the parentheses they are) and remove them. If that is correct, I am not sure if it can be done with just Excel formulas or not, but it can be done with a UDF (user defined function)...

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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NoZeroQ just like it was a built-in Excel function. For example,

=NoZeroQ(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[ivill]

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))

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)
the result will be TOTAL111Q(PETER222Q;MIKE0Q)
I expected the result should be TOTAL111Q(PETER222Q;LILLY333Q)

 

[ivill]

This is what i expected in the B column

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)

 

[Peter_SSs]

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)

Which is exactly why I asked for ..

5 varied examples and the expected result for each

.. since both of your original given examples had exactly the same pattern of 4 items with the middle 2 to be removed.

Assuming Rick's interpretation is correct and not more than a total of 9 terms in the brackets you could try this long worksheet function formula in column B or an alternative UDF if you want to consider that. The UDF is used in column C below.

I note your examples in post #7, thanks, but I don't understand the final result. Why is WOOD110Q left in the brackets?

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)

 

[Rick Rothstein]

You should have posted those examples from the beginning as there is no way we could have guessed them. Here is my UDF revised to work correctly with those examples...

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

@Peter_SSs,

I note your examples in post #7, thanks, but I don't understand the final result. Why is WOOD110Q left in the brackets?

I assumed it was because the number in front of the "Q" is not 0, it is 110.

 

[ivill]

guys, Sorry for my expression, Rick Rothstein's code works like a charm, thank you all!