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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Zack BarresseConnect With a Mentor CEOCommented:
Since it is always a number, just text stored as a number, you can coerce it...

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

It's a nifty way of taking a number as text, and making it into an actual number.  That is the double urnary.  You could also use 1* or 0+ with the same effect.

Zack
0
 
Zack BarresseCEOCommented:
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
0
 
GeoffHarperCommented:
pos = InStr(FIELDNAME,"X")
If pos > 0 Then
    ResultStr = Mid(FIELDNAME,(pos+1))
Else
    ResultStr = ""
End If


Is that what you're looking for?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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?
0
 
Zack BarresseCEOCommented:
This should work...

=IFERROR(RIGHT(A1,LEN(A1)-FIND("X",A1,1)),0)
0
 
Zack BarresseCEOCommented:
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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
0
 
Zack BarresseCEOCommented:
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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok I understand now.

Yes I would only want 24. Or whatever is to the left of the decimal point.
0
 
Zack BarresseCEOCommented:
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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
It's working for some but not all. If the value to the right of the decimal is anything other than 0 then it's still showing it. If all the numbers to the right of the decimal are 0's then it's only showing the numbers to the left of the decimal.
0
 
Zack BarresseCEOCommented:
Can you give some examples?  When I change your data I don't get the same results.

Zack
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Nevermind. I got it working. I just changed the value to number in the cell with the formula. Then I just adjusted the decimal values.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks very much! I greatly appreciate it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.