| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
| Find & replace different years (part of the date)in a column | Excel Worksheet Functions | |||
| find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
| Find and replace part of a text string | Excel Discussion (Misc queries) | |||
| How do I find and replace part of a hyperlink that isn't visible? | Excel Discussion (Misc queries) | |||
Linear Mode
