Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > White space removal
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default White space removal
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default White space removal
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



On Wed, 14 Oct 2009 18:21:06 +0100, "camlad" wrote:

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default White space removal

camlad;525281 Wrote:
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

See if this works for you:

Sub blah()
For Each cll In Range("A1:A535").Cells
cll.Value = Application.WorksheetFunction.Trim(cll.Value)
Next cll
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144093

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default White space removal
Trim(cll.value) will not remove "all spaces"...........only extra spaces.


Gord

On Wed, 14 Oct 2009 18:48:29 +0100, p45cal
wrote:


camlad;525281 Wrote:
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

See if this works for you:

Sub blah()
For Each cll In Range("A1:A535").Cells
cll.Value = Application.WorksheetFunction.Trim(cll.Value)
Next cll
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default White space removal
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

"camlad" wrote in message
...
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




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default White space removal
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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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



On Wed, 14 Oct 2009 18:21:06 +0100, "camlad"
wrote:

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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default White space removal
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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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



On Wed, 14 Oct 2009 18:21:06 +0100, "camlad"
wrote:

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





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default White space removal
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


On Wed, 14 Oct 2009 22:45:33 +0100, "camlad" wrote:

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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



On Wed, 14 Oct 2009 18:21:06 +0100, "camlad"
wrote:

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




Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22,906
Default White space removal
Make that "did NOT survive the trip"


Gord

On Wed, 14 Oct 2009 15:37:00 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


On Wed, 14 Oct 2009 22:45:33 +0100, "camlad" wrote:

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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



On Wed, 14 Oct 2009 18:21:06 +0100, "camlad"
wrote:

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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default White space removal

Gord Dibben;525309 Wrote:
Trim(cll.value) will not remove "all spaces"...........only extra
spaces.

Gord

Quite right. My mistake.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144093

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Space removal - a first name,space last name Tech Express Excel Discussion (Misc queries) 4 February 9th 09 08:24 PM
Remove All White Space Carol Excel Programming 1 November 17th 08 01:02 PM
white space deletion JOMARFL Excel Worksheet Functions 2 February 2nd 07 08:43 PM
White Space in PDF from Excel Antony Excel Discussion (Misc queries) 0 May 23rd 05 08:21 PM
Inefficient Space Removal Sauron Excel Programming 5 January 2nd 04 11:03 AM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement