Find And Delete

[Maxal]

Hi

Need a macro for deleting some unwanted strings.

In each cell of Column A, every client name is trailed by different tags and then some unwanted space. For instance:

Blazer/d...
Rockwell-A.......
Sigma Mills-DJ....
Ikom-L..
Sagmone Shoes/f............ (dots are spaces)

I want all proper names to be cleaned of everything trailing, including extra spaces so that I get only client names:

Blazer
Rockwell
Sigma Mills
Ikom
Sagmone Shoes

That's pretty much it. Hope to have some good help

[MarvinP]

Hi Maxal,

If Blazer/d.. is in cell A1 then this formula will do what you need in B1

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),"-",REPT(" ",LEN(A1))),LEN(A1)))


The above formula will stick a bunch of spaces for the "/" and "-" characters. Then it trims the left part of that to give what you want.

[Maxal]

Hey MarvinP, thanx...but I am looking for a macro to do the job. I have to sort lots of data on daily basis.

The content to be cleaned is in Column A and downwards, A1, A2, A3 etc.

[Vikas_Gautam]

Looking for VBA code, Try this:-

Please Login or Register  to view this content.

Edit:- Correcting the compilers error.

[HaHoBe]

Hi, Vikas,

code will only replace the characters but not trim the rest of the cells from the character on as wanted by OP.

Ciao,
Holger

[Vikas_Gautam]

Yeah.. Holger I know.
Will you suggested looping through then...?

[Maxal]

Yeah.. Holger I know.
Will you suggested looping through then...?

Yes please, could anyone suggest a complete solution?

[MarvinP]

Ok Maxal,

Here is the VBA and example to do what you want. You can add to the list of last characters in the code. That is if you wanted ~ and : to also be "end of line" characters, you would change:
LastChr = Split("/ -", " ") to
LastChr = Split("/ - ~ :"," ")

Please Login or Register  to view this content.

[Maxal]

Hey MarvinP, it works like a charm, except that there is a catch.

I have some names that are divided by hyphens. Precisely speaking, mostly divided by "Al-" for instance, Al-Nikara, Al-Shiraza, Al-Yahso, etc. These "Al-" are always at the beginning of a word.

Is there a way to add an exception to the code so that "Al-" is spared by the macro?

[Vikas_Gautam]

Here is the workaround for Al-

Please Login or Register  to view this content.

[MarvinP]

Hi Maxal,

To take care of your Al-Shiraza type names here is a revised code. Try this.

This will check to see if the "-" happens in the first 3 letters. If it does then it will look for the "-" from the back of the string.

Please Login or Register  to view this content.

[Maxal]

Thanx @MarvinP and @Vikas_Gautam. Both of you have been immensely helpful