copy & replace questions
 

Hi - closely-related find & replace questions for Excel (or Word if
necessary??):

1. Can I remove white space from the beginning of (a range of) cells?
2. Is there a "replace" wildcard that matches "the first captured
regular expression," as in Perl? That is, can Excel find
<whitespace<number and replace it with <number - that is, replacing
<number with *itself*, rather than with anything else?

Thanks *very* much -

copy & replace questions
 

Not sure if this applies, but try using the Trim formula.

So if this was in A1

Test Trim

Would look like

Test Trim

If you used =Trim(A1)

"Guest3731" wrote:

Hi - closely-related find & replace questions for Excel (or Word if
necessary??):

1. Can I remove white space from the beginning of (a range of) cells?
2. Is there a "replace" wildcard that matches "the first captured
regular expression," as in Perl? That is, can Excel find
<whitespace<number and replace it with <number - that is, replacing
<number with *itself*, rather than with anything else?

Thanks *very* much -

copy & replace questions
 

TRIM function strips leading and trailing and extra spaces from strings.

See other reply for formula method cell by cell.

To TRIM a range in place you can use a macro.

Sub TRIM_EXTRA_SPACES()
Dim Cell As Range
For Each Cell In Selection
If (Not IsEmpty(Cell)) And _
Not IsNumeric(Cell.Value) And _
InStr(Cell.Formula, "=") = 0 _
Then Cell.Value = Application.Trim(Cell.Value)
Next
End Sub

To remove all whitespace you can use editreplace or a macro.

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


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 11:39:40 -0700 (PDT), Guest3731
wrote:

Hi - closely-related find & replace questions for Excel (or Word if
necessary??):

1. Can I remove white space from the beginning of (a range of) cells?
2. Is there a "replace" wildcard that matches "the first captured
regular expression," as in Perl? That is, can Excel find
<whitespace<number and replace it with <number - that is, replacing
<number with *itself*, rather than with anything else?

Thanks *very* much -

copy & replace questions
 

Gord Dibben wrote:
[snip macro solutions]


Thanks much for both responses -