# Parse a field in Excel 2007

I need to extract certain data from a field. I need all the data that is after the letter "X" in my field. For example:

MOLYBDENUM-RD-0.0784-.0002X24.000

I need to extract the 24.000
It won't be the same length every time though. It could be 2.000, 100.000, etc.

How can I do this?
Zack Barresse

Formula or VBA?

Formula:
=RIGHT(A1,LEN(A1)-FIND("X",A1,1))

VBA:
Sub GetData()
Dim sVal As String, rCell As Range
Set rCell = ActiveCell 'change to suit
sVal = Right(rCell.Value, Len(rCell.Value) - InStrRev(rCell.Value, "X"))
MsgBox sVal
End Sub

Zack
pos = InStr(FIELDNAME,"X")
If pos > 0 Then
ResultStr = Mid(FIELDNAME,(pos+1))
Else
ResultStr = ""
End If

Is that what you're looking for?

firefytr:

Your formula works but one thing. If it doesn't find an "X" in my cell how can I have it just put a 0 as my result instead of getting the #Value! error?
This should work...

=IFERROR(RIGHT(A1,LEN(A1)-FIND("X",A1,1)),0)
Oh, and if you need backwards compatibility, you can adjust it to this ...

=IF(ISERROR(FIND("X",A2,1)),0,RIGHT(A2,LEN(A2)-FIND("X",A2,1)))

Zack

What do you mean by backwards compatibility?

Also, what if I just want the values to the right of the X and only up to the decimal point? I don't think I will need any values after the decimal point.
Backwards compatibility means opening the workbook in older versions than 2007.  So if you saved it as an xls and opened in 2003 your formulas wouldn't break and give the #NAME error.

Do you mean you would only then want "24" from your example?

Zack

Ok I understand now.

Yes I would only want 24. Or whatever is to the left of the decimal point.
Zack Barresse

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And I'm sorry, the backwards compatible version would be ...

=IF(FIND("X",A2,1)<>0,--RIGHT(A2,LEN(A2)-FIND("X",A2,1)),0)

And in both of these last two examples I moved the data into A2 instead of A1, which is why my formulas have different references.  Sorry for any confusion.

Zack