Lawrence Salvucci
asked on
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.
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.
ASKER
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.
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.
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
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
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)
If in A1 you have "316/316L-RD-0.225-.0005" put in B1:
=MID(A1,FIND("0.",A1),FIND
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
=LOOKUP(10^9,MID(A2,SEARCH
regards, barry
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,FI ND("-",A1, FIND("RD", A1)+3)-FIN D("RD",A1) -3)
And if the "RD" and "-" are always present then you can parse by using them:
=MID(A1,FIND("RD",A1)+3,FI
ASKER
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.
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.
Try this version.....
=LOOKUP(10^9,IF({1,0},0,LO OKUP(10^9, MID(A2,SEA RCH("-?.", A2)+1,{1,2 ,3,4,5,6,7 ,8,9})+0)) )
regards, barry
=LOOKUP(10^9,IF({1,0},0,LO
regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much! I greatly appreciate your help and quick response!
=TRIM(LEFT(RIGHT(SUBSTITUT
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