Text To Columns

[vichisov]

This should be easy, but I am just not getting it right. I have a column of data that looks like this; a bunch of ICD diagnostic codes:

821.01-Fracture of femur; closed; shaft; 996.4-Mechanical Complication Of Internal Orthopedic Device Implant And Graft; 250.00-Diabetes mellitus; w/o mention of complication or manifestation; type II, controlled; 429.2-Cardiovascular disease, unspecified

This is just a partial sample as they are generally longer. I need to spread the data into columns so that each one starts with a digit. Ie
821.01-Fracture of femur; closed; shaft; 996.4-Mechanical Complication Of Internal Orthopedic Device Implant And Graft; etc...

Any idea how to do this?

Thanks,

[Vikas_Gautam]

Here are the steps:-
1. Select the column.
2. Click Text to column in data tab.
3. Select Delimited and Click Next.
4. Select the Appropriate Delimited. In your case, it would be, I guess, ";". So type ; in Others.
5. Since you type the delimiter, you will see the data converted into a table in the Preview provide Below.
6. Check this preview for desired result.
7. If it fulfills your need then click next and click Finish.

[martindwilson]

that would split at all the; in
; 250.00-Diabetes mellitus; w/o mention of complication or manifestation; type II, controlled;

[FDibbins]

Not sure I understand exactly what you want

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[vichisov]

The poster above is exactly right. A simple ";" delineation does not do the trick. I tried replacing all "; #" using a formula I found online and applying it in Word's find/replace and then bringing it back into Excel, but I first need to get rid of all "." for it to work, which is not ideal. The formula was find: ([0-9][1,0]) and replace with: ~\1 to insert a "~" in front of any number. I then used "~" as the delineator.

Any better way to do this and keep the periods?

Thanks,

[FDibbins]

Did you see post #4?

[vichisov]

See uploaded.

[FDibbins]

OK thanks

That is what you have, but what do you want it to look like?

[vichisov]

I need to separate the data into columns where the start of numbers represents a new entry. The format seems to be "; " followed by ###.##

Thanks,

[FDibbins]

Please provide a sample of your expwected outcome, so we dont have to guess

[vichisov]

Got it. Please see attached. Good point actually as I realized not all start with a number. Some have the pattern: $##.##

Thanks,

[AlKey]

Enter this formula in B1 and pull it to the right until you see blanks

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

[FDibbins]

AlKey, thats not going to work There are a few "sets" that have ; in the middle, otherwise T2C would also work
(see posts 3 and 5)