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