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:
The string you wish to break up is located in this cell.
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 we’re looking for the first separator
Calculate length of string using the function len.
Get the first part by using the function left.
The arguments you pass to this function left are:
text: You input Cell 1’s 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
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
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.
With this method you can break up long addresses, urls or any other strings.
Please use the feedback link below for comments and suggestions.