PDA

View Full Version : Removing Spaces



lickrob
02-21-2013, 04:25 AM
I am trying to remove the spaces from numbers simmilar to this: 00 845 550 19 0007 0479 144

however when i run the below code on the cell i get this output.
8455501900070470000


Any Ideas



Dim strString As String
On Error GoTo Err

For Each cell In Selection.Cells
strString = cell.Value
strString = Replace(strString, " ", "")
cell.Value = strString
Selection.NumberFormat = "0"
Next


I have also tried:



MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")

lickrob
02-21-2013, 05:10 AM
Fixed It, Excel doesnt like long numbers apparanlty. seems daft to me. changed cell to text.

cell.NumberFormat = "@"

Paul_Hossler
02-21-2013, 07:02 PM
If you prefix it with a single quote, Excel will also keep it as text



Dim strString As String
On Error Goto Err

For Each cell In Selection.Cells
strString = cell.Value
strString = Replace(strString, " ", "")
cell.Value = "'" & strString ' Single quote between 2 double quotes
Next


Paul