Trouble w/ Text Function

ChopperJim

New Member
Joined
Oct 12, 2006
Messages
4
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 12, 2006
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 12, 2006
Messages
4

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 12, 2006
Messages
4
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,022
Members
410,647
Latest member
bernardazar
Top