Find & Replace part of cell contents
 

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).V alue)

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).Rep lace What:=" ",
replacement:=" ", Lookat:=xlPart

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


--
Jim

Find & Replace part of cell contents
 

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

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

JASelep wrote:
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).V alue)

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).Rep lace What:=" ",
replacement:=" ", Lookat:=xlPart

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


--
Jim

Find & Replace part of cell contents
 

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."

--
Jim


"JW" wrote:

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

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

JASelep wrote:
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).V alue)

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).Rep lace What:=" ",
replacement:=" ", Lookat:=xlPart

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


--
Jim

Find & Replace part of cell contents
 

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.

JASelep wrote:

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."

--
Jim

"JW" wrote:

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

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

JASelep wrote:
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).V alue)

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).Rep lace What:=" ",
replacement:=" ", Lookat:=xlPart

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


--
Jim



--

Dave Peterson