Remove extra "spaces" at the beginning of the cell Solved

Question

Hello,  I created a table with 82,000 rows containing article references. However, during copy/pasting, many cells have one or more extra spaces at the beginning of the cell.  How can I remove all the excess spaces without altering the cell's content?  Thank you for your help.   Configuration: Windows 7 / Firefox 31.0

Vaucluse · Accepted Answer

Good evening  with a formula;  =TRIM(A1)  which removes all spaces at the beginning and end of the text without altering the spaces in the text  and then a special copy/paste: values to remove the formulas  best regards  -- To err is human, to persist is diabolical

Gyrus · Answer

Hello,  Can you write a macro that will use the LTrim  function to remove leading spaces?  See you!

ccm81 · Answer

Hello  An example  https://www.cjoint.com/?3IksnB7Wixb  Best regards

Somik · Answer

Hello,  For ccm81, I'm not very familiar with macros:  Ctrl+n to clear columns ABCAlt-F11 to access the codein Module 1adjust the constants' values to the sheet you want to process How do I adapt the macro using this function to my table? The worksheet is called "Complete Battery List" and the only column to be processed is column N. I really don't know how to do it.  For Gyrus, how is the LTrim function used?  Thank you for your help.

ccm81 · Answer

at home Alt-F11 then Insert/Module copy the code To modify the constants codeb = cofin = 14 (column N)  Const NF = "Complete Battery List"Const codeb = 14Const cofin = 14Const lideb = 2  Return to the sheet Tools/macro/select Clean Execute  P.S. The Vaucluse solution, which I cordially acknowledge, may be less stressful for you  Best regards

ccm81 · Answer

@Vaucluse. There is some truth in what you say  With the macro for 30,000 rows and 3 columns, it takes 6.5 seconds on my old machine that is starting its second term  Good evening

Somik · Answer

Hello,  I tried the Vaucluse solution by creating a column M and integrating  =TRIM(N2)  and then dragging it down, but nothing happens.  When dragging down, it keeps the same cell for everything and especially it does not display the text from the cell in column N.  Any ideas?

Somik · Answer

@ccm81  I did it!!!! But it was tough. Even though I copied the code and modified the first part to adapt it, the result was the same, that is to say "Error 9 ... blah blah blah".  I restarted it by going through the detailed steps, and from there it worked.  I don't understand why, but the result is good ^^  Thank you very much for your help.  Best regards

Remove extra "spaces" at the beginning of the cell

8 réponses

Access issue to my "contact me" profile

Windows spotlight is not working.

Samsung j5 unable to answer

My nikon coolpix s3000 won't turn on anymore.

Bug with the "backspace" key

Do you know maillotsofficiels.fr?

Wii balance board issues

Focusing issue on canon eos 450d

Why do i need to press f1 to start

Wi-fi connection via livebox