Remove spaces in Excel

Solved

T10 -
Al95 - 26 Mar 2020 at 14:47

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.

approved by Jean-François Pillou
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 Posted messages 3 Status Member 1
  
  Thank you very much Niels, it’s simple and quick ;)
  
  1
Answer 2 / 8
UsulArrakis Posted messages 7683 Status Contributor 3 196

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 Posted messages 17 Status Member > T10
  
  You 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 > whiteriot Posted messages 17 Status Member
  
  How do we do a special paste for column values?
  
  0
4. T10 > whiteriot Posted messages 17 Status Member
  
  it doesn't work.
  
  0
5. UsulArrakis Posted messages 7683 Status Contributor 3 196 > T10
  
  you 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 3 / 8
UsulArrakis Posted messages 7683 Status Contributor 3 196

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 4 / 8
T10

It still doesn't work

Still the same problem, circular error.
1
1. UsulArrakis Posted messages 7683 Status Contributor 3 196
  
  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
  1. T10 > UsulArrakis Posted messages 7683 Status Contributor
    
    What an idiot I am.....
    
    A huge thank you
    
    What would I do without you!!
    
    0
  2. UsulArrakis Posted messages 7683 Status Contributor 3 196 > T10
    
    ;-)
    
    Usul of Arrakis /
    Serial_dreamer
    
    0
Answer 5 / 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 Posted messages 7683 Status Contributor 3 196
  
  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 6 / 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
Answer 7 / 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 Posted messages 7683 Status Contributor 3 196
  
  so we're having fun digging up old messages 8-)))))
  --
  
  ( _Usul from Arrakis
  Serial_Dreamer_)
  
  0
Answer 8 / 8
UsulArrakis Posted messages 7683 Status Contributor 3 196

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

Remove spaces in Excel

8 answers

Pressure cooker gasket

Smart tech tv without remote control

Missing lock icon

Understanding the use of the 'i' compartment in the washing machine

Texture issues in enshrouded

Reinstall hp deskjet 3520 printer

Xiaomi redmi note 140 -

Several computer components

Blocked app icons on the screen image

Gotek information