2 Easy Ways to Split Text in Excel: using Text to Columns and Formulas.

Learn how to effortlessly split text in Excel using Text to Columns and using Formulas (LEFT, RIGHT, MID, SEARCH) Transform and manage your data like a pro with these highly effective techniques.

Let’s say we have to split this address data to City, County to Zip code.

Table of Contents

1. Text to Column

Select the Range: Highlight the cells that contain the text you want to split.
Data Tab: Navigate to the Data tab on the Ribbon and click on “Text to Columns.”

Choose Delimited: In the dialog box that appears, choose “Delimited” and click “Next.”

Select Delimiters: You’ll see options like Tab, Semicolon, Comma, Space, and Other. If you choose “Other,” you can define a custom delimiter. Click “Next” after selecting your delimiter(s). In our case, we will split by comma.

Data Preview: Excel will show a preview of how the data will be split. Make any adjustments as needed.
Data Format: You can define the data type for each new column. Excel defaults to “General,” which tries to automatically detect the type.

Destination: Choose where you want the new columns to appear. By default, Excel will replace the original data.
Finish: Click on “Finish” to complete the operation.

Fixed Width Text to Columns

Select the Range: Same as above.
Data Tab: Same as above.
Choose Fixed Width: In the dialog box, select “Fixed Width” instead of “Delimited” and click “Next.”
Set Column Breaks: Excel will attempt to guess where the new columns should start but you can manually adjust these. Drag the lines to set the width of each column.
Data Preview, Data Format, and Destination: These steps are identical to those in the Delimited method.

2. Formulas

To split the above data in Excel using formulas, you could utilize a combination of functions like LEFT, RIGHT, MID, SEARCH, and LEN.

1. Extract City (LEFT and SEARCH Functions)

=LEFT(A2, SEARCH(“,”, A2) – 1)

Why do we need to add -1?

In the formula for extracting the city using =LEFT(A2, SEARCH(“,”, A2) – 1), the -1 is used to exclude the comma from the extracted text.

SEARCH(“,”, A2) finds the position of the first comma in the text. For example, if the cell A2 contains “New York,USA,10001”, SEARCH will return 8 because the comma is at the 8th position.
We want to extract just the city name, which is everything to the left of this comma. However, if we use =LEFT(A1, 8), it will include the comma at the end.
To avoid this, we subtract 1 from the position of the comma, effectively telling Excel to extract all the characters up to one position before the comma, which gives us “New York” without the comma. That’s why -1 is used in the formula; it ensures that the extracted text for the city name does not include the comma.

Drag to copy the formula for all data!

2. Extract Country (MID, SEARCH and LEN function)

To extract the country name, you can use a combination of MID, SEARCH, and LEN functions.

=MID(A2, SEARCH(“,”, A2) + 1, SEARCH(“,”, A2, SEARCH(“,”, A2) + 1) – SEARCH(“,”, A2) – 1)

1) The MID Function

The MID function is used to extract a substring from a text string starting at any position. Its syntax is:

=MID(text, start_num, num_chars)

text: The original text string. “,”
start_num: The position where the substring starts. A2
num_chars: The number of characters to extract.

2) The SEARCH Function

The SEARCH function is used to find the position of a specific character or substring within a text string. Its syntax is:

=SEARCH(find_text, within_text, [start_num])

find_text: The text you want to find.
within_text: The text where you want to search for find_text.
start_num (optional): The position to start the search from.

3) The Formula Components

Start Position (SEARCH(“,”, A2) + 1): This finds the position of the first comma and adds 1 to it. This is where the country name starts. For example, in “New York,USA,10001,” the first comma is at the 8th position, so the country name starts at the 9th position.
Number of Characters (SEARCH(“,”, A2, SEARCH(“,”, A2) + 1) – SEARCH(“,”, A2) – 1): This part is a bit complicated. It calculates how many characters to extract for the country name. Here’s how it works:
SEARCH(“,”, A2, SEARCH(“,”, A2) + 1): Finds the position of the second comma.
SEARCH(“,”, A2): Finds the position of the first comma.
By subtracting the position of the first comma from that of the second comma and then subtracting 1, we get the length of the country name.

Putting it all together, the MID function extracts the substring starting at the 9th character (just after the first comma) and spanning the length calculated above. This gives us the country name without any commas.

3. Extract ZIP code (RIGHT and LEN function along with SEARCH)

To extract the zip code, you can use the RIGHT and LEN functions along with SEARCH

=RIGHT(A2, LEN(A2) – SEARCH(“,”, A2, SEARCH(“,”, A2) + 1))

1) The RIGHT Function

The RIGHT function is used to extract a specified number of characters from the end of a text string. Its syntax is:

=RIGHT(text, [num_chars])

text: The original text string.
num_chars: The number of characters you wish to extract from the end of text.

2) The LEN Function.

The LEN function returns the length of a text string. Its syntax is:

=LEN(text)

text: The original text string.

3) The SEARCH Function.

The SEARCH function locates one text string within a second text string and returns the number of the starting position of the first text string from the first character of the second text string. Its syntax is:

=SEARCH(find_text, within_text, [start_num])

find_text: The text you want to find.
within_text: The text where you want to find find_text.
start_num: The starting position for the search

4) The Formula Components

Total Length (LEN(A2)): This calculates the total number of characters in the string. For example, in “New York,USA,10001,” there are 20 characters in total.
Position of the Second Comma (SEARCH(“,”, A2, SEARCH(“,”, A2) + 1)): This part of the formula finds the position of the second comma. It starts searching for a comma after the first comma has been found.
Number of Characters to Extract (LEN(A2) – SEARCH(“,”, A2, SEARCH(“,”, A2) + 1)): Subtracting the position of the second comma from the total number of characters gives us the length of the Zip Code.

When you put it all together, the RIGHT function extracts the Zip Code starting from the position right after the second comma to the end of the string.

Excel is an indispensable tool for anyone who deals with data, and its capabilities for text splitting are no exception. Text to Columns is perfect for quick and easy separations based on specific delimiters or fixed widths. On the other hand, formulas offer more dynamic control, automatically updating when your original data changes. By mastering both of these methods—Text to Columns and Formulas—you equip yourself with versatile skills that make data management not just feasible, but also efficient. Whether you’re an Excel novice or looking to upskill, these methods will undoubtedly elevate your data manipulation game.

2 Easy Ways to Split Text in Excel: using Text to Columns and Formulas.