Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract everything to the right in cell when a number is encounter Matt Excel Worksheet Functions 4 January 9th 08 04:25 PM
how do i extract a number from a cell with no spaces? Mulvaney Excel Worksheet Functions 4 July 26th 05 09:15 PM
Extract sheet name/number to cell claytorm Excel Discussion (Misc queries) 1 June 27th 05 11:34 AM
how to extract the row number of a cell with certain value houston city Excel Programming 2 July 15th 04 11:21 AM
Extract number from name of cell Matt Excel Programming 1 January 14th 04 09:46 PM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"