Solved

Parse a field in Excel 2007

Posted on 2011-03-06
14
293 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048896
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
 
LVL 4

Expert Comment

by:GeoffHarper
ID: 35048900
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35048921
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048961
This should work...

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

Expert Comment

by:Zack Barresse
ID: 35048965
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35048992
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049033
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35049052
Ok I understand now.

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

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 35049061
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049074
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35049093
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
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35049194
Can you give some examples?  When I change your data I don't get the same results.

Zack
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35049195
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
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 35049198
Thanks very much! I greatly appreciate it!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question