Skip to content

Excel Text Functions LEFT MID LEN SEARCH CONCATENATE

    Excel Text Functions vs Text to Columns vs Flash Fill

    Watch the video below, follow the tutorial and download the featured file here

    Let’s look at a practical example of where we can use a combination of text functions. To start with I’ve got a list of names. The names have been entered: first name, last name.  Say I wanted to reverse the order of the names, so the pattern become last name, followed by a comma, then first name – for example Jones, Bert.

    first name and last names

    There are a number of ways I could do this.

    Flash Fill

    If you are using Excel 2013, the quickest way would be to try the new Flash Fill feature. I would type in the name pattern I wanted as shown in B2, and then on the Data tab on the Ribbon, in the Data Tools group click the Flash Fill button.

    flash fill changes the order of the names

    Flash Fill repeats the pattern for each name.

    flash fill changes the order of the names 2

    If you don’t have Excel 2013, you have to work a little harder.

    Delimit and then Concatenate

    One option would be use the Text to Columns command and then to concatenate (join) back the names, but in reverse order. To use this method, start off by selecting all the cells containing names.  Then on the Data Tab on the Ribbon and in the Data Tools group, click the Text to Columns button. Click Next in the first step of the wizard and in the second step ensure the correct delimiter is selected – in our case a space.

    choose the correct delimiter

    Then click Finish.  Your first names and last names now appear in separate columns. You can now use a concatenation formula to join the names back together again. The formula would be =B2&”, “&A2 The ampersand character is used to join or concatenate.  Literal characters like a comma and then a space need to be surrounded by double quotes.  So our formula joins the value in B2 with a comma and space and then joins that with the value in A2.

    concatenate first name and last name

    TEXT Functions:  LEFT, MID, SEARCH, LEN

    A second method is a little more complicated but avoids having to make so many extra columns. We are going to use a combination of the LEFT, MID, SEARCH and LEN text functions to reverse the order of the names.

    To start with I need to find the position of the space within the name. The SEARCH function will find this for me.

    The function has three arguments

    Find text – the text you want to find – in our example it is a space (note the double quotes)

    Within text – the text you are searching within – in our example cell A2

    Start num the starting point for your search, if you want to start searching from the beginning of text, leave blank, but say you want to start searching from the fifth character onwards you would type 5.

    Our SEARCH formula reads =SEARCH(“ “, A2) and returns 5.  The space is the fifth character within the name.

    search for a space

    Having created the SEARCH formula, I would then cut it to the clipboard – don’t cut the = sign. We will need this formula later on!

    The next step is to extract the last name from the text.  I am going to use the MID function to achieve this. MID has three arguments:

    Text – the text you want to extract characters from, in our example A2

    Start num – the position of the first character you want to extract. In our example that’s the first letter of the last name (or, more to the point, the next character after the space).  Our SEARCH function found the position of the space, so if I just add 1, I will get the position of the first letter of the last name – SEARCH(“ “,A2)+1

    Num chars – the number of characters you want to return.  Unfortunately teh last names have a different number of characters. So what’s the solution? I’ve used the LEN function to solve this problem.  LEN returns the number of characters in a text string: if I subtract this value from the position of the space then I get the number of characters in the last name – LEN(A2)-SEARCH(” “,A2) Actually, as long as you put a value in this argument that exceeds the maximum number of characters you are ever likely to need to return, you can get away without using the LEN function.  I will use it anyway for the purpose of this tutorial.

    the mid function

    So far so good, but now I need to add the first name.  Before I do that I need to join or concatenate the last name with a comma and then space.  I’ve done this below

    =MID(A2,SEARCH(” “,A2)+1,LEN(A2)-SEARCH(” “,A2))&”, “&

    Now to extract the first name…

    The LEFT function will allow me to do this. It has two arguments:

    Text – the text you want to extract characters from, in our example A2

    Num chars – the number of characters you want to extract, starting from the first character.  In our example this will be the last character of the first name or the character that precedes the space. Our SEARCH function found the position of the space, so if I just subtract 1, I will get the position of the last letter of the first name SEARCH(” “,A2)-1

    My formula is now ready to go…

    =MID(A2,SEARCH(” “,A2)+1,LEN(A2)-SEARCH(” “,A2))&”, “&LEFT(A2,SEARCH(” “,A2)-1)

    combination of text functions

    Leave a Reply