custom function to convert numbers stored as text


Josh Craig

Hi everyone,

I know you can convert text to numbers with the whole paste multiply by 1
thing but I was wondering if anyone had seen code (or would know what code to
use) to convert numbers stored as text to numbers using a custom built VBA

Any help greatly appreciated!



Not sure what you mean, but here is a function that will convert a string
value digit to an integer value.

Function MakeNum(rng As Range)
MakeNum = CInt(rng)
End Function

To use it, assume a string value of "123456" in cell b5:

In an empty cell put formula =MakeNum(b5) > Enter.

Matthew Herbert


There is no way of knowing what you truly mean because you didn't provide an
example (as was mentioned in a prior post). However, if you have a numeric
value in the spreadsheet and the spreadsheet is treating that numeric value
as text, then here are two built-in Excel ways that should get that text into
a number.

(1) In a separate cell: =NumberTextCell+1-1. Copy (new formula cells)-Paste
(2) Place a 1 somewhere on the spreadsheet. Copy the 1-select numeric cells
that are text-Paste Special-Multiply


Matthew Herbert

Jacob Skaria

OR do you mean code to conver to numbers

Dim cell As Range
For Each cell In Selection
If cell.Text <> "" And IsNumeric(cell.Value) Then
cell.Value = Val(cell.Text)
cell.NumberFormat = "General"
End If

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question