Remove prefix/suffix text with excel/vba

[STEEL010]

Hi There,

Maybe someone can help.

I want to remove prefix/suffix text in text cell like below.

situation now:
PL_hftyo_BARCODE LABELS

change to:
BARCODE LABELS

it must be variable because the prefix/suffix is not fixed with the same length

Regards,
Steel010

 

[Rick Rothstein]

My previous quote was not answered, can you guys still help?

"I have only have one more question about the prefix, is it possible that it stops at the seconde "_" underscore. because I have some text with more underscore but these are no prefix's."

PL_hftyo_BARCODE_LABELS_FOR_ORDERS

See if this code works for you...

[table="width: 500"]
[tr]
	[td]Sub RemovePrefix()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF({1},LEFT(@,FIND(""_"",@&""__"",FIND(""_"",@&""_"")+1)-1))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[STEEL010]

Hi Rick,

it leaves only this part PL_hftyo_ the part that I want is after the second underscore.

 

[Rick Rothstein]

Hi Rick,

it leaves only this part PL_hftyo_ the part that I want is after the second underscore.

Sorry, try this macro then...

[table="width: 500"]
[tr]
	[td]Sub RemovePrefix()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF({1},REPLACE(@,1,FIND(""_"",@&""__"",FIND(""_"",@&""_"")+1),""""))", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[STEEL010]

YES THATS THE ONE!!!!!

Thank you for your help ray:



STEEL010