Space in Cell

[hemrajrav328]

Is there a formula/macro to remove spaces in cells ?

See attached example.

[HansV]

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:

Code: Select all

Sub RemoveSpaces()
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart
    Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub

[DocAElstein]

Hello
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

( 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

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,

Window Select.JPG

, 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

Runed it on Selection.JPG

_.__________________________________-
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

[snb]

Code: Select all

Sub M_snb()
   [A1:A9] = [index(substitute(trim(A1:A9),char(160),""),)]
End Sub