Delete space at beginning
 

Hi All

Is it possible to delete the space at the beginning of a cell of data? I
have a list of addresses for a mail merge and some (not all) have a space at
the beginning of the data. Ideally the find and replace (rather than a
formula) would be best.

Example (in the first one the address has a space):
1 Corsica Street
22 High Street

Any help greatly appreciated

Kind regards

Rexmann

Delete space at beginning
 

Select the cells you want to adjust and run this small macro:

Sub spaceSaver()
For Each r In Selection
If IsEmpty(r) Then
Else
If Left(r.Value, 1) = " " Then
r.Value = Right(r.Value, Len(r.Value) - 1)
End If
End If
Next
End Sub

--
Gary''s Student - gsnu200787


"rexmann" wrote:

Hi All

Is it possible to delete the space at the beginning of a cell of data? I
have a list of addresses for a mail merge and some (not all) have a space at
the beginning of the data. Ideally the find and replace (rather than a
formula) would be best.

Example (in the first one the address has a space):
1 Corsica Street
22 High Street

Any help greatly appreciated

Kind regards

Rexmann

Delete space at beginning
 

Dim LastRow As Long
Dim i As Long

With ActiveSheet

.Cells(.Rows.Count,"A").End(xlUp).Row
For i = 1To LastRow

.Cells(i,"A").Value = Trim(.Cells(i,"A").Value)
Next i

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"rexmann" wrote in message
...
Hi All

Is it possible to delete the space at the beginning of a cell of data? I
have a list of addresses for a mail merge and some (not all) have a space
at
the beginning of the data. Ideally the find and replace (rather than a
formula) would be best.

Example (in the first one the address has a space):
1 Corsica Street
22 High Street

Any help greatly appreciated

Kind regards

Rexmann

Delete space at beginning
 

Brilliant, thank you, that worked great.

I have tried to mess around with it and get it to do something else. Is it
easy to modify to make it delete spaces at the end?

Any ideas gratefully received

Rexmann



"Gary''s Student" wrote:

Select the cells you want to adjust and run this small macro:

Sub spaceSaver()
For Each r In Selection
If IsEmpty(r) Then
Else
If Left(r.Value, 1) = " " Then
r.Value = Right(r.Value, Len(r.Value) - 1)
End If
End If
Next
End Sub

--
Gary''s Student - gsnu200787


"rexmann" wrote:

Hi All

Is it possible to delete the space at the beginning of a cell of data? I
have a list of addresses for a mail merge and some (not all) have a space at
the beginning of the data. Ideally the find and replace (rather than a
formula) would be best.

Example (in the first one the address has a space):
1 Corsica Street
22 High Street

Any help greatly appreciated

Kind regards

Rexmann

Delete space at beginning
 

See my offering.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"rexmann" wrote in message
...
Brilliant, thank you, that worked great.

I have tried to mess around with it and get it to do something else. Is it
easy to modify to make it delete spaces at the end?

Any ideas gratefully received

Rexmann



"Gary''s Student" wrote:

Select the cells you want to adjust and run this small macro:

Sub spaceSaver()
For Each r In Selection
If IsEmpty(r) Then
Else
If Left(r.Value, 1) = " " Then
r.Value = Right(r.Value, Len(r.Value) - 1)
End If
End If
Next
End Sub

--
Gary''s Student - gsnu200787


"rexmann" wrote:

Hi All

Is it possible to delete the space at the beginning of a cell of data?
I
have a list of addresses for a mail merge and some (not all) have a
space at
the beginning of the data. Ideally the find and replace (rather than a
formula) would be best.

Example (in the first one the address has a space):
1 Corsica Street
22 High Street

Any help greatly appreciated

Kind regards

Rexmann