Character extraction-CodePudding

I am trying to extract characters between an "=" and a space in a string in excel. however whenever I try to do this I get errors or it pulls data after a different space in the text string. The strings vary in their character length and what comes after the space. Essentially I need to pull whatever is between the "=" and the space (so the number after "grips=" but not any of that gibberish after the number) examples of the text:

gbg bobbrazer gb3 360x2,5/6,25x50 4/8/22 grips=100 fls922 IE522-11

gbg bobbrazer bloodtrait 360x2,5/6,25x50 4/8/22 grips=12 IE0008

So for the above examples I would need the result to be "100" for the first string and "12" for the second. Everytime I use a Mid(left and LEN( combo I can make it work for specific sets but only if the gibberish at the end is the same for some reason. Current Formula I am using is this abomination:

=IFERROR(IFERROR(MID(LEFT(D2,FIND(" K",D2)-1),FIND("=",D2) 1,LEN(D2)),MID(LEFT(D2,FIND(" B",D2)-1),FIND("=",D2) 1,LEN(D2))),MID(LEFT(D2,FIND(" U",D2)-1),FIND("=",D2) 1,LEN(D2)))

CodePudding user response：

Assuming:

The '=' always exists;
The portion of the text you'd like to extract is an integer;
This integer is never more than 99 characters long;

You could try the following:

Formula in B1:

=-LOOKUP(1,-MID(A1,FIND("=",A1) 1,ROW($1:$99)))

CodePudding user response：

You were on the right track with Mid and Find. A combination of those two will be able to search for two specific characters and return the string in-between them.

Here is the Excel formula to do this:

=MID(A1,FIND("=",A1) 1,FIND(" ",A1,FIND("=",A1))-FIND("=",A1)-1)

Since you tagged VBA, here is the VBA way to do this:

    Dim myString As String, PositionOfEquals As Long, PositionOfNextSpace As Long
    myString = [A1]
    PositionOfEquals = InStr(1, myString, "=")
    PositionOfNextSpace = InStr(PositionOfEquals, myString, " ")
    Debug.Print Mid(myString, PositionOfEquals   1, PositionOfNextSpace - PositionOfEquals - 1)

CodePudding user response：

Easier to use LET and assign your values as you go along

=LET(text, I4, eq, IFERROR(FIND("=", text), 0), space, IFERROR(FIND(" ", text, eq), LEN(text) 1), MID(text, eq   1, space-eq-1))