Extract text from a long string of text

Question

I need to extract the following portion CDA-CUP-PF from the following string of MECH~CDA-CUP-PF~1 - CUP0915.2XL - Copper Reducer (P) text

AddFormula TopLeft.Offset(1, 3).Resize(RowCount, 1), "=IFERROR(RIGHT(AA" & Row & ",FIND(""~"",AA" & Row & ")-1,FIND(""^"",AA" & Row & ")+1-FIND(""-"",AA" & Row & ")),"""")"

This is what I see right now: MECH^CHU I need to see this: CDA-CUP-PF I need to use something like the VBA code above.

Why do you want to use VBA to write a worksheet formula? Why not just use a UDF? e.g `Split(s, "~")(1)` would return what you want. — Ron Rosenfeld, Feb 04 '19 at 21:16
https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Selkie, Feb 04 '19 at 22:40

urdearboy · Accepted Answer · 2019-02-04T21:40:41.503

1

Assuming your pattern is isolating the text in between ~ a formula solution is:

=MID(A1,FIND("~",A1)+2,FIND("~",A1,FIND("~",A1)+1)-FIND("~",A1)-3)

A VBA - UDF solution would look something like this

Public Function Isolate(x As Range)

    Dim xString: xString = Split(x, "~")
    Isolate = xString(1)

End Function

edited Feb 04 '19 at 21:40

answered Feb 04 '19 at 21:22

urdearboy

14,439
5
28
58

We are almost there. When I follow the formula solution above on a worksheet it works. When I try to post it in vba code like this: AddFormula TopLeft.Offset(1, 3).Resize(RowCount, 1), "=MID(AA" & Row & ",FIND(""~"",AA)+1,FIND(""~"",AA" & Row & ")FIND(""~"",)+1)-FIND(""~"",AA" & Row & ")-1)" AddFormula TopLeft.Offset(1, 3).Resize(RowCount, 1), "=MID(AA" & Row & ",FIND(""~"",AA)+1,FIND(""~"",AA" & Row & ")FIND(""~"",)+1)-FIND(""~"",AA" & Row & ")-1)" – Kurt Nichols Feb 04 '19 at 21:43
I see the following: MECH^CDA what do I need to change? Thank you for your efforts so far. – Kurt Nichols Feb 04 '19 at 21:45
Is `AA` a variable you declared? If so, you have it in quotes in your code in a few places so it needs to be removed. If it is not a variable, then you have other syntax issues – urdearboy Feb 04 '19 at 21:45
I gave you a `UDF` vba solution. Why not use that? Its must easier to work with – urdearboy Feb 04 '19 at 21:45
Yes AA is a Row variable that has been declared. Again per my statement I need to use this type of code format. – Kurt Nichols Feb 04 '19 at 22:16

score 0 · Answer 2 · answered Feb 04 '19 at 22:30

This formula will do what you want: =TRIM(MID(SUBSTITUTE(A1,"~",REPT(" ",255)),255,255))

It works by replacing ~ with 255 spaces, then it carves out 255 characters from 255 characters in (Which guarantees we get what you want) then it trims off the spare spaces.

If you want the other parts, use left or right instead of mid.

The UDF is a much better option though especially as you are doing this from code already.

Extract text from a long string of text

2 Answers2