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:

Material	Material Description	Storage Bin
5665	RC_Gskt_GASKET SET	2C111
9966	RC_O-Ring_O-RING	2C162
8888	RC_Ring_RING, PISTON SEAL	2C134
9966	RC_Ring_RING, WIPER SET	2C163
What I want is that it changes all text when I do a run in collum B staring from line 2

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[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