changing numbers to text

C

Claudia

Hi group,

I have a long list of zip codes in column A that has been
complied. the problem is that some are text and some are
not. What I mean is that I "test" them with =ISTEXT(A1)
for example, if they are text I get TRUE if not I get
False.

I want to convert all of them to Text so that I can export
them to ACcess.

I tried converting the ones that were not text by choosing
the Text under Format menue/ Cells/ Number then Text but
this doesn't convert it really. When I "test" it with
=ISTEXT(), its still not a text.

Any help is very much appreciated

C.
 
K

Kieran

Claudia,

Add another column to your data and use the function =value(CellRef).

This will give numbers as a result whereever possible.

(CellRef) refers to the cell address of the 'unclean' data
 
C

Claudia

Hi,
Thank you for responding.
But I actually want to do the opposit. I want to convert
values to text.

How can I do this?

Thanks
 
J

JulieD

Hi

you can use the =TEXT function if there is a consistent format to your
numbers,
e.g. if all the numbers are five digits long then
=TEXT(A1,"00000")
and filled down will give you text for each of these numbers - you can then
copy this column, choose edit / paste special - values to change the formula
into values.

Cheers
JulieD
(perth, australia)
 
K

Kieran

Claudia,

oops..

use the =text() function instead of =Value() function to do that.

Sorry about the confusion.
 
P

Peo Sjoblom

Use a help column

=TEXT(A1,"00000")

or

=TEXT(A1,"00000-0000")

if you use zip + 4

then copy down and copy and paste as values in place,
that will give you text

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
C

Claudia

Thank you all

Very very much
-----Original Message-----
Hi,
Thank you for responding.
But I actually want to do the opposit. I want to convert
values to text.

How can I do this?

Thanks


-
.
Click to expand...
 
Top