Separating Text in Excel (Considerations)

Here are some things to consider before separating your cells:

·         To ensure consistent data separation, standardize all cell data and corresponding delimiters. Using Find and Replace can make standardization quicker and easier.

·         Do not use delimiters that also function as text characters.
EXAMPLE: In Eau Claire WI, the space between Eau Claire and WI could work as a delimiter. But there is also a space operating as a text character between Eau and Claire, which you would not want to separate. One solution could involve separating cities and states with commas and using the comma as your delimiter.

·         Determine if you should separate your data into three columns (e.g., Jane M. Doe or John Doe Jr.).

·         Look for consecutive delimiters (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you should select the Treat consecutive delimiters as one option.
NOTE: If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one more blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns

Related Posts by Categories