Saving number as text, how to automate it

Question

I have a 7702216772 number inside a cell. If I put a ' before the fist digit and click Enter Excel transforms the number to a text and puts a green triangle at the left top of the cell:

I have many rows of similar numbers all of which need to be transformed into text. However clicking each and adding ' before the first symbol and clicking Enter would take a lot of time. Is there any way to do it programatically?

I tried using formula: ="'"&H4 but it doesn't do what's expected - the green triangle never appears on the result cell.

I also tried setting cell format to Text, but the green triangle doesn't appear in that case too.

I need the green triangle to appear at the upper left corner, just like at the picture!

Is this a mobile number which is dropping the 0 at the start? Because if so just change the column to Text value before pasting. — Kyoujin, Jan 18 '18 at 12:40
In cell G4 put formula `=H4&""` and copy down as much as you need. `Copy >> Paste Special >> Values` should give you what you need. — shrivallabha.redij, Jan 18 '18 at 12:42
@Kyoujin The first digit is not zero. I tried changing column to text, doesn't work. While it says the column's format is Text the pasted numbers lack green trianle at the upper left corner. — Ans, Jan 18 '18 at 12:49

score 2 · Answer 1 · answered Jan 18 '18 at 12:41

If all your number are in a single column, the following code will do it:

Sub foo()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'get the last row with data on Column A

For i = 1 To LastRow 'loop from row 1 to last
    ws.Cells(i, "A").Value = "'" & ws.Cells(i, "A").Value 'add the ' before the number
Next i
End Sub

Change the "A" to whichever column you are using.

score 1 · Answer 2 · answered Jan 18 '18 at 12:46

1

Just Select the cells you wish to process and run this short macro:

Sub Textify()
    Dim rng As Range, r As Range
    Set rng = Selection.Cells.SpecialCells(2, 1)
    For Each r In rng
        r.Value = "'" & r.Value
    Next r
End Sub

answered Jan 18 '18 at 12:46

Gary's Student

95,722
10
59
99

score 1 · Answer 3 · answered Jan 18 '18 at 12:56

Non VBA answer; I'm using Column G in this answer but it depends on where your numbers are. You'll have to change the cell but I think you will be ok with this.

In an empty cell, enter formula: ="'"&G4
Use the fill handle or Ctrl+D to fill it down to the length of Column G's values.
Select the whole of Column G's values and copy them to the clipboard
Select the same range in Column G, right-click, select Paste Special and choose Values

score 0 · Answer 4 · answered Jan 18 '18 at 14:28

0

I have tested it now for several times and it worked always

Cells(xx, xx).FormulaR1C1 = "'" & Cells(xx, xx).Value

Same would work for ActiveCell or whatever you like.

answered Jan 18 '18 at 14:28

C. Henke

151
1
13

Saving number as text, how to automate it

4 Answers4