eliminating extra spaces in Excel cells

Laura1 · 03-17-2006, 01:10 PM

Hello everyone, I just ran a report in Access, copied and pasted the data in
to Excel. Now, though, there are extra spaces after the data in each cell.
How do I get rid of these extra spaces? I need to do a mail merge and it's
not merging correctly because of all of these extra spaces.

So my merge looks like this: Joe Smith

Any help is appreciated!

03-17-2006, 01:10 PM

Hi

You could use Find/Replace, finding 2 spaces and replacing with 1

Andy.

"Laura1" <[email protected]> wrote in message
news:[email protected]...
> Hello everyone, I just ran a report in Access, copied and pasted the data
> in
> to Excel. Now, though, there are extra spaces after the data in each
> cell.
> How do I get rid of these extra spaces? I need to do a mail merge and
> it's
> not merging correctly because of all of these extra spaces.
>
> So my merge looks like this: Joe Smith
>
> Any help is appreciated!

SVC · 03-17-2006, 01:25 PM

Use the TRIM function. Create a blank column after column that has the extra
spaces (e.g. if it is Column A, create Column B), in cell B1 type =TRIM(A1),
then copy down. Now copy Column B and use Edit > Paste Special, Values to
paste over the data in Column B and eliminate the formula. You blank spaces
will be gone.

"Laura1" wrote:

> Hello everyone, I just ran a report in Access, copied and pasted the data in
> to Excel. Now, though, there are extra spaces after the data in each cell.
> How do I get rid of these extra spaces? I need to do a mail merge and it's
> not merging correctly because of all of these extra spaces.
>
> So my merge looks like this: Joe Smith
>
> Any help is appreciated!

**bxc2739** · 03-17-2006, 01:34 PM

Hi,
I feel compelled to post to this becuase I just had the (almost) exact
same problem that you have now. ANd it took me a day and a half to
go through the mess, but if it helps you can take a lot at

http://www.excelforum.com/showthread.php?t=523214
http://www.excelforum.com/showthread.php?t=523549

try this macro:

Sub No_010_013()
Dim rCell
Dim NewValue As String
Dim K As Long 'counter

For Each rCell In ActiveSheet.UsedRange
For K = 1 To Len(rCell.Value)
If Asc(Mid(rCell.Value, K, 1)) <> 13 Then
NewValue = NewValue & Mid(rCell.Value, K, 1)
Else
NewValue = NewValue & " "
End If
Next K
rCell.Value = "'" & NewValue
NewValue = ""
Next rCell
End Sub

Good Luck!

CLR · 03-17-2006, 01:35 PM

ASAP Utilities, available free from www.asap-utilities.com, has a feature
that will do this for you, among many other things.

Vaya con Dios,
Chuck, CABGx3

"SVC" wrote:

> Use the TRIM function. Create a blank column after column that has the extra
> spaces (e.g. if it is Column A, create Column B), in cell B1 type =TRIM(A1),
> then copy down. Now copy Column B and use Edit > Paste Special, Values to
> paste over the data in Column B and eliminate the formula. You blank spaces
> will be gone.
>
> "Laura1" wrote:
>
> > Hello everyone, I just ran a report in Access, copied and pasted the data in
> > to Excel. Now, though, there are extra spaces after the data in each cell.
> > How do I get rid of these extra spaces? I need to do a mail merge and it's
> > not merging correctly because of all of these extra spaces.
> >
> > So my merge looks like this: Joe Smith
> >
> > Any help is appreciated!