It’s odd, but facing a potentially lot shorter life expectancy, I need to prepare to become more expendable in personal life.
This means that I need to document or/and automate a lot of duties.
In this case, it is administrative work as custodian for my brother that is based on scripts, Excel sheets and manual steps.
In order to lessen these steps, I wanted to get the formatted value of certain Excel WorkSheet cells so I could concatenate them in other places.
It appears that neither the CONCATENATE function (nor the more recently introduced CONCAT function) nor & concatenation operator just take the unformatted value of the cell and put that in as text.
Note that the TEXTJOIN function is basically a CONCATENATE function with an extra delimiter parameter, so it does not format text.
One format I needed was YYYY-MM-DD, basically the ISO-8601 date format.
The Excel format string for this is yyyy-mm-dd;@, and the corresponding formula to apply it on cell L5 is =TEXT(J5, "yyyy-mm-dd;@")
Based on [Wayback] Get Formatted Value of Cell in Excel – Stack Overflow:
Use the
TEXT()function:
TEXT(value, format_text)
So if the value is 23.5 and you pass=TEXT(A1, "$0.00")it will return$23.50
Source: http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx
At a later stage, I will look into the actual format strings.
References:
- [Wayback] CONCATENATE function – Office Support
- [Wayback] TEXT function – Office Support
- [Wayback] TEXTJOIN function – Office Support
- [Wayback] Combine text and numbers – Excel
- [Wayback] Get Formatted Value of Cell in Excel – Stack Overflow
–jeroen
The Wiert Corner - irregular stream of stuff 