How to convert contents of column from numeric data type to text

[moondaddy]

you know how sometimes when you paste a value into a cell it reads like a
number but Excel thinks its text and aligns it to the left and gives you a
little dropdown menu with options such as keep this value as text or to
convert it to numeric data. I have a whole column of mostly numbers (and
excel is storing them as numeric data) and want to convert them to text. I
don't have that menu option available since excel thinks they're numbers.
Is there a way to convert numeric data to text?

Thanks.

 

[Pete_UK]

If you don't mind a helper column, then you could enter this formula in
a column somewhere:

=""&A1

and copy down, assuming your data starts in cell A1.

You could fix the values using Paste Special, then copy the text values
to overwrite the numeric ones and delete the helper column.

Hope this helps.

Pete

 

[Sandy Mann]

Copy an empty cell - but make sure that it is truely empty and then select
your text numbers and Paste Special > Add. This should change the text into
real numbers.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

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

 

[Garage YaKa]

-First select Range to convert:

Sub ConvertValueText()
For Each c In Selection
c.Value = "'" & c
c.Value = c
Next c
End Sub

Cordialy JB

 

[Pete_UK]

Sandy,

the OP wanted to do the opposite of this, i.e. turn numbers into text
values.

Pete

 

[Sandy Mann]

Sandy,

the OP wanted to do the opposite of this, i.e. turn numbers into text
values.

Yes so I see from a re-reading of the OP - nothing else for it I'm going to
have to learn to read! <g>

--
HTH

Sandy
In Perth, the ancient capital of Scotland

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

 

[Saruman]

I have the same problem with thousands of numbers at work and found this
seems to work.

Excel gives you a hint on what is needed as the dropdown message says the
cells are either stored as text or are preceeded by an apostrophe. You need
to preceed every number with an apostrophe!

A simple way to do this, is to type an apostrophe in a blank cell. When you
click off the cell, the apostrophe disappears but is still in the cell as it
is a nonprinting character. Now copy the cell, highlight the range of
numbers that you need to see as text, right click in the range selected and
Paste Special. When the Paste Special menu appears, in the Operation part of
the menu, click the Add radio button and then click the OK Button.

Voila, Numbers stored as text!

Saruman