Writing a macro for '=' and removing commas

pccvet

New Member
Joined
Dec 16, 2011
Messages
6
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Welcome to MrExcel.

See if you can adapt this:

Code:
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
 
Upvote 0
pccvet,


Welcome to the MrExcel forum.


Sample raw data before the macro:


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





After the macro:


Excel Workbook
A
111,225.00
27,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.


Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top