Remove everything before and including #

[tomleitch]

Hi Folks,

I think this is quite an easy one - I've seen similar solutions through searching but not quite exactly this.

I have a column with data in it and I want to use vba to get rid of everything before - and including #

So for instance I've got ZP02-TS-GLE#1282255 and I want to get rid of everything except 1282255 for that cell

I want to do this for the whole column.

Any help appreciated


Many Thanks
Tom

 

[Fluff]

How about

Sub tomleitch()
   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value <> "" Then Cl.Offset(, 1).Value = Split(Cl, "#")(UBound(Split(Cl, "#")))
   Next Cl
End Sub

 

[tomleitch]

How about

Sub tomleitch()
   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value <> "" Then Cl.Offset(, 1).Value = Split(Cl, "#")(UBound(Split(Cl, "#")))
   Next Cl
End Sub

Always appreciated Fluff

 

[DanteAmor]

How about

Sub test()
    With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=MID(RC[-1],SEARCH(""#"",RC[-1])+1,LEN(RC[-1]))"
        .Value = .Value
    End With
End Sub

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rick Rothstein]

I am assuming your single example is representative of your actual data. If you have header text and that text does not contain a # sign anywhere in it, or if you do not have any header text in Column A at all, then you can use this simple one-liner macro to do what you want...

Sub Test()
  [A:A].Replace "*#", "", xlPart, , , , False, False
End Sub

If you do have header text and it contains a # sign, then you can use this one-liner macro instead...

Sub Test()
  Range("A2", Cells(Rows.Count, "A").End(xlUp)).Replace "*#", "", xlPart, , , , False, False
End Sub

 

[tomleitch]

Thanks Rick, all sorted now