Remove spaces in Excel

Solved

T10 -

Hi,

I have a list of 3 columns
Column A = Name
Column B = Address
Column C = Locality

In the first row, all the names start with a space in each cell.
Is there a formula to automatically remove this space
because there are still 3500 lines and if I have to do them one by one...
Thank you

8 answers

Answer 1 / 8 - Best answer
The best answer is the one that has been validated by our teams.
Niels
It's even simpler!
You select all the cells where there is this issue, then Ctrl + H

"Replace" and in the first line "Find what" you press the spacebar and in the 2nd "Replace with" you put absolutely nothing...

"Replace All" and voila, no more spaces... =)

40
1. Lol
  
  This solution works well, but it removes the spaces between words.
  
  2
2. chalex92
  
  Thank you very much Niels, it’s simple and quick ;)
  
  1
3. lulu
  
  That's excellent, it works in 2 seconds for 300 references
  
  you're a champ
  thank you for your help
  
  0
4. nanoush13
  
  thank you thank you thank you thank you thank you!!!! have I already said thank you??
  
  0
5. Abuhucem
  
  Thank you Niels, but when you have a list of 1500 names, that will be a problem. The best thing is to process the text with what Excel offers as a formula. SUPPRESPACE(A1) or TRIM(A1) for the English version.
  
  0
Answer 2 / 8
UsulArrakis Contributor
Hello
use the function (text) TRIM

Usul of Arrakis /
Serial_dreamer

-3
1. T10
  
  So I click on Fx
  
  Then in the functions on text.
  Next, what do I write in value?
  And in format_text, what do I write?
  
  Thank you
  
  0
Answer 3 / 8
UsulArrakis Contributor
In the function / text / look for the TRIM function.
In the window that opens, you have a field with a small grid (containing a red arrow) on the right side. Click on it, then select the cell and click on the grid again.
Then OK.

Usul of Arrakis /
Serial_dreamer

32
1. T10
  
  Sorry but it doesn't work.
  
  It's giving me a circular error.
  
  0
2. whiteriot
  
  @T10You need to place your formula in a new cell. Insert a column right after the one you are working on. Place your formula as indicated above. Do a special paste values on your result column and you can remove the old column where there were spaces.
  
  0
3. T10
  
  @whiteriotHow do we do a special paste for column values?
  
  0
4. T10
  
  @whiteriotit doesn't work.
  
  0
5. UsulArrakis Contributor
  
  @T10you select your range of cells
  copy (ctrl + C) then you select the cell where you want to paste
  then menu Edit / Paste special / check "values" / OK
  
  Usul of Arrakis /
  Serial_dreamer
  
  0
Answer 4 / 8
UsulArrakis Contributor
ok
use the function (text) REPLACE
1st field: the cell containing the space
2nd field: 1
3rd field: 1
4th field: "" quotes without space in between

Usul from Arrakis /
Serial_dreamer

1
Answer 5 / 8
T10
It still doesn't work

Still the same problem, circular error.

1
1. UsulArrakis Contributor
  
  In which cell do you put the "REPLACE" function?
  In A2: the NAME
  In B2: the ADDRESS
  In C2: the LOCALITY (here 6460 Chimay)
  In D2: =REPLACE(C2,1,1,"")
  I'm doing very well on your file!!!
  
  Usul of Arrakis /
  Serial_dreamer
  
  0
2. T10
  
  @UsulArrakisWhat an idiot I am.....
  
  A huge thank you
  
  What would I do without you!!
  
  0
3. UsulArrakis Contributor
  
  @T10;-)
  
  Usul of Arrakis /
  Serial_dreamer
  
  0
Answer 6 / 8
laruche
Hello,

If you want, there's always VBA!

You can find the example here: http://bout2code.com/non-classe/2008/08/supprimer-les-espaces-en-trop-sur-excel/

0
1. UsulArrakis Contributor
  
  so we're having fun digging up old messages 8-)))))
  --
  
  ( _Usul from Arrakis
  Serial_Dreamer_)
  
  0
Answer 7 / 8
MiiKo
I have the same problem with a series of dates in the format dd/mm/yyyy. I have a space before and after that prevents me from using formulas... Can you help me??

1
1. UsulArrakis Contributor
  
  Have you tried the TRIM function?
  If the date is in A1, put in B1 =NUM(CLEAN(A1)) and set the B1 cell to date format
  --
  
  (_Usul of Arrakis
  Serial_Dreamer_)
  
  0
2. EmmanuelB
  
  Hello,
  
  I have the same issue with a percentage where there is a space between the last digit after the decimal point and the % sign.
  
  I don't feel like your methods are working in this case. I will continue, but if you have other ideas...
  
  Thank you
  
  0
3. EmmanuelB
  
  finally managed to solve it by combining the IF, CNUM, and REPLACE functions... :-)
  
  0
Answer 8 / 8
Kurt
You can also copy your column into a text editor, like Ultraedit, then simply align the selection to the left (SHIFT + TAB), and paste the result back into your Excel sheet :-)

1

Remove spaces in Excel

8 answers

Laptop won't start

Very slow laptop

Open and rename .mdi files

I need a bit more detail to help with javascript. what are you trying to do or what's the issue you're facing?

Mailbox

Printer connection

Difficulties changing password on yahoo mail

No sound on my laptop

Remove leading zeros in printouts

Linkedin profile visits in private mode