Find & Replace part of cell contents

[Guest]

in Excel 2003 I've an input worksheet with text in some cells
some users formated their text using multiple spaces causing text length
problems

from another spreadsheet which is proofing the input I can locate the
problem cells

SrceFld ="D45"

Len(SrceBook.Sheets("InputSheet").Range(SrceFld).Value)

I want to replace all cell contents occurances of " " with " " (double
space with single space)

I've tried
didn't change anything>> Replace
SrceBook.Sheets("InputSheet").Range(SrceFld).Value, " ", " ", 1, -1, 1

didn't change anything>>
SrceBook.Sheets("("InputSheet").Range(SrceFld).Replace What:=" ",
replacement:=" ", Lookat:=xlPart

Fails>> SrceBook.Sheets("Execute Dealer Plan").Range(SrceFld).Value.Replace
What:=" ", replacement:=" ", Lookat:=xlPart

 

[JW]

You can use the Trim function to replace leading, trailing, and
excessive spaces:
=Trim(A1)

Or
Range("A1")=WorksheetFunction.Trim(Range("A1"))

 

[Guest]

As I recall Trim only does leading and trailing spaces

users used spaces to essentially force wordwrap within the text window so I
have cell contents like...

"No National rally planned in our area like in 2006


Since the industry has taken a downturn, I
am expecting lower sales this year.


Perhaps production flexibility in the future with lower
cost alternate materials with compensate for projected rising costs in 2008."

 

[Dave Peterson]

VBA's Trim() only touches the leading and trailing spaces.

But JW used:
WorksheetFunction.Trim(Range("A1"))

And Excel's =trim() gets leading/trailing and duplicate embedded spaces.