Parse a field in Excel 2007 to get a numberic value between "-"

I'm trying to parse my text field to pull out all the numbers between the "-"'s. For example:

316/316L-RD-0.225-.0005

I need the 0.225

It won't always be that number of digits. It could be 0.2357, etc.
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
...actually I missed the 2007 reference....

In Excel 2007 it would be simpler to use IFERROR function, i.e.

=IFERROR(LOOKUP(10^9,MID(A2,SEARCH("-?.",A2)+1,{1,2,3,4,5,6,7,8,9})+0),0)

regards, barry
0
 
barry houdiniCommented:
How many "-"s will there be? If you always need the data between the second and the third try this formula

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",20)),40),20))

assuming your data is in A2

That returns a text result, if you want it to be numeric then add +0 to the end of the formula and format result cell to show required number of decimal places.

regards, barry
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
There isn't always 3 "-",s. Here's another example using only 2 of them

440C-RD-0.250

The number I'm trying to extract will always have a leading digit to the right of the decimal point. Not always a zero but it will be a number to the left of the decimal point.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

how about using text to columns with the - sign as the delimiter. In the data samples provided so far, the number would be in the third column of the result.

cheers, teylyn
0
 
jimyXCommented:
If it's always going to be small figure "0.255..." and ".0005" then:
If in A1 you have "316/316L-RD-0.225-.0005" put in B1:
=MID(A1,FIND("0.",A1),FIND(".0",A1)-FIND("0.",A1)-1)
0
 
barry houdiniCommented:
Hello JimyX, I think the Asker says it might not always be zero....but you could SEARCH for -?., i.e.

=LOOKUP(10^9,MID(A2,SEARCH("-?.",A2)+1,{1,2,3,4,5,6,7,8,9})+0)

regards, barry
0
 
jimyXCommented:
316/316L-RD-0.225-.0005
And if the "RD" and "-" are always present then you can parse by using them:

=MID(A1,FIND("RD",A1)+3,FIND("-",A1,FIND("RD",A1)+3)-FIND("RD",A1)-3)
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Barry -

Your "LOOKUP" formula is working. Now how do I get rid of the #N/A for the ones that don't have any values in the cell whatsoever? I just want those to show 0.
0
 
barry houdiniCommented:
Try this version.....

=LOOKUP(10^9,IF({1,0},0,LOOKUP(10^9,MID(A2,SEARCH("-?.",A2)+1,{1,2,3,4,5,6,7,8,9})+0)))

regards, barry
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much! I greatly appreciate your help and quick response!
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.