Delete everything before certain text

[Tasciovanus]

Hi All

Thanks for having a look at my problem, its 1 in the morning and I'm a little brain fried, perhaps you may be able to give me some guidance as I'm fairly new to programming macros in Excel.

Basically I have a range of data typically B11 to N50 which may contain text which will need manipulation based on the contents.

The only text I need to manipulate will be between 7 and 11 characters long and always have "(SWP)" in the middle.
examples would look like XXX(SWP)YYY otherwise I want to leave things as they are.

I want to do is to delete everything before the Y YY or YYY (ie after the (SWP) characters).

Does that make any sense.

Looking forward to any help you may be able to give me.
Thanks
Andy

[martindwilson]

why not just find/replace?
find (swp)*
replace with (swp)
buy im not sure what you want to keep
I

want to do is to delete everything before the Y YY or YYY (ie after the (SWP) characters).

do you want to keep
xxx(swp)
xxx
yyyy
or
(swp)yyyyy
in any of those case you can use the* wild card
xxx(swp) find (swp)* replace (swp)
xxx find (swp)* replace with nothing
yyyy find *(swp) replace with nothing
or
(swp)yyyyy find *(swp) replace (swp)

[martindwilson]

hmm forum playing up again must be backup time

[Tasciovanus]

ok

So XXX and YYY can be random 1, 2 or 3 character text seperated by the text (SWP) within a certain range of cells;
other cells within the same range can contain other data which I dont want to manipulate.

I don't quite get how I can find and replace when I don't know what the cell will contain other than within the cell there will be the (SWP) text.

Thanks
Andy

[martindwilson]

see my post #2 use the * wild card as shown (i was updating it as you posted)

[Tasciovanus]

Sorry looks like the forum backup was causing me a few viewing issues.
Thanks
Andy

[Moo the Dog]

Agree with Martin on this one, Find/Replace is easiest method...

If you ever wanted to create a macro that would do the same thing, just turn on the macro recorder and take the steps you wold need to perform the replacement manually, then stop the recorder and check out the VBA to see how it is set up.

Below, I did just that:

Please Login or Register  to view this content.

Or, you could loop through each cell in the range and do the same...

Please Login or Register  to view this content.

Lots of ways to do these things... but I would still go with the old Find/Replace as Martin suggested.

- Moo

[Tasciovanus]

Thanks both.
I was trying to over engineer the process.
All works fine.
Cheers
Andy