PDA

View Full Version : [SOLVED] VBA remove specific characters in cell



MattehWoo
08-10-2016, 07:40 AM
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
08-10-2016, 07:52 AM
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
08-10-2016, 08:39 AM
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
08-10-2016, 09:41 AM
Sub M_snb()
sheet1.cells.replace "Reg ",""
End Sub

MattehWoo
08-11-2016, 03:44 AM
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
08-11-2016, 09:23 AM
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
08-12-2016, 02:12 AM
I think you're right, i'll stick your code in later