1

I have a column consisting of cells (from 1 hundred to over 100 thousands) with values like :

5
12
78.456
1235.78510
AFT780.963
ZFLQ
57180.004
...

I want a way to rapidly change the format of the cell to text. Selecting them all and manually change the format to text is not working. I've also tried =text(A1,"0"), but it cause problem for cells with numbers after a .. 123.1230 should stay like this when converted to text (and I only want a value, not a formula in the cell).

Excel show a little green ribbon in the upper left corner of the cell when there is only numbers and that the cell is correctly interpreted as text.

Is there a way to do it?

[EDIT]

Credit to the accepted answer, adding ' before the number convert it to text. Also, I`ve found another way on this website (Scroll down to "Convert numbers to text in Excel with Text to Columns wizard"). In the Data tab, you can use the converter wizard to convert to text your selection and it worked for me.

It can be done using VBA like this:

Range("A1:A11").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 2), TrailingMinusNumbers:=True
2
Sub ConvertToText()

Dim i As Long, lastRow As Long
lastRow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    Sheets("Sheet1").Cells(i, 1).Formula = "'" & Sheets("Sheet1").Cells(i, 1).Formula
Next i

End Sub

Assuming your data is in column A, use this VBA code. Open the VBA editor by pressing Alt+F11, insert a Module (Insert > Module), paste in this code and hit F5 to run.

It works by adding a single quote (') to the front of every cell, forcing Excel to interpret the value as text.

| improve this answer | |
  • I've tested it and it worked. The problem was when I was transferring data to a System i, numbers were always interpreted as numeric and cancel the transfer. I thought adding a ' would still cause problems, but it doesn't. Thanks – varnaud Aug 7 '15 at 15:36
1

Highlight the column select the Home tab in the number section select "Text" from the dropdown box. The default is usually "General".

Sorry I don't know how to paste screen grabs into the dialog box.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.