Using LEFT (or other functions) where the "=" is part of the formula?

[boznian]

I need to sort, and maybe filter, and find values from program code. Unfortunately the character(s) that I need are the 2 to the left of an = (equals) sign. I haven't found any way to use the = sign IN the formula - is this possible? It gave me a tip to place a single quote prior to the = sign but the formula still crashed. Excel 2000 FWIW. Example data, column A, what I need for column B:
y2=0.0 ...so I need "y2"
forcedmin1=auto ...so I need n1
max0=200.0 ...so I need x0
etc.

Every line is a different total length and the = sign is always in a different place so I can't use any 'fixed' parameters.

 

[Zot]

You can use SEARCH to find location of = . Then use MID to extract data.

Say forcedmin1=auto is in cell "M2". The formula would be =MID(M2,SEARCH("=",M2)-2,2)

 

[boznian]

That did it, thanks a bunch! Never heard of "MID" before.

 

[boznian]

I thought I was done, but new data shows I didn't plan ahead well enough and I didn't ask the right question. I need UP TO the 2 characters to the left of the = sign, but only the NUMBERS. So the raw code may look like this:
id10= ... I need 10
pale220= ... I need 20
nit1= ... I need 1
x0= ... I need 0
How do I return just the 1 or 2 digit numeric value prior to the = sign?
I can do this to get the starting position of the number, but haven't been able to go further:
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},C60&"0123456789"))

 

[Zot]

It is easier to do with VBA. Are you familiar with VBA?

 

[boznian]

Yes, I have used VBA before

 

[Fluff]

How about

=IFERROR(MID(A2,FIND("=",A2)-2,2)+0,MID(A2,FIND("=",A2)-1,1))

 

[boznian]

=IFERROR(MID(A2,FIND("=",A2)-2,2)+0,MID(A2,FIND("=",A2)-1,1))

That seems to work well! Let me do a little further testing on the actual code from today...

 

[Zot]

That seems to work well! Let me do a little further testing on the actual code from today...

Yes, that should work perfectly. I was thinking if you have so many rows, long formula would invisibly bloat the file size ?

I though creating a UDF would be better.

I was about to proposed this

Function GetNum(cell As Range) As Long

Dim n&

Select Case True
    Case cell Like "*##=*"
        GetNum = Mid(cell, Application.Find("=", cell) - 2, 2)
    Case cell Like "*#=*"
        GetNum = Mid(cell, Application.Find("=", cell) - 1, 1)
End Select

End Function

 

[boznian]

Yes, that seems to have done it nicely! I am going to mark this resolved. Thank you