Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-04-2014, 12:09 PM
kjxavier kjxavier is offline Convert alphabets to numeric values Windows XP Convert alphabets to numeric values Office 2007
Advanced Beginner
Convert alphabets to numeric values
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Thumbs up Convert alphabets to numeric values

Hi!



Is it possible to convert :

Nine Hundred Sixty Three Thousand Seven Hundred Eighty One
Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight
Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four
Eight Hundred Twenty Six Thousand One Hundred Eighty Nine
Nine Hundred Three Thousand Nine Hundred Six

to numeric...

EG:
963781
878878
872784
903906

Appreciate your early reply
Reply With Quote
  #2  
Old 07-05-2014, 12:54 AM
macropod's Avatar
macropod macropod is offline Convert alphabets to numeric values Windows 7 32bit Convert alphabets to numeric values Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use a macro like:
Code:
Sub NumberStringToNumeric()
Application.ScreenUpdating = False
Dim Rng As Range, StrTmp As String, lNumber As Long, i As Long
Dim StrNums As String, StrDigits As String, StrMagTxt As String, StrMagNum As String
StrNums = "zero,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"
StrDigits = "0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,30,40,50,60,70,80,90"
StrMagTxt = "[Bb]illion,[Hh]undred [Mm]illion,[Mm]illion,[Hh]undred [Tt]housand,[Tt]housand,[Hh]undred"
StrMagNum = "000000000|,00000000|,000000|,00000|,000|,00|"
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = True
    .MatchWildcards = False
    For i = 0 To UBound(Split(StrNums, ","))
      .Text = Split(StrNums, ",")(i)
      .Replacement.Text = Split(StrDigits, ",")(i)
      .Execute Replace:=wdReplaceAll
    Next
    .MatchWildcards = True
    .Text = "0[ \-]([0-9])"
    .Replacement.Text = "\1"
    .Execute Replace:=wdReplaceAll
    For i = 0 To UBound(Split(StrMagTxt, ","))
      .Text = Split(StrMagTxt, ",")(i)
      .Replacement.Text = Split(StrMagNum, ",")(i)
      .Execute Replace:=wdReplaceAll
    Next
    .Wrap = wdFindStop
    .Text = "[0-9 |,]{1,}"
    .Execute
  End With
  Do While .Find.Found
    lNumber = 0
    Set Rng = .Duplicate
    With Rng
      While Not IsNumeric(.Characters.Last)
        .End = .End - 1
      Wend
      StrTmp = Replace(Replace(.Text, "| ", "|"), ", ", "")
      For i = 0 To UBound(Split(StrTmp, "|")) - 1
        If UBound(Split(Split(StrTmp, "|")(i + 1), " ")) > 0 Then
          If Len(Split(Split(StrTmp, "|")(i), " ")(1)) > Len(Split(Split(StrTmp, "|")(i + 1), " ")(1)) Then
            lNumber = lNumber + CLng(Replace(Split(StrTmp, "|")(i), " ", ""))
          Else
            lNumber = lNumber + CLng(Replace(Split(StrTmp, "|")(i), " ", "") & Split(Split(StrTmp, "|")(i + 1), " ")(1))
          End If
        Else
          lNumber = lNumber + CLng(Replace(Split(StrTmp, "|")(i), " ", ""))
        End If
      Next
      lNumber = lNumber + CLng(Replace(Split(StrTmp, "|")(UBound(Split(StrTmp, "|"))), " ", ""))
    End With
    .Text = Format(lNumber, "#,##0")
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub
Note: The code assumes your number strings don't include the 'and' that is common in UK English, for example (e.g. one hundred and one). It's also not really designed for handling currencies or decimals, though it should handle the numeric aspects of those OK too.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 07-05-2014, 11:34 PM
kjxavier kjxavier is offline Convert alphabets to numeric values Windows XP Convert alphabets to numeric values Office 2007
Advanced Beginner
Convert alphabets to numeric values
 
Join Date: Jul 2011
Posts: 39
kjxavier is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
You could use a macro like:
Code:
Sub NumberStringToNumeric()
...
End Sub
Note: the code assumes your number strings don't include the 'and' that is common in UK English, for example (e.g. one hundred and one). It's also not really designed for handling currencies or decimals, though it should handle the numeric aspects of those OK too.
Thank you for the effort and time! WE APPRECIATE THAT A LOT

can you please write me a macro which does the opposite as well

i.e:

742111.37
676736.06

=
SEVEN HUNDRED FORTY TWO THOUSAND ONE HUNDRED ELEVEN AND THIRTY SEVEN CENTS

SIX HUNDRED SEVENTY SIX THOUSAND SEVEN HUNDRED THIRTY SIX AND POINT SIX CENTS


NOTE: " AND POINT SIX CENTS means .06"
Reply With Quote
  #4  
Old 07-06-2014, 05:34 AM
macropod's Avatar
macropod macropod is offline Convert alphabets to numeric values Windows 7 32bit Convert alphabets to numeric values Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Please also don't keep posting the same questions in multiple threads. If you have a new question, start a new thread for it and don't continue in the existing one; if it's a related question, continue in the same thread and don't start a new one.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
alphabet to numeric, convert, formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert alphabets to numeric values Sorting Alphanumeric & Numeric values slovenc0417 Excel 2 06-07-2013 07:58 PM
Convert alphabets to numeric values Handle Text / Numeric values in SSRS while Export To Excel achuki Excel 5 02-07-2012 02:14 PM
Convert alphabets to numeric values Convert numeric value into words KIM SOLIS Excel 5 09-12-2011 10:53 PM
Convert alphabets to numeric values [How To] Generate Alpha Numeric Values in Excel 2010 stnicholas81 Excel 1 07-25-2011 01:31 AM
Convert alphabets to numeric values Running alphabets at left margins and pinpoint cites at right margin ghumdinger Word 3 05-05-2011 02:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft