CODEWITHSUNDEEP
excelvbatype-conversion
Jayl
Jayl

Reputation: 31

Excel VBA convert everything to text

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 right, 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...

Upvotes: 1

Views: 5029

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 2

Related Questions