Convert text to numbers
To convert text to number you can apply the Excel VALUE function
Example: Convert text to numbers
|
=VALUE(B5)
|
The formula uses the Excel VALUE function to convert the text numbers, captured in cell B5, to a numeric value.
|
METHOD 2. Convert text to numbers adding 0
EXCEL
|
=B5+0
|
The formula adds 0 to the text number to convert it to a numeric value.
|
Dim ws As Worksheet
With ws.Range("B5")
.Value = .Value
End With
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are converting the text in cell ("B5") to a number. Therefore, if using the exact VBA code, you need to capture the text that you want to convert to a number in cell ("B5").
Range: Select the range that captures the text that you want to convert to a number by changing the cell reference ("B5") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Worksheet Name: Select the worksheet where you are converting text to a number by changing the worksheet name ("Analysis") in the VBA code to any existing worksheet in the workbook, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: This VBA code will convert the text to a number in the same cell that the text is captured. In this example it will be cell ("B5").
With Selection
.Value = .Value
End With
End Sub
Note 1: This VBA code will convert the text to a number in the selected cell.
Dim ws As Worksheet
With ws.Range("D5")
.Value = .Value
End With
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are converting the text in cell ("B5") to a number. Therefore, if using the exact VBA code, you need to capture the text that you want to convert to a number in cell ("B5").
ADJUSTABLE PARAMETERS
Range: Select the range that captures the text that you want to convert to a number by changing the cell reference ("B5") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Range: Select the output range by changing the cell reference ("D5") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Worksheet Name: Select the worksheet where you are converting text to a number by changing the worksheet name ("Analysis") in the VBA code to any existing worksheet in the workbook, that doesn't conflict with the formula.
METHOD 4. Convert text to numbers with specific range and different output range (with For Loop) using VBA
VBA
Dim ws As Worksheet
With ws.Range("D" & x)
.Value = .Value
End With
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are converting the text in range ("B5:B10") to numbers. Therefore, if using the exact VBA code, you need to capture the text that you want to convert to numbers in range ("B5:B10").
ADJUSTABLE PARAMETERS
Output and Text Row Range: The text and output range in the same rows (row 5 to 10), which are represented by the x variable. You can select the output row range by changing the x For and To values in the VBA code.
Output and Text Column Range: Select the column text and output range by changing the column reference ("B") and ("D"), respectively, in the VBA code.
Worksheet Name: Select the worksheet where you are converting text to a number by changing the worksheet name ("Analysis") in the VBA code to any existing worksheet in the workbook, that doesn't conflict with the formula.