| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find $ in a string of text and return numbers
In a string of text I need to find the first occurrence of a $ (there will
only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory €“ approved $42,000 (bc 9.32) 2.) E-1700 Inventory €“ signage $7,000 September 2006 Results: 42,000 7,000 -- Thanks! Craig |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find $ in a string of text and return numbers
Try this:
With Your sample data in A1:A2 B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH(" ",MID(A1,SEARCH("$",A1)+1,255))-1) Copy that formula down to B2 Does that help? *********** Regards, Ron XL2002, WinXP "Craig" wrote: In a string of text I need to find the first occurrence of a $ (there will only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory €“ approved $42,000 (bc 9.32) 2.) E-1700 Inventory €“ signage $7,000 September 2006 Results: 42,000 7,000 -- Thanks! Craig |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find $ in a string of text and return numbers
Wow! Perfect, thanks Ron!
Craig "Ron Coderre" wrote: Try this: With Your sample data in A1:A2 B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH(" ",MID(A1,SEARCH("$",A1)+1,255))-1) Copy that formula down to B2 Does that help? *********** Regards, Ron XL2002, WinXP "Craig" wrote: In a string of text I need to find the first occurrence of a $ (there will only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory €“ approved $42,000 (bc 9.32) 2.) E-1700 Inventory €“ signage $7,000 September 2006 Results: 42,000 7,000 -- Thanks! Craig |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find $ in a string of text and return numbers
The formula is working great. Is there a way to modify the formula so if the
string ends with the $ and number it will return the number? Sample: 3. Optics Inventory €“ approved $99,999 Result: 99,999 -- Thanks! Craig "Craig" wrote: Wow! Perfect, thanks Ron! Craig "Ron Coderre" wrote: Try this: With Your sample data in A1:A2 B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH(" ",MID(A1,SEARCH("$",A1)+1,255))-1) Copy that formula down to B2 Does that help? *********** Regards, Ron XL2002, WinXP "Craig" wrote: In a string of text I need to find the first occurrence of a $ (there will only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory €“ approved $42,000 (bc 9.32) 2.) E-1700 Inventory €“ signage $7,000 September 2006 Results: 42,000 7,000 -- Thanks! Craig |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find $ in a string of text and return numbers
For text in A1 that ends in with a dollar sign followed by a number,
try this: B1: =--MID(A1,SEARCH("$",A1)+1,255) Does that help? *********** Regards, Ron XL2002, WinXP "Craig" wrote: The formula is working great. Is there a way to modify the formula so if the string ends with the $ and number it will return the number? Sample: 3. Optics Inventory €“ approved $99,999 Result: 99,999 -- Thanks! Craig "Craig" wrote: Wow! Perfect, thanks Ron! Craig "Ron Coderre" wrote: Try this: With Your sample data in A1:A2 B1: =--LEFT(MID(A1,SEARCH("$",A1)+1,255),SEARCH(" ",MID(A1,SEARCH("$",A1)+1,255))-1) Copy that formula down to B2 Does that help? *********** Regards, Ron XL2002, WinXP "Craig" wrote: In a string of text I need to find the first occurrence of a $ (there will only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory €“ approved $42,000 (bc 9.32) 2.) E-1700 Inventory €“ signage $7,000 September 2006 Results: 42,000 7,000 -- Thanks! Craig |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find $ in a string of text and return numbers
On Wed, 13 Sep 2006 08:10:02 -0700, Craig
wrote: In a string of text I need to find the first occurrence of a $ (there will only be one $ in each string) and then return the numbers after the dollar sign until the first space. Samples: 1.) E-100 Inventory – approved $42,000 (bc 9.32) 2.) E-1700 Inventory – signage $7,000 September 2006 Results: 42,000 7,000 One way: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr The use the Regular Expression formula: =REGEX.MID(A1,"(?<=\$).*?(?=\s|$)") That will return a text string of 42,000. If you want the value to be numeric, prefix the formula with a double unary: =--REGEX.MID(A1,"(?<=\$).*?(?=\s|$)") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to find replace text or symbol with carriage return | New Users to Excel | |||
| find text in a string formula | Excel Worksheet Functions | |||
| Get different numbers to return text?? | Excel Worksheet Functions | |||
| Help - Separating numbers appearing on the right of a text string | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
