Thursday, March 5, 2015

How to Convert Numbers stored in Text Format to Number Format

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

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog