convert number to text

[Guest]

How can i convert a number to text format without then editing that cell to
force the cell to except the format change.
I need a numbers to convert to text because a download from another program
is already in text format with SS# in the cell. Then using at templet sheet
with a VLook Up formula to pull information off the first workbook.

 

[Guest]

To convert number to text, select the area you want to convert, right-click

 

[Guest]

Ester,
That is true, and the formating will say that it is text but you must edit
(double click) in each cell before it will except the change to text. I'm
looking for a way to change a group of cells in pop without have to edit
(double click) in each and every cell.

 

[Frank Kabel]

Hi
you may try the following formula in a helper column
=TEXT(A1,"0")

 

[Jim Rech]

You might select the range of data and run this macro:

Sub a()
Dim Cell As Range
Dim OldVal As String
For Each Cell In Selection
OldVal = Cell.Value
Cell.Value = OldVal
Next
End Sub

Doing Cell.Value = Cell.Value directly doesn't seem to work. This assumes
the range has the Text number format.
--
Jim Rech
Excel MVP

| Ester,
| That is true, and the formating will say that it is text but you must edit
| (double click) in each cell before it will except the change to text. I'm
| looking for a way to change a group of cells in pop without have to edit
| (double click) in each and every cell.
|
| "Ester" wrote:
|
| > To convert number to text, select the area you want to convert,
right-click
| > on format cells and choose "text".
| >
| > "RayG" wrote:
| >
| > > How can i convert a number to text format without then editing that
cell to
| > > force the cell to except the format change.
| > > I need a numbers to convert to text because a download from another
program
| > > is already in text format with SS# in the cell. Then using at templet
sheet
| > > with a VLook Up formula to pull information off the first workbook.
| > >

 

[Guest]

Frank,
I thought this was the right answer, but there was just on thing. Some SS#'s
start with the number 0 and during this formula process the 0 (zero) is left
off. Any other ideas?

 

[Guest]

Jim Rech,
I have know idea where to start to write this macro.

Column A1 : A600 is what I need to convert.

 

[Frank Kabel]

Hi
if they all have the same amount of digits try:
=TEXT(A1,"00000")
use as many zeros as required