A lot of problems the users can have in Excel relate to the formatting of the numbers and values. One of the most common issues is how a user needs to deal with Text formatting.
In the example below, we will show how to convert text to numbers in Excel with the help of Visual Basic for Applications (VBA).
Convert Text in Specific Columns
For our example, we will use the list of random numbers located in column A, formatted as text:
Since the numbers are currently formatted as text, they are all located on the left side of the cells.
We can convert these numbers manually, by going to the Home >> Numbers >> Format Cells option or we can write the code.
To write the code in VBA, we will press a combination of ALT + F11 and we will then right-click anywhere on the left side of our module window:
In our module, we will then input the following code:
Sub ConvertToNumbers() Columns("A").Select Selection.NumberFormat = "0" End Sub
This code, called simply ConvertToNumbers, takes column A and formats all the values in the column as “0”, which is a format for numbers without decimals, regardless of current format and the numbers that are in the column.
We can access this code in the module and run it by pressing F5 or by clicking on the „play“ button, as shown in the picture below.
Once we do press it, we will see that the format of our column A has been changed:
We can define many ways in which the user can access our code (without the need for going into the module), and we will show one of them in the example below.
Convert Text in Desired Columns
Now, the code above is hardcoded, meaning that when we run it, we will always change the formatting in column A. But what if we wanted to choose the column in which we want to change the formatting?
For that purpose, we can use the following code:
Sub ConvertToNumbers2() Dim col As Range Set col = Application.InputBox(Prompt:="Input any cell that is located in your column." & vbCrLf & "For example B11", Type:=8) col.EntireColumn.Select Selection.NumberFormat = "0" End Sub
In the code above, we first declare the “col” variable as a range. Then we set this variable to be equal to the InputBox which will ask the user to input any cell that is located in the column that we want to change the format for. With “vbCrLf” we insert a line break, to separate two statements.
We can see that our InputBox returns the range value (type number 8).
After the user designates the cell, we select the entire column where that cell is located with:
Finally, we change the format of this column with:
Selection.NumberFormat = "0"
To give access to the code for our users, we will create a user form by going to Developer tab >> Insert >> Form Controls >> Button:
Once we do define the place where the button will be located, we will name it (Choose column to format), then right-click on it and select Assign Macro:
When we click on it, we will assign our code (ConverToNumbers2) to the button:
We will create write down random numbers in column E, format them as text, and then click on our button. The following picture will appear:
We will input any cell from column E (in our case cell E2) and click OK. As a result, entire column E will be formatted: