Trouble w/ Text Function

[ChopperJim]

The Excel spreadsheet has thousands of rows of birthdates. The admin used a Format, Cells, Number, Custom and then selected mmddyyyy and all was well until the recipient said they need that column in a text format. When I try to simply change the cell to a text format, it converts the mmddyyyy to a five digit number - not sure why. When I use Data, Text-to-Columns command, I lose the 0's on single digit days & single digit months. When I try the text function / formula; =text(A2,"0") it doesn't convert the custom format at all. I'm certain it's user error, but I'm still stumped!

Any suggestions, short of re-entry? Thanks in advance!

Rgds,
El Viejo

 

[Joe4]

When I try to simply change the cell to a text format, it converts the mmddyyyy to a five digit number - not sure why

Because Excel stores dates as numbers, specifically the number of days since 1/1/1900.

Try:
=TEXT(A2,"mmddyyyy")

That will convert it to text and maintain all leading zeroes.

 

[ChopperJim]

JM14,

Thanks for the reply. Truth is, I have tried that several times w/o success. Sorry I didn't share that particular attempt w/ you earlier. It's good advice tho, when the cell holds a date-formatted entry. However, the real culprit here (at least I think so) is the fact that the original entry is in a custom format (Format, Cells, Numbers, Custom then mmddyyyy). I'm guessing that is the key factor that's making this so challenging. Your thoughts?

Rgds,
El Viejo

 

[Joe4]

If you select an entry, what displays in the formula bar?

If I format a cell as Custom format "mmddyyyy", and enter 5/10/2005 in the cell, it will display the cell as 05102005, but if I click on that cell I see 5/10/2005 in the formula bar.

Then if I apply the formula I posted in the previous post to it, it converts it 05102005 (the actual underlying value, not just what is displayed in the cell).

So, what do you see in the formula bar, and what exactly are you trying to convert it to?

 

[ChopperJim]

Thanks for your response and patience.

The A2 cell reads 09041965 for Sept 4th, 1965 & the formula bar reads 9/4/1965.

I entered the text-function into B2, =TEXT(A2,"mmddyyyy") and hit return and all I see is, =TEXT(A2,"mmddyyyy") in B2. No change, no action, no converstion - am I doing something wrong?

Rgds,
El Viejo

 

[Joe4]

Whenever you type in a formula into a cell, and it returns the literal text of that formula instead of your expected value, it means that you have entered the formula as Text.

Highlight the cell (column) you are entering the formula into, change the format to General, then re-enter your formula.

 

[ChopperJim]

jm14,

Brilliant - thank you very much.

Rgds,
El Viejo

PS: I knew it was user error - but that's all I knew. Thanks again!

 

[Joe4]

Your welcome.

It can be confusing, because usually the Formats usually pertain to how the data is displayed, not how it is entered (at least with numbers and dates). But if you have the format selected as Text, then anything your type into that cell will be treated literally as Text, some formulas won't work.

By the way, using the TEXT function is what ensures that the result is TEXT and maintains leading zeroes.