You may have missed my edit to you in my last post to the OP. Here is what I wrote... I assumed it was because the number in front of the "Q" is not 0, it is 110.Glad you got an answer, but for my benefit can you explain that final result? That is, why is WOOD110Q left in when it contains "0Q"
Yes, Rick, I had missed that edit. Thank you for the clarification.You may have missed my edit to you in my last post to the OP. Here is what I wrote... I assumed it was because the number in front of the "Q" is not 0, it is 110.
Function Remove0Q(S As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = ";\w+?\D0Q(?=;|\))"
Remove0Q = Replace(.Replace(Replace(S, "(", "(;", 1, 1), ""), "(;", "(", 1, 1)
End With
End Function
ivill.xlsm | |||||
---|---|---|---|---|---|
A | C | ||||
1 | AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) | |||
2 | TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | |||
3 | TOTAL1110Q(KEN0Q;PETER222Q;MIKE0Q;BILL0Q;LILLY333Q;ANN0Q) | TOTAL1110Q(PETER222Q;LILLY333Q) | |||
4 | AA111Q(BB222Q;CC0Q;DD0Q;EE333Q) | AA111Q(BB222Q;EE333Q) | |||
5 | TOTAL111Q(PETER222Q;MIKE0Q;BILL0Q;LILLY333Q) | TOTAL111Q(PETER222Q;LILLY333Q) | |||
6 | TOTAL111Q(PETER222Q;BILL0Q;LILLY333Q;MIKE0Q) | TOTAL111Q(PETER222Q;LILLY333Q) | |||
7 | total110Q(ALICE55Q;JIM0Q;WOOD55Q) | total110Q(ALICE55Q;WOOD55Q) | |||
8 | total110Q(ALICE0Q;JIM0Q;WOOD110Q) | total110Q(WOOD110Q) | |||
Text Replacement |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1:C8 | C1 | =Remove0Q(A1) |