Follow

Convert a Column of data in Excel to a Row separated by commas

- From the Programs menu, open up EmEditor (a special editor that will do the convert regex magic).

- From the Excel sheet, copy the column (for example, Column A with a list of account numbers) and paste them into EmEditor. They should appear as one single column, with each account number on its own line.

- Click the 'Search' menu, then click the 'Replace...' menu item.

- In the 'Find' field of the dialog that appears, type in   ^(.+)$

- In the 'Replace' field of the dialog that appears, type in   \1,

- Ensure that the 'Use Regular Expressions' check box is checked.

- Click 'Replace All'. This will cause the dialog to disappear, and a comma to be added to the end of each account number.

- Again, click the 'Search' menu, then click the 'Replace...' menu item to bring back the dialog.

- In the 'Find' field of the dialog that appears, type in   ,\r\n

- In the 'Replace' field of the dialog that appears, type in   ,  (i.e. a single comma)

- Ensure that the 'Use Escape Sequence' check box is checked. You may first need to uncheck any other checkboxes that are checked in the dialog.

- Click 'Replace All'; this should cause all the account numbers to now be in one row on one line, separated by just commas.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk