Suberb excel VBA trick to convert numbers into text for selected colunms in sheet

It is very crucial to have certain columns value format as text before it use for data transformation in your project. There may be different causes to change digits stored as numbers to text. it has multiple ways that  you can solve this problem. you can used text formula too. but, to convert values cell by cell takes a lot time and tedious task as well. There might be chances of miss few value to convert into text format.


Here is the code which will help you to ease this task.


Sub Convert_to_Text()
'
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim clmns As Range
Dim wrksht As Worksheet
Dim sht As String

Application.ScreenUpdating = False

'Set your sheet name here
sht = "Sheet1"

For Each wrksht In ThisWorkbook.Worksheets
       
        For Each clmns In wrksht.Columns("A:F")   'Set your columns here to convert into text format
       
            If wrksht.Name = "NIV" Then
                clmns.TextToColumns Destination:=Range(clmns.End(xlUp).Address), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
            End If
       
        Next clmns
       
        wrksht.Columns("A:F").NumberFormat = "@"

Next wrksht

Application.ScreenUpdating = True

MsgBox "Input columns converted into text format.", vbInformation, "Convert to Text"

End Sub