convert number to text

R

RayG

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.
 
R

RayG

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.
 
J

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.
| > >
 
R

RayG

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?
 
R

RayG

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

Column A1 : A600 is what I need to convert.
 
F

Frank Kabel

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