Replace String in Cell Faster

[mrmmickle1]

I have data that resembles this format:

GX14-526544
RF14-26547
UC14-659681

I am trying to replace these strings in each cell so they are now:

526544
26547
659681

I am using this method currently:

[COLOR=#0000ff]For[/COLOR] x = 2 [COLOR=#0000ff]To [/COLOR]LastRow [COLOR=#0000ff]Step[/COLOR] 1
     Cells(x, 2).Value = Right(Cells(x, 2).Value, Len(Cells(x, 2).Value) - InStr(Cells(x, 2).Value, "-"))
 [COLOR=#0000ff]  Next [/COLOR]x

The problem is that it is taking an extensive period of time. I have already set ScreenUpdating = False. Is there a way to do this string replacement all at once without having to cycle through each individual cell? My data contains several thousand rows.

Any advice or help would be much appreciated. Thanks!

 

[NeonRedSharpie]

Cells(1, 2).EntireColumn.Offset(0, 1).InsertWith Range(Cells(2, 3), Cells(LastRow, 3))
    .FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1]))"
    .Value = .Value
End With
Cells(1, 2).EntireColumn.Delete

Try that.

 

[TerryHogarth21]

You could always do text to column and put the criteria of the split after the hyphen if that is 100% accurate.

 

[mrmmickle1]

NeonRedSharpie,

Thanks for the advice. Using the formula works well.

TerryHogarth21,

This works as well!

I appreciate the help. It's funny how simple things can be sometimes...


This is what I ended up going with:

   Range("C2:C" & LastRow).FormulaR1C1 = "=NUMBERVALUE(RIGHT(RC[-1],LEN(RC[-1])-FIND(""-"",RC[-1])))"
   Range(Cells(2, 3), Cells(Lastrow, 3)).Copy
   Range(Cells(2, 3), Cells(Lastrow, 3)).PasteSpecial Paste:=xlPasteValues
   Columns(2).EntireColumn.Delete