extract number from a cell
 

I need to extract a number from a sting of characters in column A to column B.
The format of the string "within quotes" examples and the desired result "=" below:
colA colB
"b1 c 3" = $1.00
"e15 d 12" = $15.00
"eff1.25 c" 15" =$1.25
"b3.75 c 6" =$3.75
"f2.5 c 1" = $2.50
"e1.33 c 12" = $1.33
"b1.2 c 3" = $1.20
"b4 c 2" = $4.00
"db5 c 1" = $5.00
"b1 h 2" = $1.00

The format of the string in column A is first a 1, 2, or 3 letter code followed immediately by the number I need to extract to column B and then a space after that number. (That space is then followed by a letter, a space, and then another number.) Looking for the correct formula for column B to achieve the result.
Thanks in advance!

extract number from a cell
 

Hi Paul,

Am Wed, 14 Jun 2017 08:06:51 -0700 (PDT) schrieb Paul Doucette:

I need to extract a number from a sting of characters in column A to column B.
The format of the string "within quotes" examples and the desired result "=" below:
colA colB
"b1 c 3" = $1.00
"e15 d 12" = $15.00
"eff1.25 c" 15" =$1.25
"b3.75 c 6" =$3.75
"f2.5 c 1" = $2.50
"e1.33 c 12" = $1.33
"b1.2 c 3" = $1.20
"b4 c 2" = $4.00
"db5 c 1" = $5.00
"b1 h 2" = $1.00

The format of the string in column A is first a 1, 2, or 3 letter code followed immediately by the number I need to extract to column B and then a space after that number. (That space is then followed by a letter, a space, and then another number.) Looking for the correct formula for column B to achieve the result.

try in B1:
=LOOKUP(9^9,1*RIGHT(MID(A1,1,FIND(" ",A1)-1),COLUMN(A1:IQ1)))


Regards
Claus B.
--
Windows10
Office 2016

extract number from a cell
 

Hi again,

Am Wed, 14 Jun 2017 17:18:21 +0200 schrieb Claus Busch:

try in B1:
=LOOKUP(9^9,1*RIGHT(MID(A1,1,FIND(" ",A1)-1),COLUMN(A1:IQ1)))

or do it with an UDF:

Function myPrice(myRng As Range) As Double
Dim varData As Variant
Dim re As Object
Dim ptrn As String

Set re = CreateObject("vbscript.regexp")
ptrn = "[a-z] {0,}"
re.Pattern = ptrn
re.IgnoreCase = True
re.Global = True
varData = Split(myRng, " ")
myPrice = re.Replace(varData(0), "")

End Function

and call that function in the sheet with e.g.
=myPrice(A1)


Regards
Claus B.
--
Windows10
Office 2016

extract number from a cell
 

On Wednesday, June 14, 2017 at 11:07:02 AM UTC-4, Paul Doucette wrote:
I need to extract a number from a sting of characters in column A to column B.
The format of the string "within quotes" examples and the desired result "=" below:
colA colB
"b1 c 3" = $1.00
"e15 d 12" = $15.00
"eff1.25 c" 15" =$1.25
"b3.75 c 6" =$3.75
"f2.5 c 1" = $2.50
"e1.33 c 12" = $1.33
"b1.2 c 3" = $1.20
"b4 c 2" = $4.00
"db5 c 1" = $5.00
"b1 h 2" = $1.00

The format of the string in column A is first a 1, 2, or 3 letter code followed immediately by the number I need to extract to column B and then a space after that number. (That space is then followed by a letter, a space, and then another number.) Looking for the correct formula for column B to achieve the result.
Thanks in advance!

Thank you Claus!