Deleting a text in multiple cells

Question

i cant manage to properly delete a text in multiple cells in a column in Excel. The sheet looks like this:

123 456 7|
123 456 7|
123 456 7|

I just want to get rid of 123 456. (By the way, 123 456 7 are all in one cell)

I think there's an error in my code:

Sub RemoveText()

Dim cell As Range

For Each cell In Range("A1:A3")
    cell.Value = Replace(cell.Text, "123 456", "", Count:=2)
Next

End Sub

@trill https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 — Gaterde, Apr 23 '18 at 14:56
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 — Alisson Bezerra, Apr 23 '18 at 14:56
I just copied and pasted your Sub here and it worked. What kind of error do you get? — Alisson Bezerra, Apr 23 '18 at 14:59

score 2 · Answer 1 · answered Apr 23 '18 at 14:57

2

You need to use cell.Value also within the replace function

Sub test()

Dim rng As Range
Set rng = Range("A1:A3")

For Each cell In rng
    cell.Value = Replace(cell.Value, "123 456", "")
Next cell

End Sub

answered Apr 23 '18 at 14:57

FloLie

score 2 · Accepted Answer · answered Apr 23 '18 at 15:04

2

No need for a loop or .value vs. .text. Simple one liner will do:

Range("A1:A3").Replace "123 456","",xlPart

Be mindful that the replace function can tie up resources when used on formula heavy and or data intensive ranges.

answered Apr 23 '18 at 15:04

Scott Holtzman

2 Answers2