Paramix Learning: Parse Or Extract Text Components Out Of Strings In Excel

Parse or Extract Text Components out of Strings in Excel

By: Eddie Baki, 3.3.2013, 07:47

You often get strings of text you wish to separate into their individual components. In Excel you can do that by using a handful of functions.

The easiest approach to text parsing problems is the following:

Cell 1: Location of String

The string you wish to break up is located in this cell.

Cell 2: Location of First Separator

The first separator separates the first part of the string from the rest of the string.

In Excel this location is found using the find function, which requires three arguments: find_text: Here you input the separator ie " " for space
within_text: you input Cell 1, which hold your string
start_num: You can leave this empty as were looking for the first separator

Cell 3: Length of string

Calculate length of string using the function len.

Cell 4: Leftmost part of string

Get the first part by using the function left.

The arguments you pass to this function left are: text: You input Cell 1s address, as it holds the string you wish to break up
num_chars: Address of Cell 2 minus one, because the first part of the string ends one character ahead of the first separator

Cell 5: Rest of string

Here you get the remainder of string after the leftmost part is removed.

This is achieved using the right function by passing it the following arguments: text: Cell 1
num_chars: Length of string located in Cell 3 minus Location of first separator located in Cell 2

Repeat using Cell 4 as location of string

Now you have broken up the string into two parts. The first part in Cell 4 and the rest of it in Cell 5.

You now repeat this process using the contents of Cell 5 as the new location of your string.

Exercise: Parsing an address

With this method you can break up long addresses, urls or any other strings.

Please use the feedback link below for comments and suggestions.

Your Feedback

Your E-mail