Need to Extract Data from middle of cell

**iturnrocks** · 04-16-2008, 11:04 AM

I have data in cells that looks like this

This is all in one cell.

001 000950694 082687864236 UPC TTD3 24 EA 8.2200 8/3 197.28

I need a macro that extracts "TTD3" and "24" and puts them in separate cells.

Variations: sometimes instead of TTD3, its WS20, SM6, RTD4, GW13, RT40, or10/6RS.

Quantity can be anywhere from 15 to 468.

Here are a couple more examples

002 000950715 082687862003 UPC WS20 48 EA 3.9900 8/3 191.52
003 000969736 082687112108 UPC SM6 12 EA 6.8800 4/3 82.56

The other data in the cell isnt needed, it can be deleted or left as long as the part number and quantity end up in different cells, so for example A1 would start with "003 000969736 082687112108 UPC SM6 12 EA 6.8800 4/3 82.56" and then B1 would contain "SM6" and C1 would contain "12"

Thank you

**sweep** · 04-16-2008, 11:39 AM

Hi,

You could extract this data using Data > Text to Columns, with space as the delimeter.

**iturnrocks** · 04-16-2008, 12:33 PM

Originally Posted by sweep

Hi,

You could extract this data using Data > Text to Columns, with space as the delimeter.

I think this will work.

Thank you

**ptm0412** · 04-16-2008, 12:06 PM

002 000950715 082687862003 UPC WS20 48 EA 3.9900 8/3 191.52
003 000969736 082687112108 UPC SM6 12 EA 6.8800 4/3 82.56

I think Excel Function is OK:

B1=  LEFT(RIGHT(TRIM(A1),LEN(A1)-31),FIND(" ",RIGHT(TRIM(A1),LEN(A1)-31),1)-1)
C1 = MID(RIGHT(TRIM(A1),LEN(A1)-31-LEN(B1)-1),1,FIND(" ",RIGHT(TRIM(A1),LEN(A1)-31-LEN(B1)-1),1)-1)*1

Note:
Trim(A1) to prevent A1 from extra space between character group.
31 = len("002 000950715 082687862003 UPC ") = const for every strings
Find(" ",....) - 1 : is the position of the first character in the string after cut 31 left characters
and so on
You may define a range name Str1 = RIGHT(TRIM($A1),LEN($A1)-31) when the cell point is in row 1.
And another range name Str2 = RIGHT(TRIM($A1),LEN($A1)-31-LEN($B1)-1) when the cell point is also in row 1.
Then the formulas are:

B1 = left(Str1,find(" ",Str1,1)-1)
C1 = Mid(Str2,1,Find(" ",Str2,1)-1)*1

**Portuga** · 04-16-2008, 12:31 PM

It seems that you always want word 4 and 5 of the text.
If that is the case, you can use the following code to extract word 4:

=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)

And for word 5:

=MID(MID(MID(SUBSTITUTE(A1," ","^",4),1,256),FIND("^",SUBSTITUTE(A1," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",4),1,256),FIND("^",SUBSTITUTE(A1," ","^",4)),256))-2)

If you concatenate both, you should have what you want in a single (massive) formula.

**ptm0412** · 04-16-2008, 12:41 PM

Here is the attachment that I calculate in both ways: function with and without range name

**JenniC** · 03-12-2009, 12:59 PM

There is a good example at http://www.biterscripting.com/SS_CSV.html .

I think you are looking to extract words 5 (part) and 6 (qty).

Assuming, your data is in x.csv, I think the following commands should work. ( I am just modifying that sample script to suit your requirements. )

var str data, new_data ; cat x.csv > $data
while ($data <> "")
do
var str row ; lex -e "1" $data > $row
# Get the 5th and 6th word and add into next columns.
var str part, qty
wex -e "5" $row > $part
wex -e "6" $row > $qty
set $row = $row + "," + $part + "," +$qty
set $new_data = $new_data + "\n" + $row
done

# Write new data back.
echo $new_data > x.csv

J