The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,908 other followers

Get Formatted Value of Cell in Excel – Stack Overflow

Posted by jpluimers on 2022/01/18

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:

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: