| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting Numbers froma Text String
Hi...I have a problem I've been working on for a while now...to no avail.
a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
A couple questions I need answered first, you show values from A2,B2 etc, do
these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
Thanks John, so much for answering.
The first ten in A2 are consistent, B2 is consistent, c2 can be from two to five digits and d2 can be from 1 to three. I've extracted all but the one I need which is a quantity...one to 400... So, with what is left the first order would be the first 2-4 numbers and then (what I need) the one to three number I need right next to the text string. Does that answer everything? Thanks for the help. -- Jules "John Bundy" wrote: A couple questions I need answered first, you show values from A2,B2 etc, do these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
Actually, I am confused by the question. The OP starts by saying he needs
the one (number?) right before the text begins, which, to me looks like an 18-digit number from the example. Then the OP follows that by saying "some times it may be up to three numbers"... is that still before the text? If so, how are the numbers distinguished from each other. The example show three numbers in A2, B2 and C2 made from the first 17 of the 18 digits before the text. I see no rule behind this split out.. and what about the 18th digit that got lost in the shuffle? Rick A couple questions I need answered first, you show values from A2,B2 etc, do these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
As there is (apparently) no way of knowing the lenght of C2 and D2, there
appears to be no way of extracting them generically. In your example c2 & d2 =131 and therefor logic says c2=13 and D2=1 If the last digits (having extracted A2 & B2) were 12345, then ... C2 D2 12 345 123 45 1234 5 How can we determine which of these it is? "Jules" wrote: Thanks John, so much for answering. The first ten in A2 are consistent, B2 is consistent, c2 can be from two to five digits and d2 can be from 1 to three. I've extracted all but the one I need which is a quantity...one to 400... So, with what is left the first order would be the first 2-4 numbers and then (what I need) the one to three number I need right next to the text string. Does that answer everything? Thanks for the help. -- Jules "John Bundy" wrote: A couple questions I need answered first, you show values from A2,B2 etc, do these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
I can get all of the numbers up to the text through code, and i can parse out
A and B but i have to know how to tell where c2 and d2 seperate, how can i tell if c is 5 digits and d is 1 digit or whether c is 3 digits and d is 3 digits? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Thanks John, so much for answering. The first ten in A2 are consistent, B2 is consistent, c2 can be from two to five digits and d2 can be from 1 to three. I've extracted all but the one I need which is a quantity...one to 400... So, with what is left the first order would be the first 2-4 numbers and then (what I need) the one to three number I need right next to the text string. Does that answer everything? Thanks for the help. -- Jules "John Bundy" wrote: A couple questions I need answered first, you show values from A2,B2 etc, do these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
Not sure why my message posted before ones that came earlier bu just in case
you didn't get it I can get all of the numbers up to the text through code, and i can parse out A and B but i have to know how to tell where c2 and d2 seperate, how can i tell if c is 5 digits and d is 1 digit or whether c is 3 digits and d is 3 digits? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Thanks John, so much for answering. The first ten in A2 are consistent, B2 is consistent, c2 can be from two to five digits and d2 can be from 1 to three. I've extracted all but the one I need which is a quantity...one to 400... So, with what is left the first order would be the first 2-4 numbers and then (what I need) the one to three number I need right next to the text string. Does that answer everything? Thanks for the help. -- Jules "John Bundy" wrote: A couple questions I need answered first, you show values from A2,B2 etc, do these numbers have meaning? Are they always the same length. Is there a consistent amount of numbers before the first letter? and finally you say there could be up to 3 numbers that you need, how do you know how many you'll need? -- -John Please rate when your question is answered to help us and others know what is helpful. "Jules" wrote: Hi...I have a problem I've been working on for a while now...to no avail. a2=200865346120565131TED STOCK THIGH HIGH XL 3922 I'm managed to get to a2=2008653461 b2=20565 c2=13 d2=131ted stock thigh high xl 3922 e2=ted stock thigh high xl 3922 Problem is D2 - I need the one after the three... It is not consistent in the database... But what is consistent is I need the one right before the text begins (some times it may be up to three numbers) all located before the text. Any help would be great! Thanks jules 01.20.09 -- Jules |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting Numbers froma Text String
"Jules" wrote...
.... The first ten in A2 are consistent, . . . =LEFT(x,10) . . . B2 is consistent, . . . =MID(X,11,5) . . . c2 can be from two to five digits and d2 can be from 1 to three. This requires further specification. If C2 and D2 coult contain any 2-5 or 1-3 digit numbers, respectively, there's no reliable means of telling, for example, whether 12345 should be C2 = 12 and D2 = 345, C2 = 123 and D2 = 45 or C2 = 1234 and D2 = 5. If there's no unambiguous rule, there's no way to do this with formulas. Only when there are only 3 numerals to split between C2 and D2 is it purely unambiguous: C2 gets the first 2 and D2 gets the third. C2: =IF(COUNT(-MID(x,19,1))=0,MID(x,16,2),"?") D2: =IF(COUNT(-MID(x,19,1))=0,MID(x,18,1),"?") If D2 contained quantities, and if quantities would never appear with leading zeros, then 0s at char positions 18 to 20 (if C2 could be 2 to 5 numerals) would also be part of C2 rather than D2. Also, if the last numeral or two before the text were a 0, then you could back up to the first nonzero numeral. I've extracted all but the one I need which is a quantity...one to 400... So, with what is left the first order would be the first 2-4 numbers . . . Now you say 2-4. Which is it? 2 to 5 chars or 2 to 4 chars in C2? . . . and then (what I need) the one to three number I need right next to the text string. Does that answer everything? .... No. See above. It's not unlikely you won't be able to do this with formulas. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting a numbers from a text string | Excel Worksheet Functions | |||
| Extracting numbers from string of text | Excel Discussion (Misc queries) | |||
| extracting numbers within text string! | Excel Worksheet Functions | |||
| Extracting a Single Worksheet froma Mutipage Workbook | Excel Discussion (Misc queries) | |||
| extracting numbers from string | Excel Discussion (Misc queries) | |||
Linear Mode
