How to Concatenate in Excel using LEFT, MID and RIGHT String Functions

I have worked with data downloads from companies where the date comes out of the database it is in an unsable format for date purposes.  Instead of displaying 30/05/2010 the cell would read 20100530.  I found a workaround for this using three different functions in Excel all in one formula.  The functions are LEFT, RIGHT and MID.  And the formula will then concatenate these together to give the required date format.

So in my example I want to turn 20100530 into 30/05/2010.  Let’s assume that the date is in cell A1 and the new version will be cell B1.  In cell B1 type the following formula

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

Now lets break that down into it’s component parts.



RIGHT(A1,2) – This first part is telling Excel to start at the right hand side of our date number and then only take the first two numbers from that right hand side.  Using RIGHT(A1,2) we give us 30.

&"/"& – This tells Excel to insert a forward slash.  It is concatenated using the ampersand character, &.  Whenever you insert something like this, you must have an ampersand before and after the string you want to insert.  And the string must be inside speech marks “/” like so.  The true is same for the other use of the forward slash. This part of the formula adds a / to 30, so we now have 30/.

MID(A1,5,2) – For this part I am telling Excel to start in the middle of the date number.  I have specified where to start in the string and how many characters to take by typing 5,2 after the A1 cell reference.  When using the MID function Excel always starts from the left.  So 5 places in from the left from 20100530 would start at the third zero. Then a comma and the 2 specifies how many characters to take starting from the fifth position.  This adds the month to our date, so we now have 30/05.

&"/"& – This is second use of the forward slash. This part of the formula adds a / to 30/05, so we now have 30/05/.

LEFT(A1,4) – The final part of the this formula concatenates the year to our nearlyconcatenate in excel completed date.  Using the LEFT function tells Excel to start from the left, and then after the comma simply specifiy how many character to take.  In our case its 4 characters which represents the 2010 we need to complete the date.  So we now have a finished date in UK format now displaying 30/05/2010.  Finally copy the cell and then do paste special and click values.  This removes the formula and replaces it with the value of our now worked out date.

3 thoughts on “How to Concatenate in Excel using LEFT, MID and RIGHT String Functions

  1. Suzee

    This formula works but I have a strings of numbers that look like this 9999998601298666RACOSTA how do I get rid of the name on the right and how do I get rid of the 6 nines. in the same formula. I can do it indivial but it keep give me an error if I have a right and left.

  2. Excel Geek

    If it is always six nines on the left, then 10 numbers and then RACOSTA then try this…
    =mid(A1,6,10) This will give you 8601298666. Just change the A1 reference to whatever your cell reference is.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>