Thursday, 5 March 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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts