Excel VBA convert everything to text-CodePudding

I have tried converting my data to text. But it's not really text? If that makes sense.

So, this is my code

Dim rng As Range
Set rng = wb.Sheets(1).Range("A1"). CurrentRegion
rng.NumberFormat = "@"

This code will convert everything to "text" but when I select some numbers, it shows the sum at the bottom left, which text isn't supposed to do so.

2nd thing is, after I have convert it to "text", I realise I have to click on the cell, press enter, then it will really become text format...

CodePudding user response：

Convert Numbers to Text

Note that the single quote in front of a number will be ignored. Get its length or concatenate to prove it.

Option Explicit

Sub ConvertToText()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim rng As Range: Set rng = wb.Sheets(1).Range("A1").CurrentRegion
    'rng.NumberFormat = "@" ' with or without
    Dim cCell As Range
    For Each cCell In rng.Cells
        If IsNumeric(cCell.Value) Then
            cCell.Value = "'" & CStr(cCell.Value)
        End If
    Next cCell

End Sub