A Couple Useful Excel Formatting Codes

There are a couple excel formulas that I find myself recreating over and over and over. When data comes from a client it is never in the perfect format to load into Salesforce with a tool like APEX Data Loader (as seen from the post below) here are a couple.

Date:

Typical format is something like

8/12/2009 9:30

All I want is the date without a time so here is what I use

=month()&"/"&day()&"/"&year()

In each of the parentheses put the cell you want to convert, like =month(B2)&...

Name Concatenation:

Sometimes I need to put names together or bring them apart or just change the spacing between them, you can use a combination of concatenations and the "Left", "Right", and "Mid" functions in SF as well.

=concatenate("left(B2,3)","mid(B2,5,2)","right(B2,4))

Then when you finish the first one just click on the cell, then scroll down to the end of the list that needs to be changed and highlight every line and press Fill then Down in excel. This will apply the formula to every field selected in the column. Next select all the fields that have just been created and copy them, then right click on the column they need to go and click Paste Special... then click Values then OK. This will copy the contents of the cell and not the formula so you will then be good to load!