Excel macro to convert numbers into text for selected colunms in worksheet

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://www.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