Problems Splitting String into Chunks

[garvizu]

Hi all,

I have a spreadsheet with many string entries that contain text in the following format: PhaseName, SubphaseName, Subphase2Name.

The problem is the various string portions are delineated by multiple spaces and the number of spaces is not consistent. For example, in some cases the number of spaces between the PhaseName and SubphaseName is 14 while the number of spaces between SubphaseName and SUbphase2Name might be 5.

I would like to split the original string into three columns as shown in the attached spreadsheet.

Your help would be greatly appreciated.

Thank you very much!

[Ron Coderre]

Actually, the sections are delimited by carriage returns.

Try this:

1) select the data column
2) replace all double-spaces with nothing
(That will do most of the clean up)


3) Data.Text-to-columns
--Delimited....Click: Next
--Select the "Other" box
--Press CTRL+j (that will put the carriage return in the box)
--Click: Finish

There will only be minor clean up left.

Or...If you want to use formulas
Put this regular formula in B2 and copy across through D2 and down as far as you need:
=TRIM(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",LEN($A2))),(COLUMNS($B:B)-1)*LEN($A2)+1,LEN($A2)))

Note: THe ASCII code for a carriate return is 10.

Does that help?

[garvizu]

Wow! It took you minutes what has been bugging me for hours.

Thank you very much!

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)