Hello Excellers, welcome to another #macromonday blog post in my Excel series. If you’re an Excel user, there’s a good chance you’ve had to convert text to numbers at some point. Maybe you needed to do some math with a list of phone numbers, or you had a table of data with product codes that needed to be converted into numbers for analysis. Im my experience, Excel users find this process a tedious. If so, you can use a handy macro that does the job for you. In this blog post, we’ll show you how to create and use the macro. So let’s get started!
Why Convert Text To Numbers?
So, if you find yourself in a situation where your numbers look like text, you can not add, subtract, divide, multiply or perform any kind of operation. It is at this point that users notice Excel is not calculating as expected.
Identify Numbers Formatted As Text String
Microsoft normally does a great job at automatically identifying the correct type of data in its cells and converting it automatically for us. Somtimes however, this process does not get it right. It is easy to spot the incorrect cell format with text as numbers. The cell contents will be left aligned (as in the case fo text strings even though the cell contain a number). Excel also displays a green triangle in the top left of each cell with this type of content. Select any of the cells, and an error will be displayed. Select it and the pop up menu appears with the top line warning that the cell contains a number formatted as text. Each cell with this formatting displays the green triangle.
Manually Convert Text To Numbers
This article details how to write and use a simple Excel VBA macro to convert Excel text to numbers. You could always choose to doo this manually, but in my experience users find this method a little fiddly. But, for clarify an training purposes follow the steps below to manually change text to number in Excel. There are a number of ways to achieve the same result, this is just one of them using the ‘Convert To Number’ option.
- Select the range of cells to convert
- Click the yellow exclamation mark
- Select Convert to Number
The numbers are now correct and the green triangles are gone.
Macro To Convert Text To Numbers
Ok. so let’s do the same thing as the manual process but a lot quicker and a lot easier in my opinion.
Step 1. Open The Visual Basic Editor | Insert A New Module
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
Before we begin to write any code you need to decide where to store the code. There is a choice
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What’s the difference?
If you save the Macro in your Personal Macro workbook, it will be available in your Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook. In this instance, I may want to reuse the VBA code repeatedly so that in this instance, I store it in my Personal Macro Workbook. When you select this option, Excel creates (if it does not already exist) and saves the Macro in that location. By default, this macro workbook is named Personal.xlsb. I intend (and do) use this Macro repeatedly, so I store it in my Personal Macro Workbook. Right, let’s get the code written.
Step .2 Naming The Macro
The next step is to name the macro. In this example I have named the macro as ConvertToNumbers. Exce automatically inserts the End Sub code line once I insert the macro name. All other lines of code sit between these two lines.
Step 3. Use The With Statement In Excel VBA
The method I use in this macro to convert text to numbers is the With statement. The syntax of this statement is
The object is the selection of cells, and the code will change the properties of the cell formating. Hence the lines of code are simple.
Step 4. Finishing The VBA Code
The finished code looks like below. Once formatting has been applied to the cell, the macro ends.
Other Useful Excel Macros
Need Some Extra Help With Excel?
