Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove space at beginning of each name Supe Excel Discussion (Misc queries) 3 February 8th 08 07:19 PM
eliminate space at beginning of line Finger Tips Excel Worksheet Functions 1 June 1st 07 12:55 AM
How do I remove a blanck space from the beginning of a formula? kan2953 Excel Worksheet Functions 6 April 6th 05 05:38 PM
Delete space Mohamed Excel Discussion (Misc queries) 3 March 8th 05 04:32 PM
Is there a way to use the space bar to delete? Jolene Excel Worksheet Functions 1 November 3rd 04 07:03 PM


All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"