Parsing and Concatenation
Published on 29th June 2004
This time we are going to look at parsing and concatenating in Excel, but first we'll explain what these words mean.
Parsing is used when you want to split some data into two (or more). The most usual use of this in Excel is if you have someone's full name in a cell, and you want to divide it and have the first name in one cell and surname in another.
Concatenation is the opposite. You have two different bits of data that you want join together in a single cell.
Parsing
Parsing uses functions like:
- Left()
- Right()
- Mid()
These pick out, the left, right or middle of a cell, respectively. Here are some examples:
- =LEFT(A1,5) looks at cell A1 and displays the first (leftmost) 5 characters in the cell.
- =RIGHT(A1,5) gives you the last (rightmost) 5 characters in A1.
- =MID(A1,3,7) gives you the 7 characters that start at the third character in the cell. Ie. If "Black Swan Training was in A1, the answer would be "ack Swa".
- To find out how many characters are in a cell use:
- =LEN(A1).
- To identify the position of a particular character use:
- =FIND("X",A1). This example will tell you where in cell A1 is the X. In X-ray it would be 1, while in Rex it will be 3.
You can nest these functions together.
- To pick out the first word in a cell (eg. A first name), use: =LEFT(A1,FIND(" ",A1))
- FIND locates the space after the first word, and LEFT tells us everything left of it.
- To pick out a surname, you can use:
- =RIGHT(A1,LEN(A1)-FIND(" ",A1))
This works out how many characters follow the space (the total length minus the position of the space) and uses RIGHT to tells us what they are.
Concatenation
Concatenation is much easier.
You use the & sign to join two pieces of data together.
=A1 & B1 would join the contents of the two cells together. Be careful however: you might get "JohnSmith".
To add a space between the two try this: =A1 & " " & B1. This joins A1 to a space followed by B1.
You can concatenate numbers with text, so if the weight of an item is in A1 you can have a formula that says =A1 & " kilograms".