Extracting Numbers froma Text String

J

Jules

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
 
J

John Bundy

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?
 
J

Jules

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.
 
R

Rick Rothstein \(MVP - VB\)

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
 
T

Toppers

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?
 
J

John Bundy

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?
 
J

John Bundy

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?
 
H

Harlan Grove

Jules said:
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.
Click to expand...

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 . . .
Click to expand...

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?
Click to expand...
....

No. See above.

It's not unlikely you won't be able to do this with formulas.
 
Top