Excel Formula Help - Text Functions, LEN, FIND, LEFT, RIGHT
Presented with a list of first and last names (only) in a single column in Microsoft Excel, we would like to separate them out into two separate entries over two columns.
We can do this by using the various functions that help us work with text. The LEN function enables us to measure the the length of the text string and the FIND function allows us to find a common variable between all of the text entries in the table. We can use these functions in combination with the LEFT and RIGHT function as follows:
Firstly, we build a table with columns FIND and LEN. In the Len column, we begin with a simple formula that counts the length of the text entry (including spaces) in a cell, =LEN() as shown in the example:
We also need to determine the location of the space between first and last names, as this will help us separate them in a moment; we do this by using FIND and placing quotation marks around the common variable we wish to locate in each cell, in this case, the space between the first and the last name, as seen here:
We can now go to our main table and determine the contents of the First Name column with the formula =LEFT(A3,B9-1) Here, we are telling Excel to return the contents of the Full Name field to the left of the " " space, as referenced by the contents of the FIND column -1
Finally, the contents of the Full Name column are again returned in the Last Name column by telling Excel to take the full length of the text string, LEN, and discard everything before and including the contents of FIND :
After copying down we receive first and last names in separate columns as required.
For more help with managing simple and complex text functions, contact the Excel4Business experts.
