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
wrksht.Columns("A:F").NumberFormat = "@"
Application.ScreenUpdating = True
MsgBox "Input columns converted into text format.", vbInformation, "Convert to Text"