How to remove spaces between characters and numbers within cells in Excel?

When you copy some data into Excel, there may be some spaces between characters or numbers, and how can you remove all spaces? This tutorial can introduce some tips for you to remove all spaces in the range of cells.

Remove all spaces between characters in Excel

Remove all spaces with the Replace function

Quickly remove extra spaces, leading spaces or trailing spaces with one click


arrow blue right bubble Remove all spaces between characters in Excel


1. In a blank cell B1 which is adjacent to the original data cell, enter the formula =Substitute(A1," ",""), and then press the Enter key. See screenshot:

doc-remove-spaces-8

2. Select B1, then drag the fill handle over the range of cells that you want to apply this formula. Then the original contents are extracted to the new column with all spaces between characters removed. See screenshot:

doc-remove-spaces-9

Tips: Please replace the original content with copying the new contents and pasting them as values.


arrow blue right bubble Remove all spaces with the Replace function

Sometimes, we want to remove all spaces between character and numbers in a range, we can use Replace function to solve it.

1. Highlight the range that you want to remove all spaces.

2. Click Home > Find & Select > Replace…, the Find and Replace dialog box will pop out.

3. In the Find what box, press the space bar once, don’t input anything in the Replace with box, then click Replace All. There will be a prompt message telling you how many spaces have been removed. See screenshot:

4. Then click OK, all of the spaces between the values are deleted. See screenshots:


arrow blue right bubble Quickly remove extra spaces, leading spaces or trailing spaces with one click

There is a flexible way to remove extra spaces, all spaces, leading spaces, trailing spaces or leading and trailing spaces in a range. If you have Kutools for Excel, you can use the Remove Spaces feature to quickly remove any types of spaces in the range with one click.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now.

If you have installed Kutools for Excel, you can remove the spaces as follows:

1. Select the range you want to remove spaces.

2. Click Kutools > Text > Remove Spaces. See screenshot:

3. Then a Remove Spaces dialog box will display, choose the Spaces Type that you want to remove, and you can preview the results from the Preview pane. See screenshot:

4. Then click OK or Apply to finish it.

Note: From the Spaces Type, we can see there are many other spaces types that we can quickly remove with this Remove Spaces tool. For more detailed information about Remove Spaces, please visit here.

 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +14 Muhibullah Afzalzada
Thanks a lot, I really needed and the Replace option was a charm.
2014-01-01 05:20 Reply Reply with quote Quote
Permalink 0 Stephen
Thanks a lot for the explanation - saved our dept a lot of work.
2016-10-20 15:23 Reply Reply with quote Quote
Permalink +12 Muhibullah Afzalzada
Thanks a lot, the Replace option worked as a charm... you really saved loads of my time.
2014-01-01 05:21 Reply Reply with quote Quote
Permalink +3 Janardhan
Very easy to understand. Good to follow.
2014-01-20 10:07 Reply Reply with quote Quote
Permalink -4 ashutosh
bull[censored]. ...doesn't work
2015-07-07 11:38 Reply Reply with quote Quote
Permalink +6 Joel
Didnt work for me too, however found the right way. Select and highlight the space you want to get rid of. Copy this space and select the find and replace. Paste this space in find and replace with nothing. I guess it wasnt a space in the first place. Whatever it was you now find it by copying whatever it was. Hope it works for you.
2015-12-08 09:41 Reply Reply with quote Quote
Permalink 0 Erika
It worked for me! Thank you!
2016-05-18 05:42 Reply Reply with quote Quote
Permalink 0 Imran
Quoting Erika:
It worked for me! Thank you!

Thank you so much for that. This saved a lot of time
2016-08-25 13:30 Reply Reply with quote Quote
Permalink 0 kati
thanks soo much. you are a genius!!!!
2016-09-15 19:31 Reply Reply with quote Quote
Permalink 0 Jay
THAT WORKED! Thanks man
2017-03-07 21:04 Reply Reply with quote Quote
Permalink 0 Stefan
Thank you, easy to understand. best on the web
2014-05-26 12:50 Reply Reply with quote Quote
Permalink -1 Rebeccah
Find/replace doesn't work for me, which is why I'm googling this topic in the first place. Is there a setting somewhere that disables this?

I want to delete ":" form the cells in a column (and "/" from the cells in another column). I ought to be able to highlight the column, ^H, type ":" (or "/") in the find field, leave the replace field blank, and click Replace All, and it should do it. Or Find Next/Replace/Re place/Replace through the cells one at a time. But it advances therough the cells but doesn't do anything. If I put something in the replace field, it will do the replace, but it won't replace with an empty string.
2014-07-24 20:36 Reply Reply with quote Quote
Permalink 0 Eva
Thanks for finally talking about >How to remove spaces between character and numbers within cells in Excel?
2014-07-27 19:19 Reply Reply with quote Quote
Permalink -2 Ranjith Kumar
Thanks Very very use full
2014-08-13 05:20 Reply Reply with quote Quote
Permalink 0 corpsman0000
how do you remove spaces without removing the zeros that in the begining of values middle and end? i just want to remove the spaces in between the values only. i tried the above and the zeros disappeared.
2014-08-21 23:46 Reply Reply with quote Quote
Permalink +1 Avi
Convert the cell into text format then use replace function
2014-10-27 13:14 Reply Reply with quote Quote
Permalink +2 KALPESH SUTHAR
Thanks a lot....its really helpful..save lot of time... :-)
2014-09-01 16:38 Reply Reply with quote Quote
Permalink -1 BISHNU
I like this tools very much
2014-09-14 09:27 Reply Reply with quote Quote
Permalink +2 ilham
wow thanks ..useful :-) :):):):):):)
2014-09-23 09:28 Reply Reply with quote Quote
Permalink 0 Prabhakar
Wow its fantastic small commands work very well
2014-09-25 09:07 Reply Reply with quote Quote
Permalink +1 GaryMonday
Thank you very much for the trick, very very useful, it makes my spreadsheet a lot neater.

Thanks again.

Gary
2014-10-20 14:20 Reply Reply with quote Quote
Permalink +2 Anna
Very helpful!
Thank you
2014-11-13 11:39 Reply Reply with quote Quote
Permalink +1 karan
very helpful :) and easy to understand

;-)
2015-02-03 04:37 Reply Reply with quote Quote
Permalink +1 kirtan
tried all the options above. not working as there are spaces before and after number. Any other alternative?
2015-03-10 14:06 Reply Reply with quote Quote
Permalink +4 Yasar Arafath
Try this one

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
2015-04-22 15:00 Reply Reply with quote Quote
Permalink +2 carla
Thank you! very helpful. saved the day.
2015-03-11 19:59 Reply Reply with quote Quote
Permalink +1 Sushen
Very use full command
2015-03-14 10:48 Reply Reply with quote Quote
Permalink +5 Yasar Arafath
Remove space before and after the cell content (Eg:___26350__)
We can use this formula =TRIM(CLEAN(SUB STITUTE(A1,CHAR (160)," ")))
2015-04-22 14:59 Reply Reply with quote Quote
Permalink 0 Manish
Thanks a lot:):):).... It saved a lot of time....keep on updating such useful and very rate instances!!!!!!
2015-09-09 11:21 Reply Reply with quote Quote
Permalink 0 M&M
Replace option is the best and easiest! Thanks for sharing the tips!
2015-04-30 16:41 Reply Reply with quote Quote
Permalink 0 Maria
I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?
2015-05-08 19:44 Reply Reply with quote Quote
Permalink 0 Imran
Quoting Maria:
I don't have kutools and I tired all formulas, eventually I had the trailing spaces removed from behind a date. However, excel still does not recognize the entry as a "date" and cannot sort by date, even though the format is date. So I have to enter each cell and F2. We are talking thousands of cells in a column... Can anyone give suggestions?

Select the column--> go to Data> Text to column> select delimited> next> next> change the date format as MDY or DMY as the case in your sheet> press fininsh.
2016-08-25 13:34 Reply Reply with quote Quote
Permalink -1 rajesh
Great command, It is very usefull.
2015-05-15 17:35 Reply Reply with quote Quote
Permalink 0 Mr.Niekoo
How i can remove dots from my columns of excel.I want to remove all dots from full list of columns.The data consist on Phone no's list

email me
2015-05-30 09:53 Reply Reply with quote Quote
Permalink 0 Henman
Neither of these suggestions work... except, I'm sure, the Kutools solution so conveniently suggested. What an awesome blog. Thanks.
2015-06-22 18:49 Reply Reply with quote Quote
Permalink 0 KD
Thank You. Your help has reduced my effort a lot.
2015-06-24 05:15 Reply Reply with quote Quote
Permalink 0 Suresh
Thanks a lot for sharing this Tool :-)
2015-08-11 16:47 Reply Reply with quote Quote
Permalink 0 JUNAID
THANKS A LOT FOR HELPING US!!!!!!!!!!!!! !
2016-01-14 07:14 Reply Reply with quote Quote
Permalink 0 JUNAID
Thanks a lot for helping us!!!!!!!!! :-)
2016-01-14 07:15 Reply Reply with quote Quote
Permalink 0 Dinesh
i am using MS 2010 ,how to remove all space in excel. exmple also give
2016-01-21 14:49 Reply Reply with quote Quote
Permalink 0 Ashok Kumar R
:-) good.. any move issue please mail me
2016-02-25 08:34 Reply Reply with quote Quote
Permalink +1 Sharil
Oh its amazing, i didn't knew about to remove the space between words in a cell....its so easy...thanku so much.
2016-03-30 06:49 Reply Reply with quote Quote
Permalink 0 RDM
THANK YOU! Really helped me and my bandwidth
2016-05-10 22:13 Reply Reply with quote Quote
Permalink 0 Kosova
Thanks a lot, now its so easy.
2016-05-11 12:05 Reply Reply with quote Quote
Permalink 0 SUDHIR MISHRA
EXCELLENT :) THANK FOR HELP
2016-08-17 10:52 Reply Reply with quote Quote
Permalink 0 sachin
Thank You Very Much. help me a lot..
2016-12-01 09:50 Reply Reply with quote Quote
Permalink 0 PRAMOD
Excellent, nice formula to substitute or remove the spaces in a cell.
2016-12-01 12:21 Reply Reply with quote Quote
Permalink 0 PRAMOD
Excellent.Very helpful to remove space in a cell
2016-12-01 12:22 Reply Reply with quote Quote
Permalink 0 Joseph Wokwera
Thanks so much helpful. i ve been trying this for 2 days. it worked
2017-02-20 15:23 Reply Reply with quote Quote
Permalink 0 Cecep Saefulloh
Great Tips especially for research keywords and to make a lot of hashtags on facebook, and You have great tools to make it simple

It is appropriate that we visit each other and communicate
Web Development Agency Konsultan Blog Teknologi
2017-04-01 16:07 Reply Reply with quote Quote

Add comment


Security code
Refresh