How to remove spaces in the begining and at the end of a cell

[arshad_masud]

Hi

I have huge data extracted from ERP. There are spaces at the start of the data and even at the end. I tried to use "Trim" & "Clean" function but failed. Can some body help me.


Here is one example of data "         310101:Share capital "


Thanks in advance

 

[NARAYANK991]

Hi Arshad ,


The problem is your data has non-space characters (code = 160) instead of spaces ; TRIM removes only spaces.


The example you have given , has 9 such characters in the beginning , and alphanumeric text thereafter , and 1 such character at the end. If you take the length of the string using =LEN(...) , the length is 30 ; however the alphanumeric characters are only 20.


If you take the actual string using =MID(...,10,20), you get the string that you want.


All of the above depends on all of the data having the same length.


You can also use the Convert Text to Columns facility , with fixed width option , to eliminate the leading and trailing non-alphanumeric characters.


Narayan

 

[arshad_masud]

Dear NARAYANK991


Thanks for your time and effort. But the problem is that all the data has not the same lenght, otherwise i would have used "left, right or mid" formula. Neither i could use Convert Text to Columns facility due to the issue mention above.


Thanks

 

[NARAYANK991]

Hi Arshad ,


In that case , your problem is easily solved.


Use the SUBSTITUTE function to replace all instances of char(160) by null characters.


=SUBSTITUTE(O11,CHAR(160),"")


Narayan

 

[arshad_masud]

Dear NARAYANK991


It worked. Really grateful

 

[Luke M]

If you don't want to use formulas, you can also write in some cells:

=CHAR(160)

Copy that cell, then do a find and replace. Paste what you just copied (will look like a space) in the find line, leave the replace line blank. Replace all, done.

 

[NARAYANK991]

Hi Arshad ,


Thanks for the feedback.


Narayan