Solved

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

Posted on 2011-03-06
10
289 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Lawrence Salvucci
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 35049372
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35049414
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35049496
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
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 24

Expert Comment

by:jimyX
ID: 35049593
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35049701
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
 
LVL 24

Expert Comment

by:jimyX
ID: 35049756
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
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35049914
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35050072
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35050093
...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
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 35050172
Thank you very much! I greatly appreciate your help and quick response!
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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

756 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