View Single Post
 
Old 07-05-2014, 12:54 AM
macropod's Avatar
macropod macropod is offline Windows 7 32bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
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