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 nearly 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.

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.

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.Good article, thanks.

I also just found this video which helped me get these functions working right for me http://www.youtube.com/watch?v=zMPowDT_16Q

very helpful !

Please note that Microsoft Excel determines how far to copy cells after the fill handle double click based on the cells referred to by your formula. If there happen to be empty cells in your table, say cell A6 and B6 were blank in this example, the formula would be copied up to row 5 only. In this case, you would need to drag the fill handle down manually to concatenate the entire columns.

i want only amounts which is after second (|) some time its in 3 digits and some times 4 or 5 digits

is there any small formula

97867|Fos|350|Main|Green|2234|

97868|Fos|4500|Main|Green|46577|

i mean i want only that amount which is between 2nd and 3rd (|)

Results should be 350 and 4500 for this data

i want only amounts which is after second (|) some time its in 3 digits and some times 4 or 5 digits

is there any small formula

974867|Fos|350|Main|Green|2234|

97868|Fos|4500|Main|Green|46577|

i mean i want only that amount which is between 2nd and 3rd (|)

Results should be 350 and 4500 for this data