Quick way to convert to Text

[Brent]

I am looking at a quick way to convert a column of numbers to text... Any
ideas?
thanks

 

[Gord Dibben]

Brent

"Text" as in 123 is converted to one hundred and twenty three?

See Bob Phillips' site.

http://www.xldynamic.com/source/xld.xlFAQ0004.html


Gord Dibben MS Excel MVP

 

[Sandy Mann]

Try copying an unused cell then highlighting the required column ans Paste
Special > Add
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gord Dibben]

Which works like a hot d..n when going the other way<g>


Gord

 

[Sandy Mann]

Not only can't I read - now I'm cross-eyed! <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Brent]

No- what I mean is 123 (Number format) to "123" Text format... thanks
brent

 

[Brent]

Gordon- I am importing a CSV file and I am getting a type conversion file
error on one of my
fields (a upc file). I need that filed to be imported as a nvarchar (text
format)...

I brought the file into excel and tried to convert the data to text and it
looked like
8.81E+10 and will not work for my SQl link....

Any ideas on getting the converion to match up?

Thanks
Brent

 

[Lori]

One way: In a new workbook choose Data > Import External Data > Import
Data, select text files from the drop down and locate the .csv file
then set Text as the data format for the column in the wizard.

 

[Gord Dibben]

Brent

Format the cells as number to get rid of the scientific notation.

Then save as>file type>text

The "numbers" will not revert to scientific notation in the text file.

Link that *.txt file to your SQL


Gord

 

[Brent]

Lori- it worked, but that is a H&*&*( of a way to do it- is there any other
way? You would think that it would be eaiser than this???
Thanks
brent

 

[Brent]

Gord- I could not get that to work... Loris way worked- but that is a
convoluted way of doing something that should be so simple...

 

[Lori]

Hi Brent - A general way to convert a column of numbers to text is:
Data>Text to Columns>Next>Next>Text on that column.
(Assuming tab-delimited as default)

Note however that Excel has an accuracy of 15 digits so if your csv
file contains longer strings of digits they will be rounded.

In that case you should convert the column before opening in Excel as
suggested above. Another option is to change the .csv extension
to .txt so that you get the import wizard when you open the file from
Excel.

 

[Guest]

I'm having a similar problem except some of the data in the problem columns
is showing the entire number and some of it is showing up in scientific
notation. I have tried resizing the column and all of the suggestions posted
on this thread to no avail. Does anyone have any other ideas on how to fix
this problem?

I'm saving the file from an xls format to a csv format. when I open the csv
I see the scientific notation. Some of the numbers are quite long. They are
account numbers. In the original xls file they are showing up the say way.

 

[Guest]

Correction: In the original xls spreadsheet some of the data is showing up
with the green arrow in the upper left corner of the cell. If I choose
"convert to number" I then get the scientific notation.