![]() |
|
#1
|
|||
|
|||
|
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 |