Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I obtain the displayed value instead of actual value in Excel?

I have a cell containing a date ex. "05/11/09" It is currently displayed as "11-MAY-09". How do I copy-paste or use VBA to get the string "11-MAY-09" into the cell next to it ( NOT "05/11/09")?

I can't figure it out other than piecing out the date pieces by itself.

like image 750
JC. Avatar asked Jul 08 '09 18:07

JC.


People also ask

Why is Excel displaying formula instead of result?

There are two main reasons you might see a formula instead of a result: You accidentally enabled Show Formulas. Excel thinks your formula is text.

Which options allows you to see the actual formula instead of its calculated result?

With a quick key combination you can see formulas instead of formula results in a worksheet. To show formulas in all cells press CTRL+` (that little mark is the grave accent mark key). When the formulas are visible, print your worksheet as you normally would.


3 Answers

Range("B1").Value = Range("A1").Text

Using the cell's .text instead of .value modifier will copy the text formatting instead of the raw date.

Chuck

like image 95
Chuck Dickens Avatar answered Nov 15 '22 07:11

Chuck Dickens


I believe you can use the TEXT function to format a date value to your liking.

The format string of "dd-mmm-yy" would format "05/11/09" as "11-MAY-09".

like image 22
dpmattingly Avatar answered Nov 15 '22 07:11

dpmattingly


Use the Format function.

Format("5/11/2009", "DD-MMM-YY")

This will return:

11-May-09

If case matters:

UCase(Format("5/11/2009", "DD-MMM-YY"))

returns:

11-MAY-09
like image 20
mandroid Avatar answered Nov 15 '22 08:11

mandroid