Software

Ask something
Discussion Locked

Excel - comma delimited data sort?

0 helpful
5 replies

by acantu

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I exported a list of exchange email user accounts in order to edit different entries 'en masse'.
All is well...except, I now need to be able to sort only the 'Display Name' column (example: Jone, Joe J.) into three separate columns for another project. Is there a way to save this one column into a different format that will turn it into three columns?

5 replies

by dklippert

If you have the data in CSV format, try renaming the extension .DAT and bring it in. The Import wizard may give you enough choices. If you have the data in Excel already, you can try this:

Insert extra columns to the right of "Display Name" or move that column to the far right of the table. Select the column and go to Data>Text to Columns. You can choose the delimiter, comma - space, etc.) When you finish the wizard, any data immediately to the right of the column will be over written.
You may have to poke around and edit the results. Some names, such as von Wigglesworth, have to be fixed by hand. You also might want to use Trim() to make sure there are no extra spaces.

by shanghai sam

Thank you! I played with both your suggestions using ficticious names saved in csv and xls formats. Renaming the csv with a .dat didn't work for me.
However, your second suggestion using the xls format, select the column, Data > Text to Columns- worked like a charm! Thanks for the tip (although I now have first hand experience playing around with dummy data)about any data immediately to the right of the column will be overwritten. Yes, that's true. I learned that it would be beneficial toinsert three columns to the right of my original 'Display Name' column - to make room for the First name, middle initial, and any 'Jr. II or Sr' that some users like to include in their names. Of course, the 'Display Name' column becomes the users Last name.
Thanks for making my work life so much easier!

by coolbarbarian

There is usually a way of writing a formula if the normal parse tools or import tools do not work. You would have to look for common factors in the column to be split eg split first at comma and then at second space (Jone, Joe J.)or last letter is always middle initial if followed by period. Typically you would use FIND command combined with LEFT/RIGHT/MID etc

Alan

This discussion is closed to new replies.