convert YYYYMMDD format to MM/DD/YYYY

Posted by Jeo Thomas on 12:38 PM, 21-Apr-16

Some times data is received in  YYYYMMDD for example : 20160124

The below Excel formula will help you to convert the above format to MM/DD/YY for example : 01/24/16

=DATE(LEFT(C1,4),MID(C1,5,2),RIGHT(C1,2))

Thanks

Jeo Thomas

Convert European spend amount format to normal

Posted by Jeo Thomas on 12:32 PM, 21-Apr-16

In normal format Spend amount is represented using "," (coma) as thousand separator and "."(dot) as decimal but European format is the other way round.

Below Excel formula will help you to convert Spend Amount from European to normal one.

=SUBSTITUTE(SUBSTITUTE(g2, "." , "" ), "," , ".")+0

Thanks
Jeo Thomas


Formula to extract first word in a cell

Posted by Jeo Thomas on 12:30 PM, 21-Apr-16

Below excel formula will help you to extract first word in a cell


=LEFT(A1,SEARCH(" ",A1)-1)

Thanks
Jeo Thomas

Formula to extract middle word in a cell.

Posted by Jeo Thomas on 12:23 PM, 21-Apr-16

Below excel formula will extract middle word of the first three words in a cell.


=MID(B51,SEARCH(" ",B51,1)+1,SEARCH(" ",B51,SEARCH(" ",B51,1)+1)-SEARCH(" ",B51,1))

If there are less than three words in a cell it will give output as #VALUE!

Thanks
Jeo Thomas