How can i make numbers have text format without insering ' at each

  • Thread starter Numbers into text format in excel
  • Start date
N

Numbers into text format in excel

I have a file with 10000 numbers and I want them to be in "text" format. I do
the basics (format-cell-text) but only when I insert ' or edit the cell
(F2+ENTER)I have the right results. is there an easier way which is less time
consuming?
 
H

Harlan Grove

I have a file with 10000 numbers and I want them to be in "text" format.
I do the basics (format-cell-text) but only when I insert ' or edit the
cell (F2+ENTER)I have the right results. is there an easier way which
is less time consuming?
Click to expand...

You'd have to do the following for each column of values. Select a column of
values you want to convert to text. Run the menu command Data > Text to
Columns. Select Fixed Width in the first screen of the wizard and click the
NExt button. Immediately click the Next button in the second screen of the
wizard. Select Text in the Column data format section of the third screen of
the wizard and click the Finish button.
 
N

Numbers into text format in excel

thanks a lot!!!

Harlan Grove said:
...

You'd have to do the following for each column of values. Select a column of
values you want to convert to text. Run the menu command Data > Text to
Columns. Select Fixed Width in the first screen of the wizard and click the
NExt button. Immediately click the Next button in the second screen of the
wizard. Select Text in the Column data format section of the third screen of
the wizard and click the Finish button.
Click to expand...
 
G

Gord Dibben

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
moretext = InputBox("Enter your Text")
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub

Select the column of numbers then run the macro.

Enter an apostrophe into the inputbox.


Gord Dibben MS Excel MVP
 
Top