VBA to Remove hyphens and some other text in only one column of a Table

[captainxcel]

Hello,
I have a table (list object) called balances_table which starts in cell A1 and is the only data on on a particular worksheet. I paste data into the table from a downloaded csv file as below (a few sample rows included). I'd like to write a macro to remove the strings "Account Number: " and "-" from the Account column so that the account column has only numbers like 11112222 and 33334444. The code I wrote (see below table) is not working exactly as planned. It does clean up the Account column exactly as I wanted, but it is also removing negative signs from the Balance column (which is in column C), which I do not want to happen. What am I doing wrong?
Thanks!

Account	Name	Balance
Account Number: 1111-2222	Smith Foods	-125.73​
Account Number: 3333-4444	Johnson Supermarkets	1250.99​

Sub clean_up_acct_col()
Columns("A:A").Replace What:="-", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
Columns("A:A").Replace What:="Account Number: ", Replacement:="", _
LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[kevin9999]

Try the following on a copy of your sheet:

Sub clean_up_acct_col()
    With Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        .Value2 = Evaluate("Substitute(Substitute(" & .Address(, , , 1) & ",""Account Number: "",""""),""-"","""")")
    End With
End Sub

Before:

Book1
	A	B	C
1	Account	Name	Balance
2	Account Number: 1111-2222	Smith Foods	-125.73
3	Account Number: 3333-4444	Johnson Supermarkets	1250.99
Sheet1

After:

Book1
	A	B	C
1	Account	Name	Balance
2	11112222	Smith Foods	-125.73
3	33334444	Johnson Supermarkets	1250.99
Sheet1

 

[captainxcel]

Thank you kevin9999! Works great. I'll dig into the code this weekend to improve my knowledge. I kinda figured a with / end with would be needed.

 

[kevin9999]

Thank you kevin9999! Works great. I'll dig into the code this weekend to improve my knowledge. I kinda figured a with / end with would be needed.

Happy to help, and thanks for the feedback