How to separate variable strings to different cells

[gmilton]

I have a spreadsheet with almost 5000 rows that was imported from a text document into one column. I would like to separate different parts of this column into new columns. Unfortunately, it is a mixture of variable length fields. Certain sections are consistent, others are not.

Can you please help me separate these fields
Here are examples of two rows:
14-00-100040 UD ACETAMINOPHEN 325 MG .0219 100 004410400836 614228 5/31/12 60 ISSUE 6/01/12 1730

14-00-100210 EA ALBUTEROL INHALER 90M 55.6905 1 004410400267 614225 10/28/11 60 ISSUE 6/01/12 2882

I can separate the first parts ok, but after descriptions then I get stumped based on variable lengths/characters. These fields are deliniated with spaces, but couldn't get them to separate correctly on import due to variable spaces in description.

I would like to create columns with these fields (I prefer to past formulas in cells if possible):

Cost (represented by ".0219" in 1st example and "55.6905" in 2nd) -4 digits to right of decimal, but may have up to4 before the decimal.
Quantity (repesented by "100" in 1st example and "1" in 2nd)--could be any whole #--I believe all are < 999.
Cost center ("00441???????") --these are always 12 digits long and start with "00441"
Trans Date ("5/31/12" and "10/28/11")
Code ("60" in each of these examples, but could be other two-digit code)
TDesc ("ISSUE" in each of these examples, but could be other texts such as "RETURN", "DESC CHANGE", or "RCPT WO ORD"-I think that is all on this report)
Sequence ("1730" and "2882") is always the last 4 digits.

Any help would be greatly appreciated.
Gary

 

[gmilton]

texasalynn and Dannyh1,
Thanks so much to both of you. With what you sent, I was able to make a few adjustments and get enought to use this report for what we need.

Again, I'm sorry about the confusion. I guess I was just overwhelmed.

Thanks again to both!
Gary

 

[Rick Rothstein]

TDesc ("ISSUE" in each of these examples, but could be other texts such as "RETURN", "DESC CHANGE", or "RCPT WO ORD"-I think that is all on this report)

The highlighted item will cause the most problem in that its field can contain spaces and spaces are used as delimiters. Is there any chance of you changing the spaces inside of "DESC CHANGE" and "RCPT WO ORD" to, say, underline characters? If not, the formula solutions will become ridiculously long since every reference to the cell, say for example, A2 will have to be replaced by the double substitution SUBSTITUTE(SUBSTITUTE(A2,"DESC CHANGE","DESC_CHANGE"),"RCPT WO ORD","RCPT_WO_ORD")).

Also, would a VB solution be acceptable (this would be much easier to program than to write a formula for).

 

[gmilton]

Thanks, Rick, that's an excellant idea. Actually the report came off of a pdf file and spaces were not really delimiters. However, I will do that with find & replace on the original column. That makes the Discription field the only one with extra spaces -- unfortunately, there's nothing I can do about that.

What I'm really stuck on right now it the Quantity. From the first examples:
Quantity (repesented by "100" in 1st example and "1" in 2nd)--could be any whole #--I believe all are < 999.

After replacing the TDesc spaces with underlines, how could the quantity be done? Can you count spaces from the right?

Thanks
Gary

 

[Rick Rothstein]

If you replace the spaces for the TDesc text with anything that is not a space, then these formulas should generate the text from the indicated fields...

Description: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-8)))

Cost: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1800),200))

Quantity: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),1600),200))

Cost center: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1400),200))

Trans Date: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1000),200))

Code: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),800),200))

TDesc: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),600),200))

Sequence: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))

 

[Rick Rothstein]

If you replace the spaces for the TDesc text with anything that is not a space, then these formulas should generate the text from the indicated fields...

Description: =LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-8)))

Cost: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1800),200))

Quantity: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),1600),200))

Cost center: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1400),200))

Trans Date: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1000),200))

Code: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),800),200))

TDesc: =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",200)),600),200))

Sequence: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),200))

Just to note, by the way, you did not request whatever the 614228 is called from the first example line of data (the value just before the Trans Date). If you omitted this by accident and need it, the formula for retrieving it is this...

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",200)),1200),200))

 

[gmilton]

Thanks so much, Rick! I'll give them a try. This does look much simpler.
Gary

 

[gmilton]

Rick, that did it! Thanks! It also revealed some other "extraneous" data in 150 records that I was able to clear out.

Thanks to all 3 of you for your help!
Gary