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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
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
58,912
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
58,912
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
58,912
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.
 

Forum statistics

Threads
1,140,937
Messages
5,703,264
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top