• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to delete space before any text in excel

Hi Parbati ,


Use the TRIM function ; suppose your input is in cell A1 , then if you enter the following formula in cell A2 :


=TRIM(A1)


then A2 will display the contents of cell A1 , without the spaces.


Of course , the above will work only if the characters before the actual text , are in fact space characters ; within the ASCII character set , there are other characters which may display as spaces , but which are not in fact spaces ! A real space is one whose ASCII character code is decimal 32. To find out whether this is so , do the following :


For the same text in cell A1 , in cell B1 , type in the following formula :


=CODE(A1)


If you see the value 32 in B1 , then your text contains real spaces.


Narayan
 
Hi Parbati ,


Did you check out whether the second cell contains real spaces or characters which look like spaces ?


Narayan
 
Hi Parbati ,


Giving more details , rather than just saying you checked it , would help !


If the character is having an ASCII code of 160 , then the following will work :


=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))


Narayan
 
You must log in or register to reply here.
Back
Top