how to delete specific signs or letter in the beginning of a column?

**wali** · 11-29-2007, 03:35 PM

Hello,

I have many excel wordlist, which are being converted from other file formats. Now there are sometimes useless spaces before entries in the cells. There are also some times commas and other not needed signs at the beginning of a cell in a column.

I need a macro, which removes spaces, symbols, letters, numbers or combination of them from the beginning of all cells in a column. The symbols should be chosen before the macro starts its work.

For example:

Before running the first macro:

Column A
,Word1
,,Word2
Word3
,Word4

After running the macro the beginning should be cleaned from chosen symbols, spaces or signes. (in our example if a cell begins with , then the commas should be deleted).

Column A
Word1
Word2
Word3
Word4

Thanks for your answers in advance

**StephenR** · 11-29-2007, 03:42 PM

Is it anything before the word "Word" which has to go?

**wali** · 11-29-2007, 03:51 PM

Hi,
thanks for the quick response. Yes in this case the commas before "word" must go. But sometimes there are other signs or spaces as well. Therefore i must be able to chose which signs at the beginning of the cell i want to delet.

**StephenR** · 11-29-2007, 03:53 PM

So if you had "FFWord1" you just want "Word1"?

**wali** · 11-29-2007, 03:57 PM

yes! In that case i would chose: in all cells "FF" in column A should be deleted, if the cells start with "FF".

**StephenR** · 11-29-2007, 04:24 PM

You could use this formula and then paste values over the original if you don't want to keep.

=RIGHT(A1,LEN(A1)-FIND("Word",A1)+1)

**wali** · 11-29-2007, 04:38 PM

It shows me an error, when i put the formula in column B. I dont know what i am doing wrong. Well here is the example of a list, i would like to clean the beginning from spaces:

http://www.qamosoona.com/download/ma...nn-cleaner.xls

The wordlists i have to clean have different entries and not always "word". I just wrote that as example.

**wali** · 11-29-2007, 04:41 PM

It shows me an error, when i put the formula

=RIGHT(A1,LEN(A1)-FIND("Word",A1)+1)

in column B. I dont know what i am doing wrong. Well here is the example of a list, i would like to clean the beginning from spaces:

http://www.qamosoona.com/download/ma...nn-cleaner.xls

The wordlists i have to clean have different entries and not always "word". I just wrote that as example.

**StephenR** · 11-29-2007, 04:57 PM

How on earth was I supposed to know that, I'm not psychic?!

You're going to have to be much more specific about what needs removing and what you want to keep because nobody here knows anything at all about what you're doing.

**wali** · 11-29-2007, 05:15 PM

sorry for the inconvenience!!

I give it another try:

if i have list of entries in column A, i would like to delete the "spaces" from each cell in column A, if the cell begins with "space". Only the "space" at the beginning should be deleted but not the "space" in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "space" should get away.

or

if i have list of entries in column A, i would like to delete the "," from each cell in column A, if the cell begins with ",". Only the "," at the beginning should be deleted but not the "," in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "," should get away.

or

if i have list of entries in column A, i would like to delete the "ff" from each cell in column A, if the cell begins with "ff". Only the "ff" at the beginning should be deleted but not the "ff" in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "ff" should get away.

Now each time i can give the requirment before running the macro, if i want the "space"; ","; "ff" or even other letter or symbol to be deleted from the beginning of the cells.

**StephenR** · 11-29-2007, 05:25 PM

OK, for the 3 specific cases you mention, try this:

=IF(LEFT(A1,2)="FF",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1)=",",RIGHT(A1,LEN(A1)-1),TRIM(A1)))

**wali** · 11-29-2007, 05:43 PM

sorry, but still not working. I must be doing something wrong.

i pasted the function in column B infront of a word which is supposed to be cleaned from comma at the begining of it, and it shows me an error.

I am uploading an excel file containing three worksheets. The first one should be cleaned from the "space" at the beginn of the cell, only if the cell begins with "space". The second worksheet from comma and the third from ff. Can you please type the function in column b infront of it and upload the file again. thanks

**wali** · 12-12-2007, 06:22 AM

Please help! my problem not solved yet.

Originally Posted by wali

if i have list of entries in column A, i would like to delete the "spaces" from each cell in column A, if the cell begins with "space". Only the "space" at the beginning should be deleted but not the "space" in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "space" should get away.

or

if i have list of entries in column A, i would like to delete the "," from each cell in column A, if the cell begins with ",". Only the "," at the beginning should be deleted but not the "," in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "," should get away.

or

if i have list of entries in column A, i would like to delete the "ff" from each cell in column A, if the cell begins with "ff". Only the "ff" at the beginning should be deleted but not the "ff" in the middle of entry or at the end and the entry in cell itself should remain but only the beginning "ff" should get away.

Now each time i can give the requirment before running the macro, if i want the "space"; ","; "ff" or even other letter or symbol to be deleted from the beginning of the cells.

When i give the following function as kindly proposed by stephen, i get an error message.

=IF(LEFT(A1,2)="FF",RIGHT(A1,LEN(A1)-2),IF(LEFT(A1)=",",RIGHT(A1,LEN(A1)-1),TRIM(A1)))

The second thing is that i would like to have three different functions for all three different cases i have mentioned and not one function for all.

Can any one please download the file i have uploaded in this thread and put the function in it. I will be very greatful if the solved file with functioning function in it is then uploaded here again.