Writing a macro for '=' and removing commas

[pccvet]

Sorry if this seems like a silly question but todya is my first every macro experience!

I am tryimg to create a macro that coverts a string of numbers into a sum:
I.e. I want to turn
£7,296.25, £2,245.00, £1,122.50, £561.25

into
=7296.25+2,245.00+1122.50+561.25

So far I have added in thre plus signs and am getting variable results using the format facilities to make it ignore the commas within the number. But I can't work out at all how to add in the equals sign to the start of the string of numbers. Am sure it must be simple?

Thank you!

 

[Andrew Poulsom]

Welcome to MrExcel.

See if you can adapt this:

Sub Test()
    Dim Str As String
    Str = "£7,296.25, £2,245.00, £1,122.50, £561.25"
    ActiveCell.Formula = "=" & Replace(Replace(Replace(Str, ", £", "+"), "£", ""), ",", "")
End Sub

 

[hiker95]

pccvet,


Welcome to the MrExcel forum.


Sample raw data before the macro:

Excel Workbook
	A
1	7,296.25, 2,245.00, 1,122.50, 561.25
2	1,111.11, 2,222.22, 3,333.33, 444.44
3
Sheet1

After the macro:

Excel Workbook
	A
1	11,225.00
2	7,111.10
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub StrToFormula()
' hiker95, 12/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=599492
Dim lr As Long, r As Long, s As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr Step 1
  If InStr(Range("A" & r), ", ") > 0 Then
    s = Replace(Range("A" & r), "£", "")
    s = Replace(s, ",", "")
    s = Replace(s, " ", "+")
    With Range("A" & r)
      .Formula = "=Sum(" & s & ")"
      .NumberFormat = "£#,##0.00"
    End With
  End If
Next r
Application.ScreenUpdating = True
End Sub

Then run the StrToFormula macro.

 

[pccvet]

Thank you so much - this worked and now I am much happier.

Merry Christmas!

 

[hiker95]

pccvet,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.

Merry Christmas, Happy Holidays, and a Healthy and Happy New Year.