Macro to Format or Convert the Data from the multiple columns to their respective Format
'To Convert the Number stored in a Text Format to Number Format
Method-I :
Sub Convert_Number2Text2Number()
For Each xCell In Selection
xCell.Value = CDec(xCell.Value)
Next xCell
'To Convert the Number Format as to show Negative Numbers in (Closed Brackets) with Red Color
Selection.NumberFormat = "0_);[Red](0)"
Eg : -123 = (123)
'To Convert the Number to Text Format
'First convert the Data format to Text format Selection.NumberFormat = "@"
'Next convert each cell format to Text format
For Each xCell In Selection
xCell.Value = CStr(xCell.Value)
Next xCell
End Sub
--------------------------------------------------------------------------------------------------------------
Method-II :
'To Convert the Number stored in a Text Format to Number Format ;Dates stored in a Text format to Date Format ; Percentages stored in a Text format to Percentage.
The following Macro formats/converts the Data from the Multiple columns to their respective format by default.
Sub Format_Data()
Dim XlSht As Object
Set XlSht = Sheets("MySht")
For i = 1 To 13
Range(CStr(Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI")) & ":" & CStr (Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI"))).TextToColumns DataType:=xlDelimited
Next
End Sub
'To Convert the Number stored in a Text Format to Number Format
Method-I :
Sub Convert_Number2Text2Number()
For Each xCell In Selection
xCell.Value = CDec(xCell.Value)
Next xCell
'To Convert the Number Format as to show Negative Numbers in (Closed Brackets) with Red Color
Selection.NumberFormat = "0_);[Red](0)"
Eg : -123 = (123)
'To Convert the Number to Text Format
'First convert the Data format to Text format Selection.NumberFormat = "@"
'Next convert each cell format to Text format
For Each xCell In Selection
xCell.Value = CStr(xCell.Value)
Next xCell
End Sub
--------------------------------------------------------------------------------------------------------------
Method-II :
'To Convert the Number stored in a Text Format to Number Format ;Dates stored in a Text format to Date Format ; Percentages stored in a Text format to Percentage.
The following Macro formats/converts the Data from the Multiple columns to their respective format by default.
Sub Format_Data()
Dim XlSht As Object
Set XlSht = Sheets("MySht")
For i = 1 To 13
Range(CStr(Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI")) & ":" & CStr (Choose(i, "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "Y", "AI"))).TextToColumns DataType:=xlDelimited
Next
End Sub
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------