#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
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" |
#4
|
||||
|
||||
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] |
Tags |
alphabet to numeric, convert, formula |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting Alphanumeric & Numeric values | slovenc0417 | Excel | 2 | 06-07-2013 07:58 PM |
Handle Text / Numeric values in SSRS while Export To Excel | achuki | Excel | 5 | 02-07-2012 02:14 PM |
Convert numeric value into words | KIM SOLIS | Excel | 5 | 09-12-2011 10:53 PM |
[How To] Generate Alpha Numeric Values in Excel 2010 | stnicholas81 | Excel | 1 | 07-25-2011 01:31 AM |
Running alphabets at left margins and pinpoint cites at right margin | ghumdinger | Word | 3 | 05-05-2011 02:01 AM |