Formula for Text to Numbers

[Bertha]

Is there a formula for converting Text to Numbers? I would like to change Text entered in the cells of A1 and B1 to Numbers in the cells C1 and D1, Text entered in A2 and B2 to Numbers in C2 and D2, etc. *while* I am typing them in, not *after* the colums A and B have been filled in completely.


For instance:

The desired output after typing in Text in the cells in colums A and would be as follows in the cells in colums C and D:

A B C D

fourteen	six	14	6
five	three	5	3
two	zero	2	0
twenty-eight	sixty-three	28	63

<tbody>
</tbody>

Thank you very much for any assistance,

Bertha

 

[mole999]

to achieve a auto adjust of test to number I would suggest is nigh on impossible, the vba needs to be triggered and you won't trigger until you enter, I'm sure someone can come up with a vba that would take your typed value and produce a number though I would think that would be part of a look up (and that may be very dependant on spelling)

 

[Rick Rothstein]

Is there a formula for converting Text to Numbers?

What is the maximum number you would need to be able to do this for?

 

[Bertha]

What is the maximum number you would need to be able to do this for?

100 or a number as little lower as possible/workable.

Best,
Bertha

 

[Bertha]

The spelling of the Text would be a consistent one: if a number is in the range 21 to 99, and the second digit is not zero, (the text form of) the number will be written as two words separated by a hyphen. (and the Text form of the number 100 would be one hundred)

 

[Rick Rothstein]

I am not sure how to do it with built-in worksheet functions, but here is a UDF (user defined function) that works...

Function Number(Word As String) As Variant
  Dim Parts() As String
  Const Nums As String = "one       two       three     four      five      " & _
                         "six       seven     eight     nine      ten       " & _
                         "eleven    twelve    thirteen  fourteen  fifteen   " & _
                         "sixteen   seventeen eighteen  nineteen  twenty    " & _
                         "thirty    forty     fifty     sixty     seventy   " & _
                         "eighty    ninety"
  If Len(Word) Then
    If LCase(Word) = "one hundred" Then
      Number = 100
    Else
      Parts = Split(LCase(Word) & "-", "-")
      Number = (InStr(Nums, Parts(0)) + 9) / 10
      If Number > 19 Then
        Number = 10 * (Number - 18)
        If Parts(1) <> "" Then Number = Number + (InStr(Nums, Parts(1)) + 9) / 10
      End If
    End If
  Else
    Number = ""
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Number just like it was a built-in Excel function. For example,

=Number(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Bertha]

I am not sure how to do it with built-in worksheet functions, but here is a UDF (user defined function) that works...

Function Number(Word As String) As Variant
  Dim Parts() As String
  Const Nums As String = "one       two       three     four      five      " & _
                         "six       seven     eight     nine      ten       " & _
                         "eleven    twelve    thirteen  fourteen  fifteen   " & _
                         "sixteen   seventeen eighteen  nineteen  twenty    " & _
                         "thirty    forty     fifty     sixty     seventy   " & _
                         "eighty    ninety"
  If Len(Word) Then
    If LCase(Word) = "one hundred" Then
      Number = 100
    Else
      Parts = Split(LCase(Word) & "-", "-")
      Number = (InStr(Nums, Parts(0)) + 9) / 10
      If Number > 19 Then
        Number = 10 * (Number - 18)
        If Parts(1) <> "" Then Number = Number + (InStr(Nums, Parts(1)) + 9) / 10
      End If
    End If
  Else
    Number = ""
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Number just like it was a built-in Excel function. For example,

=Number(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

I am highly impressed, and very thankful!

Bertha