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

 

[Finalfight40]

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

Are there always 2 _'s?

If so try:

=RIGHT(RIGHT(A3, LEN(A3)-SEARCH("_", A3)), LEN(RIGHT(A3, LEN(A3)-SEARCH("_", A3)))-SEARCH("_", RIGHT(A3, LEN(A3)-SEARCH("_", A3))))

Replace A3 with whatever your cell reference is.

 

[STEEL010]

Hi Finalfight40,

sometimes three.

But your formula works..... great.
how can I wright this into VBA, and auto replace exciting text with prefix/suffix.

 

[Finalfight40]

Hi Finalfight40,

sometimes three.

But your formula works..... great.
how can I wright this into VBA, and auto replace exciting text with prefix/suffix.

I couldn't put it into VBA myself, someone else might be able to help with that.

Best i could do is maybe adjust the one i gave you to include 3 instances of the underscores if that helps at all?

 

[Fluff]

Based on your sole example how about

Sub RemovePrefix()
Dim Cl As Range
For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
   Cl.Value = Split(Cl.Value, "_")(UBound(Split(Cl.Value, "_")))
Next Cl
End Sub

 

[STEEL010]

Hi Fluff,

I tried you VBA, but its does not work

this is my setup in excel see below:
[TABLE="width: 641"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Material Description[/TD]
[TD]Storage Bin[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5665[/TD]
[TD]RC_Gskt_GASKET SET[/TD]
[TD]2C111[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9966[/TD]
[TD]RC_O-Ring_O-RING[/TD]
[TD]2C162[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8888[/TD]
[TD]RC_Ring_RING, PISTON SEAL[/TD]
[TD]2C134[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9966[/TD]
[TD]RC_Ring_RING, WIPER SET[/TD]
[TD]2C163[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]


What I want is that it changes all text when I do a run in collum B staring from line 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[Fluff]

If you want it to look at col B try

Sub RemovePrefix()
Dim Cl As Range
For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
   Cl.Value = Split(Cl.Value, "_")(UBound(Split(Cl.Value, "_")))
Next Cl
End Sub

If this doesn't give the results you want, can you show us both before & after results.

 

[Rick Rothstein]

Your thread title says prefix/suffix, but all you seem to be asking for is the removal of a prefix. Given that, does this do what you want..

Sub RemovePrefix()
  Columns("B").Replace "*_", "", xlPart, , , , False, False
End Sub

 

[STEEL010]

Hi there,

sorry for answering late, but Rick's methods works.

And sorry I have no suffix's.

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.

greetings Steel010

 

[STEEL010]

Hi There,

Rick and Fluff (or someone else with great skills )


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

Greetings Steel010