Macro to extract numbers from a text string, perform math operations and return numbers back to text string.

[chinleongong]

I need to convert commentaries in excel on financial data from dollars to euros every month. Rather than manually calculating and converting the numbers and changing the $ sign to €, is it possible to write a macro that extracts numbers from a text string, multiply the numbers by an exchange rate and replace the calculated numbers back to text string? For example: Assuming an exchange rate of 1.48, I would like to change

"The variance is made up of project A (+$100m), project B (-$75m) and project C (+$25m)."

to

"The variance is made up of project A (+€67.6m), project B (-€47.3m) and project C (+€16.9m).”


Thanks in advance for your help.

 

[JoeMo]

Lightly tested, but you can try this on a copy of your worksheet. Assumes your text strings are in col A starting in A1, and the only numbers in any specific string are the ones you want to convert. Change the # sign to a euros sign where noted by the comment - I didn't have the euro symbol available.

Sub DollarsToEuros()
Dim c As Range
Const xRate As Double = 1.4
Application.ScreenUpdating = False
With CreateObject("VBScript.regexp")
       .Global = True
       .Pattern = "\d+"
       For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
              If .test(c.Value) Then
                     Set Matches = .Execute(c.Value)
                     For Each Match In Matches
                                   c.Value = Replace(c.Value, Match, Round(Match / xRate, 1))
                                   c.Replace "$", "#"  'change # to euro symbol
                     Next Match
              End If
              Next c
       End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Joe, a couple of comments.

1. I'm guessing that sometimes the original dollar values may contain decimals. eg (+$100.5m) If so, your code will give strange results for those sections.
2. Your code will also give strange results if an early match is contained within a subsequent number.
eg "The variance is made up of project A (+$10m), project B (-$100m) and project C (+$110m)."

 

[Rick Rothstein]

The following macro assumes all the values in Column A are of the same currency (all Dollars or all Euros). Also, like Joe, I used the # symbol in place of the Euro symbol, so you will have to replace all of the # symbols (highlighted in red) with the Euro character from your font. The following macro is a toggle... run it, type in the conversion rate which I think is Euros-per-Dollars (for your posted example, it would always be the 1.48 you posted no matter which symbol is showing in the cells) when asked and the calculated conversion will appear in the cells. Note that to help minimize (but not eliminate) the loss of accuracy if you toggle the sheet twice... Dollars to Euros then Euros back to Dollars... I report the values to two decimal places instead of the one your example used.

Sub DollarEuroToggle()
  Dim R As Long, X As Long, Z As Long, XchngRate As Variant, Data As Variant
  Dim CurrentSymbol As String, OtherSymbol As String, Parts() As String, Amt() As String
  XchngRate = Application.InputBox("What is the current exchange rate?", Type:=1)
  If XchngRate = "False" Then Exit Sub
  CurrentSymbol = IIf(WorksheetFunction.CountIf(Columns("A"), "*$*"), "$", "#")
  OtherSymbol = Mid("$#$", InStr("$#$", CurrentSymbol) + 1, 1)
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Parts = Split(Data(R, 1), CurrentSymbol)
    ReDim Amt(0 To UBound(Parts))
    For X = 1 To UBound(Parts)
      For Z = 1 To Len(Parts(X))
        If Mid(Parts(X), Z, 1) Like "[!0-9.]" Then
          Amt(X) = Left(Parts(X), Z - 1)
          Parts(X) = Mid(Parts(X), Z)
          Exit For
        End If
      Next
      If CurrentSymbol = "$" Then
        Parts(X) = Format(Amt(X) / XchngRate, "0.00") & Parts(X)
      Else
        Parts(X) = Format(Amt(X) * XchngRate, "0.00") & Parts(X)
      End If
    Next
    Data(R, 1) = Join(Parts, OtherSymbol)
  Next
  Range("A1", Cells(Rows.Count, "A").End(xlUp)) = Data
End Sub

EDIT NOTE: I just did a quick test and my code seems to be nearly 15 times faster than Joe's code (1000 rows of data similar to what you posted as an example... my code ran in 0.016 seconds, Joes ran in 0.219 seconds).

 

[chinleongong]

Lightly tested, but you can try this on a copy of your worksheet. Assumes your text strings are in col A starting in A1, and the only numbers in any specific string are the ones you want to convert. Change the # sign to a euros sign where noted by the comment - I didn't have the euro symbol available.

Sub DollarsToEuros()
Dim c As Range
Const xRate As Double = 1.4
Application.ScreenUpdating = False
With CreateObject("VBScript.regexp")
       .Global = True
       .Pattern = "\d+"
       For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
              If .test(c.Value) Then
                     Set Matches = .Execute(c.Value)
                     For Each Match In Matches
                                   c.Value = Replace(c.Value, Match, Round(Match / xRate, 1))
                                   c.Replace "$", "#"  'change # to euro symbol
                     Next Match
              End If
              Next c
       End With
Application.ScreenUpdating = True
End Sub

Hi Joe,

Thank you so much for your response. The code that you've written works perfectly on the example that I've given!

However, there are occasions where the project names do contain numbers in them as well. I tried the code on my real life text and for the numbers that are part of the project names they got converted as well. Below is the original and converted text for illustration:

Before conversion:
"Delta 121 (22 units) +$37.4M not budgeted, Redox 116 +$17.3m"

After conversion:
"Delta 86.2.9 (15.7 units) +€26.2.9.2.9M not budgeted, Redox 82.9 +€12.1.2.1m"

When I checked the numbers that were being converted, there seems to be a pattern in the errors of calculation. That is, the conversions appeared to be correct for all the numbers if we consider only rounded whole numbers, or before the first decimal point (eg. 121/1.4 = 86, 37.4/1.4 = 26, 17.3/1.4 = 12 etc.). But after that, almost all of them had recurring "2.9" showing.

I'm wondering if we could specifically convert only the numbers that are between a "$" sign and letter "m" (not case-sensitive)?

Thanks again for your time and great expertise.

Best regards,
Chin

 

[Rick Rothstein]

I'm wondering if we could specifically convert only the numbers that are between a "$" sign and letter "m" (not case-sensitive)?

Did you get a chance to try the code I posted in Message #4 yet?

 

[chinleongong]

Hi Rick,

You are a genius! Your code is perfect!!! It converted and avoided all the numbers that I needed to converted and avoid. And the input box for the rate is the extra bonus on top of the perfection.

Thank you so much for your help.

 

[JoeMo]

Joe, a couple of comments.

1. I'm guessing that sometimes the original dollar values may contain decimals. eg (+$100.5m) If so, your code will give strange results for those sections.
2. Your code will also give strange results if an early match is contained within a subsequent number.
eg "The variance is made up of project A (+$10m), project B (-$100m) and project C (+$110m)."

Good comments Peter - thanks. Obviously, I did not put enough thought into this one.