As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Course Of The Month
8 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Parse a field in Excel 2007
Posted on 2011-03-06
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-.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?
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?
[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
Formula or VBA?
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
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
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?
Active 3 days ago
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?
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
Oh, and if you need backwards compatibility, you can adjust it to this ...
=IF(ISERROR(FIND("X",A2,1)
Zack
=IF(ISERROR(FIND("X",A2,1)
Zack
Active 3 days ago
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.
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
Active 3 days ago
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.
Since it is always a number, just text stored as a number, you can coerce it...
=IFERROR(--RIGHT(A2,LEN(A2
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
=IFERROR(--RIGHT(A2,LEN(A2
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
And I'm sorry, the backwards compatible version would be ...
=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
=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
Active 3 days ago
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.
Active 3 days ago
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.
Featured Post
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
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 7 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.