Return Text from string containing both text and numbers left and right

**LorinL** · 10-23-2012, 08:26 AM

Hello,

I have a very long list of items that from which i would like to have the text and numbers separated. Here are a few examples:
00001 2X2 ACR/DIFFUSER 5.00 20.00 20.00 19.55
00001-A 2X2 EGGCRATE DIFF 39.95 39.95 51.95
00002 2X4 ACR H/DOOR DIFFUSER 198.00 35.00 35.00 45.00
00002-A 2X4 DIFF W/FRAME 145.00 145.00 120.00

Basically, the numbers to the right are prices. Some of the strings contain 3 prices and some contain 4. The numbers to the left are just the item numbers and they are all the same length. Some of the items themselves contain numbers in their names (e.g. 2x2) and I would like to keep these numbers in the result.

I read some older posts but i could not find one that solved this problem exactly.

I would really appreciate the assistance.

Thanks in advance,

LorinL

**NBVC** · 10-23-2012, 08:49 AM

Assuming data is starts in A2...

then in B2 enter:

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))

copied to C2 and then down the list

in D2 enter:

=TRIM(MID(A2,LEN(B2&C2)+2,MIN(FIND(0,SUBSTITUTE(MID(A2,LEN(B2&C2)+2,255),{1,2,3,4,5,6,7,8,9},0)&0))-1))

copied down

in E2 enter:

=IFERROR(TRIM(MID(SUBSTITUTE(TRIM(MID($A2,LEN(B2&C2&D2)+3,255))," ",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100))+0,"")

copied down and across number of columns equal to largest number of numeric extracts....

you can format these columns to 2 decimal places.