Is there a formula/macro to remove spaces in cells ?
See attached example.
Space in Cell
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Space in Cell
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Space in Cell
Option 1:
Select the relevant cells.
Press Ctrl+H to activate the Replace dialog
Enter a space in the 'Find what' box.
Leave the 'Replace with' box empty.
Click Replace All.
Click in the 'Find what' box again.
Remove the space, then press Alt+0160 (this is a non-breaking space).
Click 'Replace All' again.
Close the Replace dialog.
Option 2:
Select B2.
Enter the formula
=--SUBSTITUTE(SUBSTITUTE(A2, " ", ""), CHAR(160), "")
Fill down.
Apply the desired number format to the cells in B2 and down.
Option 3:
Select the relevant cells.
Run the following macro:
Select the relevant cells.
Press Ctrl+H to activate the Replace dialog
Enter a space in the 'Find what' box.
Leave the 'Replace with' box empty.
Click Replace All.
Click in the 'Find what' box again.
Remove the space, then press Alt+0160 (this is a non-breaking space).
Click 'Replace All' again.
Close the Replace dialog.
Option 2:
Select B2.
Enter the formula
=--SUBSTITUTE(SUBSTITUTE(A2, " ", ""), CHAR(160), "")
Fill down.
Apply the desired number format to the cells in B2 and down.
Option 3:
Select the relevant cells.
Run the following macro:
Code: Select all
Sub RemoveSpaces()
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart
Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 605
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Space in Cell
Hello
Some other ideas.
_1 This is just putting Hans Excel formula in with VBA
( I was not too sure about the -- , it did not seem to work for me )
_.___________________________
_2
I took a look at the sample data , ( results on the third worksheet in my uploaded file ) , and I could only see CHAR(160)s and no other space things, so this would do it as well
_._________________________________
_3
Based on those formulas we can do a Range Evaluate solution like we doned here a few times here already
_.__________________________________
_4
That last coding way, we more often do it like if you wanted to remove spaces in a window selection you made in the workbook window showing your Excel worksheet, …… In other words you make a selection on the numbers you want to get rid of all the spaces in, , then run this
_.__________________________________-
See also here, including the comments that discus some of the ways
https://excel.tips.net/T003037_Removing_Spaces.html
https://excelribbon.tips.net/T010741_Removing_Spaces
_.____
Alan
Some other ideas.
_1 This is just putting Hans Excel formula in with VBA
Code: Select all
' http://www.eileenslounge.com/viewtopic.php?p=313874#p313874
Sub HansFormula() ' ( https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly )
Let Range("B2:B9") = "=SUBSTITUTE(SUBSTITUTE(A2, "" "", """"), CHAR(160), """")"
End Sub
_.___________________________
_2
I took a look at the sample data , ( results on the third worksheet in my uploaded file ) , and I could only see CHAR(160)s and no other space things, so this would do it as well
Code: Select all
Sub HansFormula2()
Let Range("B2:B9") = "=SUBSTITUTE(A2, CHAR(160), """")"
End Sub
_3
Based on those formulas we can do a Range Evaluate solution like we doned here a few times here already
Code: Select all
Sub PrettyWay()
Let Range("B2:B9") = Evaluate("=IF({1},SUBSTITUTE(A2:A9, CHAR(160), """"))")
End Sub
Sub PrettyWay2()
Let Range("B2:B9") = Evaluate("=IF({1},SUBSTITUTE(SUBSTITUTE(A2:A9, "" "", """"), CHAR(160), """"))")
End Sub
'https://eileenslounge.com/viewtopic.php?f=27&t=39185
'http://www.eileenslounge.com/viewtopic.php?p=295893#p295893
'http://www.eileenslounge.com/viewtopic.php?f=27&t=38500&p=297591#p297591
'http://www.eileenslounge.com/viewtopic.php?p=303473#p303473
'http://www.eileenslounge.com/viewtopic.php?p=269504#p269504
' https://eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
_4
That last coding way, we more often do it like if you wanted to remove spaces in a window selection you made in the workbook window showing your Excel worksheet, …… In other words you make a selection on the numbers you want to get rid of all the spaces in, , then run this
Code: Select all
Sub PrettyWayOnTheWorkbookWindowSelection() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=40560
Let Selection.Value = Evaluate("=IF({1},SUBSTITUTE(SUBSTITUTE(" & Selection.Address & ", "" "", """"), CHAR(160), """"))")
End Sub
_.__________________________________-
See also here, including the comments that discus some of the ways
https://excel.tips.net/T003037_Removing_Spaces.html
https://excelribbon.tips.net/T010741_Removing_Spaces
_.____
Alan
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 01 Feb 2024, 03:16, edited 2 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Space in Cell
Code: Select all
Sub M_snb()
[A1:A9] = [index(substitute(trim(A1:A9),char(160),""),)]
End Sub