Separate a string of values into different columns (Part III)


November 25th, 2005 by admin admin

Splitting strings of values which are separated by a symbol is a breeze using the text to column function. What happens when you have a string without any separator, like those given below?

tops278484092828
name732180244063
baby276540921867

You can use the fixed width option instead of the delimited option in “Text to Column” function. For those who are using Excel 95 or 97 or evenExcel 2000, you might need to convert the font to one that is fixed width. This means that a “M” will occupy the same width as an “I”. The font type I recommend is “Courier” or “Courier New”.

Here are the steps:

1) Highlight the rows you want to parse.
2) Change the font to Courier.
3) Goto menu, Data, Text to Column
4) In step 1 of the wizard, select the option “fixed width”. Click Next
5) Point the mouse pointer at the appropriate width and click the left mouse button once.
6) If you want to remove any of the preset separator, click the left mouse button at the appropriate black line or separator twice.
7) Once you have set the relevant width, click finish and the strings of value are separated into the respective columns.

Leave a Reply

You must be logged in to post a comment.

s2Member®