White space removal

  • Thread starter Thread starter camlad
  • Start date Start date
C

camlad

I need to remove white space from a range so I recorded the following which
did not work.



How can I remove all spaces in a range?



Range("A1:A535").Select

Selection.Replace What:="^w", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False



Camlad
 
What does "^w" mean to you?

Try this to remove all spaces.

Public Sub Strip_WhiteSpace()
Selection.Replace what:=" ", _
replacement:="", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

If the spaces are html non-breaking spaces.

Public Sub Strip_WhiteSpace()
With Selection
.Replace what:=Chr(160), replacement:="", _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace what:=" ", _
replacement:="", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


Gord Dibben MS Excel MVP
 
Thanks Gord and p45cal

I had spotted Trim and not used it - I was trying to use the ^w which
replaces multiple spaces used in Word, inc macros..

Camlad
 
Sorry - back again. Both spaces and non-breaking spaces are removed but
there is something remaining in one cell which remains. I paste it, seven
characters on this occasion, here within parenthasis
( )
Pasting into Word and showing hidden characters reveals what look like
spaces. Are there any other forms of space?

This is a spreadsheet which is sent to me occasionally and I need to clear
everything out of the column except text which will be Jan, Feb, Mar,
.......Nov, Dec or the numneral 2009, 2010, etc. Everything else should go in
order for the rest of my programming to work properly.

How can I find what these other characters are or have I to find some way of
testing every character and deleting everything except those which appear in
Jan, Feb, Mar, ......Nov, Dec and 0-9? There must be a better way than that.

Please help.

Camlad
 
Sorry - back again. Both spaces and non-breaking spaces are removed but
there is something remaining in one cell which remains. I paste it, seven
characters on this occasion, here within parenthasis
( )
Pasting into Word and showing hidden characters reveals what look like
spaces. Are there any other forms of space?

This is a spreadsheet which is sent to me occasionally and I need to clear
everything out of the column except text which will be Jan, Feb, Mar,
.......Nov, Dec or the numneral 2009, 2010, etc. Everything else should go in
order for the rest of my programming to work properly.

How can I find what these other characters are or have I to find some way of
testing every character and deleting everything except those which appear in
Jan, Feb, Mar, ......Nov, Dec and 0-9? There must be a better way than that.

Please help.

Camlad
 
Go to Chip Pearson's site and download his cellview add-in to find
out what's in the cells.

http://www.cpearson.com/excel/CellView.aspx

An indispensable tool if bringing in data from the Internet

The copied data you just posted contains 7 char 32's which are plain old
spaces. Whatever you thought you posted did survive the trip.


Gord Dibben MS Excel MVP
 
Make that "did NOT survive the trip"


Gord

Go to Chip Pearson's site and download his cellview add-in to find
out what's in the cells.

http://www.cpearson.com/excel/CellView.aspx

An indispensable tool if bringing in data from the Internet

The copied data you just posted contains 7 char 32's which are plain old
spaces. Whatever you thought you posted did survive the trip.


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top