copy & replace questions

[Guest3731]

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 -

 

[akphidelt]

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)

 

[Gord Dibben]

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 edit>replace or a macro.

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


Gord Dibben MS Excel MVP

 

[OP]

Gord Dibben wrote:
[snip macro solutions]


Thanks much for both responses -