VBA remove specific characters in cell

[MattehWoo]

Hi all,

I have this data

Reg 01 46107 20JUN2016

Reg 01 45307 20MAY2016

Reg 01 44493 20APR2016

Reg 01 43621 20MAR2016

Reg 01 42786 20FEB2016

Now at first each line is in 1 cell. eventually in my VBA code i get it to text to columns it.

Now, i need to remove Reg at the start. The reason i'm finding this hard is because sometimes the data starts with just an R and sometimes nothing at all - just the number. Most of the time there is no space between the R and the numbers which is why i've only just discovered this problem...

Any ideas?

Cheers

[gmaxey]

I'm just beginning to dabble with Excel so I'm not sure if I understand your question or about the efficiency of this proposed solution.

If you want to trim "Reg" from the start of each cell where it occurs then perhaps his will do:

Public Sub TrimRegFromCell()
Dim cell As Range
  For Each cell In ActiveSheet.UsedRange
    If Left(cell.Value, 3) = "Reg" Then
      cell.Value = Mid(cell.Value, 4, Len(cell.Value) - 3)
    End If
  Next
End Sub

[Paul_Hossler]

As a UDF

Function JustData(s As String) As String
    If Left(s, 4) = "Reg " Then
        JustData = Right(s, Len(s) - 4)
    ElseIf Left(s, 3) = "Reg" Then
        JustData = Right(s, Len(s) - 3)
    ElseIf Left(s, 2) Like "R[0-9]" Then
        JustData = Right(s, Len(s) - 1)
    Else
        JustData = s
    End If
End Function

Could in turned into a sub and might be a little faster

[snb]

Sub M_snb()
     sheet1.cells.replace "Reg ",""
End Sub

[MattehWoo]

snb, i think for now this will do. I'm sure i'll come across so much more data formats that'll throw things back up again haha!

[Paul_Hossler]

sometimes the data starts with just an R and sometimes nothing at all - just the number. Most of the time there is no space between the R and the numbers

I think that it only gets the easy ones

[MattehWoo]

I think you're right, i'll stick your code in later