asked on # 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?

MOLYBDENUM-RD-0.0784-.0002

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?

Visual Basic ClassicMicrosoft Excel

pos = InStr(FIELDNAME,"X")

If pos > 0 Then

ResultStr = Mid(FIELDNAME,(pos+1))

Else

ResultStr = ""

End If

Is that what you're looking for?

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?

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 is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

This should work...

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

=IFERROR(RIGHT(A1,LEN(A1)-

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

=IF(ISERROR(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.

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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.

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

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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

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

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

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.

Can you give some examples? When I change your data I don't get the same results.

Zack

Zack

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.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Thanks very much! I greatly appreciate it!

Formula:

=RIGHT(A1,LEN(A1)-FIND("X"

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