Extracting Values and Trimming Text

  • Thread starter Thread starter Mr. T
  • Start date Start date
M

Mr. T

Hello,
I have seen other posts with REPLACE and TRIM and FIND formulas, but
cannot make any sense of how to use them. I would like to know the
formula for all of the following independent items. Please assist and
many thanks in advance.

Data Desired Result
A1234-56-BC7890 1234
A1234-56-BC7890 56
A1234-567-BC890 567
A1234-56-BC7890 7890
 
I'm going to assume that all number sets in the string can be of varying
lengths (even though the 1st set are all 4 digits).

1st set:
A1234-56-BC7890 = 1234
Click to expand...

=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:255"))))

2nd set:
A1234-56-BC7890 = 56
A1234-567-BC890 = 567
Click to expand...

=LOOKUP(1E100,--MID(MID(A1,FIND("-",A1),255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1),255)&"0123456789")),ROW(INDIRECT("1:255"))))

3rd set:
A1234-567-BC890 = 890
A1234-56-BC7890 = 7890
Click to expand...

=LOOKUP(1E100,--MID(MID(A1,FIND("-",A1,FIND("-",A1)+1),255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1,FIND("-",A1)+1),255)&"0123456789")),ROW(INDIRECT("1:255"))))

Note that these formulas will drop any leading zeros:
A0123-056-BC07890
Click to expand...

The formulas would return: 123, 56, 7890

If there might be leading zeros then a different approach will be needed.
 
Hello again and sorry to bother,
The formulas have worked great - thank you, but I have found an
exception. I have come across an error in the formula whereby
extracting the value between the hyphens is not working in this
example:

S10754-10-379A617

Extracting "10" from this results in 13789, "11" results in 13820 and
"12" results in 13850. For some reason, 13 through 20 provides the
expected result.

Please assist and many thanks again!
 
Hello again and sorry to bother,
The formulas have worked great - thank you, but I have found an
exception. I have come across an error in the formula whereby
extracting the value between the hyphens is not working in this
example:

S10754-10-379A617

Extracting "10" from this results in 13789, "11" results in 13820 and
"12" results in 13850. For some reason, 13 through 20 provides the
expected result.

Please assist and many thanks again!
Click to expand...

Very interesting problem. It is Excel trying to be helpful and translating
numbers that might be dates into dates.

Biff's formula is looking for strings that look like numbers. When you have
certain values in the second group, the values that the formula is returning
include 10-37. That gets interpreted, on your machine, as 10/1/37 (October 1,
1937) and the value of that is 13789. Similar with 11-37 and 12-37. Of
course, 13-37 cannot be interpreted as a date, so in that case only the number
13 is returned.

One method is to eliminate the "-" from your string, substituting something
that will never appear in the string, and can't be misinterpreted by Excel.

So group 2 would be:

=LOOKUP(1E+100,--MID(MID(SUBSTITUTE(A1,"-",CHAR(1)),FIND(
CHAR(1),SUBSTITUTE(A1,"-",CHAR(1))),255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},
MID(SUBSTITUTE(A1,"-",CHAR(1)),FIND(CHAR(1),SUBSTITUTE(
A1,"-",CHAR(1))),255)&"0123456789")),ROW(INDIRECT("1:255"))))

You could make similar changes in Group 3, but it shouldn't be necessary given
the format of your data:

=LOOKUP(1E+100,--MID(MID(SUBSTITUTE(A1,"-",CHAR(1)),
FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1)),FIND(CHAR(1),
SUBSTITUTE(A1,"-",CHAR(1)))+1),255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},
MID(SUBSTITUTE(A1,"-",CHAR(1)),FIND(CHAR(1),SUBSTITUTE(
A1,"-",CHAR(1)),FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1)))+1),255)
&"0123456789")),ROW(INDIRECT("1:255"))))

Personally, I would use a User Defined Function to pull out the digits, but
some IT departments don't like that.

Also, you did not specify what you wanted to extract for the third group which,
in these later examples, are mixed digit and non-digit:

S10754-10-379A617

Biff's formulas, as modified above, return

10754
10
379

Is that what you want?

--ron
 
That worked! Thank you very much. I didn't think of it before, but I
simply put the original formula between =value() and Excel didn't
interpret the result as a date.
Kudos to everyone in this group that contribute such fantastic work!
 
Back
Top