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

 

[texasalynn]

here is a formula that will capture the amount.

Excel 2003
	A
1	14-00-100040 UD ACETAMINOPHEN 325 MG .0219 100 004410400836 614228 5/31/12 60 ISSUE 6/01/12 1730
2	14-00-100210 EA ALBUTEROL INHALER 90M 55.6905 1 004410400267 614225 10/28/11 60 ISSUE 6/01/12 2882
3
4
5	.0219
6	55.6905
Sheet1

Cell Formulas
Range	Formula
A5	=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",5))+1,FIND("|",SUBSTITUTE(A1," ","|",6))-FIND("|",SUBSTITUTE(A1," ","|",5))-1)
A6	=MID(A2,FIND("|",SUBSTITUTE(A2," ","|",5))+1,FIND("|",SUBSTITUTE(A2," ","|",6))-FIND("|",SUBSTITUTE(A2," ","|",5))-1)

I had something similiar that I needed to split out data and used this method with formulas to split the columns within the macro

 

[gmilton]

Texasalynn,
Thank you so much for looking at this and your reply. I guess, though, I wasn't clear.
What I need is one formula that will work for both (as well as those with diffent numbers of characters /spaces). If it weren't for the variables, I could count characters,etc.

I was trying to either work from the few consistent things in each line, like work forward from the decimal (such as in .0219) or work backward from "00441???????" or "the space before '.????' ". I just don't know enough about excel and formulas to figure these out.

Any other ideas?
Thanks,
Gary

 

[Dannyh1]

Hi Try

Cost =VALUE(MID(A1,FIND(" ",A1,FIND(".",A1)-3)+1,FIND(" ",A1,FIND(".",A1)+1)-FIND(" ",A1,FIND(".",A1)-3)-1))

CC =MID(A1,FIND("0044",A1),12)

Date =SUBSTITUTE((MID(A1,FIND("/",A1)-2,8))," ",0)

Code =MID(A1,FIND("/",A1)+7,2)

Sequence =RIGHT(A1,4)

Leaves TDesc, think I'll be there in a minute

 

[Dannyh1]

TDesc =MID(A1,FIND("/",A1)+10,FIND("/",A1,FIND("/",A1,FIND("/",A1,FIND("/",A1)+1)+1)+1)-(FIND("/",A1)+15))

 

[Dannyh1]

FYI Texasalynn works for both maybe just put a "=VALUE("in front of it.

 

[texasalynn]

Texasalynn,
Thank you so much for looking at this and your reply. I guess, though, I wasn't clear.
What I need is one formula that will work for both (as well as those with diffent numbers of characters /spaces). If it weren't for the variables, I could count characters,etc.

I was trying to either work from the few consistent things in each line, like work forward from the decimal (such as in .0219) or work backward from "00441???????" or "the space before '.????' ". I just don't know enough about excel and formulas to figure these out.

Any other ideas?
Thanks,
Gary

I'm confused, my formula is the same for both lines even though one has numbers before the decimal and one without. The formula was just copied down making the reference changed to match the row reference. Unless I'm just totalling off in a different direction

 

[gmilton]

I am so sorry Texasalynn -- those two lines on my spreadsheet are separated by a few hundred rows. Those two did work correctly. However, it looks like I needed to attach more examples --with other variables in the "description"field--Those must have been what I was looking at.

In addition to these, please try on...
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
14-00-100232 EA ALBUTEROL-IPRATROPIUM 377.8064 1 004410400277 614225 11/29/11 40 RETURN 6/01/12 2230
14-00-100250 UD ALLOPURINOL 100 MG TA .0520 9 004410400626 614225 1/31/12 60 ISSUE 6/01/12 3072
14-00-100552 UD AMOXICILLEN 875MG/CLA 2.7964 12 004410400676 614225 11/30/11 60 ISSUE 6/01/12 4040
14-00-100640 UD ASCORBIC ACID 500 MG .0875 14 004410400626 614228 6/01/12 40 RETURN 6/01/12 0330
14-00-102590 UD FAMOTIDINE 20MG TAB U .1928 3 004410400277 614225 3/29/12 60 ISSUE 6/01/12 2931
14-00-102596 EA FENOFIBRATE 145 MG TA 5.2598 1 004410400626 614225 6/04/12 40 RETURN 6/04/12 0046
14-00-102809 UD FLUTICASONE/SALMETERO 3.5084 1 004410400626 614225 11/30/11 40 RETURN 6/01/12 2464
14-00-105850 EA SOAP NON MEDICATED (P 5.9994 2 004410400629 614228 10/28/11 60 ISSUE 6/01/12 3982
14-01-100328 EA CITALOPRAM 20MG TAB ( .2445 20 004410400626 614225 1/31/12 40 RETURN 6/01/12 2646

Thanks again,
Gary

 

[gmilton]

Thanks Dannyh1
Most of these worked really well--especially considering the fields. However, as with texasalynn, there were some problems on cost field that showed as "3VALUE!" in ~ 800 of these. Most of these had a decimal in the description as well.

14-00-100232 EA ALBUTEROL-IPRATROPIUM 399.3700 3 441019047303 015519 614225 5/30/12 32 RCPT WO ORD 6/01/12 0054
14-00-103792 UD LEVOTHYROXINE 0.725MG .1543 200 441019047303 015519 614225 5/30/12 32 RCPT WO ORD 6/01/12 0118
14-00-103819 UD LISINOPRIL 2.5MG .5168 7 004416579002 614225 3/30/12 60 ISSUE 6/01/12 4829
14-00-106730 UD TRIMETHOPRIM 160 MG W .4422 200 441019047303 015519 614225 5/30/12 32 RCPT WO ORD 6/01/12 0174
14-00-104812 UD OXCARBAZEPINE 300MG T .5650 1 004410400626 614225 6/04/12 40 RETURN 6/04/12 0063

If you have any ideas about these, it would be really nice to get the odd ones down to a couple hundred. If you can do something with these, I can sort and paste this into these exceptions.
Thanks again. This is so much better than I can do!
Gary

 

[gmilton]

I didn't notice ---the error that shows is "#VALUE!" , not "3VALUE!"