Using Excel as Middleware to change data the way you want

  • 27 September 2019
  • 0 replies
  • 74 views

The other day I was presented a questions about a date not being presented the way the customer wanted. In Spanish typically months are not capitalized but this customer wanted to have the month capitzalied for all of their Spanish documents. At first I gave them the idea to create a formula field in Salesforce to see if they can reformat the date the way they wanted. This option was not possible as it would take weeks of approval and testing. 

 

We were now stuck with option 2 and that was to create an excel file that would get the date from Salesforce and we will have to translate the date as needed. 

 

I like to create two tabs in the Excel a Sheet for Input and the other Sheet for Output. The Input sheet is used for the values that coming from Salesforce. In this example it will be the date value that they are trying to translate. 

 

4791iB683E48B10F64D29.jpg

 

Also, below the circled date you will notice a table. This table is for all of the months in English with the transtaion into spanish in the column next to it. This table will be used for the trantations. 

 

You will also notice that on the right of the circle I have parsed out the date. (September / 24 / 2019). This will also be used later in the output sheet. 

 

Below is a screenshot of my Output Sheet.

4792i9AF0BF0C35FE2B98.jpg

 

The Cell B2 containts a formula. It is a VLOOKUP that is looking at the Month that we parsed out.

=VLOOKUP(Input!C1,Input!A4:B15,2,FALSE)&" " & Input!D1&", " &Input!E1

The cell we are looking at is in the Input Sheet Cell C1. In our example this is "September". We are also looking at the table located in Sheet Input CellsA4 to B15. This will match September and give us the correct Spanish Translation.

 

&" " & Input!D1&", " &Input!E1

The rest of the formula is going in and getting the correct Date and Year from what we parsed out earlier. Its looking at Sheet Input and getting Cell D1 and adding a "," and they getting Cell E1 they year. 

 

Once the Formula has been created and the data is displaying the way you want it you will need to rename the cell. In our example we renamed the Cell to ChangedDate. This will become the new tag we can use on our all documents that will need to have this data manipulation. 

4795i041B3E993EBB2B1B.jpg

 

Below are a few links that are helpful to guide you further. 

Excel As Middleware  

Video for Excel as Middleware 

 

 

 

 

 


0 replies

Be the first to reply!

Reply