Excel Numbers-to-text conversion 2

[SteveBoard]

I'm using Excel 2000 as a database for a web site. The file lists addresses. I noticed the zip codes were not coming through the database search routine and it appears that some of them were entered numeric and some text. Since there are hundreds of these, I don't want to manually put an apostrophe before every zip (also don't want to have to teach someone at data entry to do that).

So is there a quick way to redefine the zip numeric as a text? I know about the format called "text" in Cells-format-numbers. But that doesn't do it; as a database, the zip number is still treated like a number. Any address with a zip code as a numeric still comes up blank.

This must be a common problem and Microsoft surely has a routine, but I can't find it.

 

[Zathras]

Try this formula. No guarantees for your particular application but it does return a text 01234 for a numeric input 1234. Interesting that when I copy and past special values, it doesn't put in a leading apostrophe, but it still knows that it's text. (See ISNUMBER and ISTEXT formulas)

A1: 1234
B1: =RIGHT(100000+A1,5)

 

[Dreamboat]

This appears to work on both numbers and text:

=TEXT(A1,"00000&quot Anne Troy

http://www.TheOfficeExperts.com

 

[SteveBoard]

Thanks to the two advisors here.

This finally worked, and I combined the Text(cell, format) functions with the ISTEXT and got perfect resolution of my problem.

Thanks a million.

 

[Draig]

Just to show yet more variation, try this formula :-
=MID("'"&A1,2,LEN(A1))
Here you at least use the apostrophe1
Barborne
Worcester
UK