convert numbers to text

[bMunny]

Hi Everyone

I import a column of numbers from a database to Excel.
The column comes in as numbers. I need to convert these
to text but it's not exactly working for me. I need it in
text because I'm using a VLookup and it requires the data
types to be the same (at least I think it does). Let's
say I have 310 (number) and I want to convert it to 310
(text). I tried using the TEXT function but what do I
input as the second parameter?

BTW, I did try formatting the column to text but it
doesn't work. It only works if I format the column, then
go into each cell, delete its contents and re-enter the
value. Then my Vlookup formula works.

Any ideas?
Any help would be fantastic. Thanks in advance!!

 

[Vasant Nanavati]

Try copying a blank cell, selecting the problem data, and doing a Paste
Special | Add.

 

[Debra Dalgleish]

In your Vlookup formula, add a zero to the lookup value, and the text
string will be converted to a number. For example, instead of:

=VLOOKUP(H1,A1:E12,3)
use
=VLOOKUP(H1+0,A1:E12,3)

 

[Vasant Nanavati]

Nice one, Deb; I didn't realize that would work!

 

[Guest]

Thanks Debra!

-----Original Message-----
In your Vlookup formula, add a zero to the lookup value, and the text
string will be converted to a number. For example, instead of:

=VLOOKUP(H1,A1:E12,3)
use
=VLOOKUP(H1+0,A1:E12,3)



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[onedaywhen]

Is there any advantage of that over the VALUE() function? (Stephen, are you there?!)

--

 

[Jon Barchenger[MS]]

Good afternoon -

Try using the following:

=Text(<cellref>,"###")

This will make the value text.

Let me know if you need further help with this issue.

Thanks,
Jon Barchenger

--------------------
**Content-Class: urn:content-classes:message
**From: "bMunny" <[email protected]>
**Sender: "bMunny" <[email protected]>
**Subject: convert numbers to text
**Date: Mon, 1 Dec 2003 14:47:38 -0800
**Lines: 18
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: 7bit
**X-Newsreader: Microsoft CDO for Windows 2000
**Thread-Index: AcO4XR6zdEA2N4+eTB2DfOcC3zJBWQ==
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa06.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:174629
**NNTP-Posting-Host: tk2msftngxs01.phx.gbl 10.40.2.125
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**Hi Everyone
**
**I import a column of numbers from a database to Excel.
**The column comes in as numbers. I need to convert these
**to text but it's not exactly working for me. I need it in
**text because I'm using a VLookup and it requires the data
**types to be the same (at least I think it does). Let's
**say I have 310 (number) and I want to convert it to 310
**(text). I tried using the TEXT function but what do I
**input as the second parameter?
**
**BTW, I did try formatting the column to text but it
**doesn't work. It only works if I format the column, then
**go into each cell, delete its contents and re-enter the
**value. Then my Vlookup formula works.
**
**Any ideas?
**Any help would be fantastic. Thanks in advance!!
**