Issue removing leading and lagging spaces

  • Thread starter Thread starter robs3131
  • Start date Start date
R

robs3131

Hi all,

I'm having a problem removing leading and lagging spaces from cells in a
column of data (FYI - the data was input from an Excel spreadsheet that was
dowloaded from a website). I looked through some other postings and found
the "Trimall" macro -- I've tried this but it is still not removing the
spaces. Below is the Trimall macro with two lines that I added (to select
all cells in Sheet1) noted with **. Column AD specifically has the data that
I am trying to remove the spaces from.

I appreciate your help!

Sub TrimALL()

**Sheets("Sheet1").Select
**Cells.Select
'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
'-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
' - Optionally reenable improperly terminated Change Event macros
Application.DisplayAlerts = True
Application.EnableEvents = True 'should be part of Change Event macro
If Application.Calculation = xlCalculationManual Then
MsgBox "Calculation was OFF will be turned ON upon completion"
End If
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(13), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(21), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'---------------------------
Selection.Replace What:=Chr(8), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=Chr(9), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Make sure your code is in a General module--not behind a worksheet and not
behind ThisWorkbook.

And

Sheets("Sheet1").Select
range("AD1").entirecolumn.Select

looks like it would work to me.

But your code looked like it would have gotten rid of all the spaces on the
worksheet. What happened when you tried it?
 
Hi Dave,

The code I wrote below does replace spaces when I actually go in and add a
space at the end of a word in a cell, however, it does not replace the
leading and lagging spaces that already exist in column AD.

I'm starting to wonder if the problem has to do with something else
altogether -- when I click into a cell in column AD, there are no leading or
lagging spaces to the value in the cell (the cursor blinks directly next to
the first and last characters when I click there) -- leading and lagging
spaces show up when I copy a cell in column AD and then bring up the "Find"
menu by clicking CTRL F and then pasting the value into the "Find What"
box...any idea on what would cause this and/or how to resolve this?

Thanks!
 
Are you copying from the formula bar?

If no, then try that.
Hi Dave,

The code I wrote below does replace spaces when I actually go in and add a
space at the end of a word in a cell, however, it does not replace the
leading and lagging spaces that already exist in column AD.

I'm starting to wonder if the problem has to do with something else
altogether -- when I click into a cell in column AD, there are no leading or
lagging spaces to the value in the cell (the cursor blinks directly next to
the first and last characters when I click there) -- leading and lagging
spaces show up when I copy a cell in column AD and then bring up the "Find"
menu by clicking CTRL F and then pasting the value into the "Find What"
box...any idea on what would cause this and/or how to resolve this?

Thanks!
Click to expand...
 
Hi Dave,

I found a workaround to my issue -- if I first delete out column AD before
putting in the formula into AD that puts the data into AD, the issue does not
come up.

Out of curiosity, is there a way to code so that when copying a cell value,
the code copies from the formula bar instead of the actual cell? When I
record-macro and do this, it just says "Activecell.formula = ...".

Thanks for your help!
 
I'm kind of confused at what you're doing (and why the original code didn't
work, too!).

But maybe...

activecell.formular1c1 = someothercell.formular1c1


Hi Dave,

I found a workaround to my issue -- if I first delete out column AD before
putting in the formula into AD that puts the data into AD, the issue does not
come up.

Out of curiosity, is there a way to code so that when copying a cell value,
the code copies from the formula bar instead of the actual cell? When I
record-macro and do this, it just says "Activecell.formula = ...".

Thanks for your help!
Click to expand...
 
Hi Dave,

Essentially, what's happening, in sequence, is the following:

- The code is using the worksheet concatenation formula in column AD to
combine the values of two other columns.
- The code then loops through column AD, copying each cell value, and then
using the worksheet find function to locate the value in another sheet.

The issue is that when the code copies the cell in column AD and pastes it
into the Find menu within Excel, the pasted value has both leading and
lagging spaces. When I click within the cell in column AD, however, there
are no leading or lagging spaces -- the spaces only show up when the codes
pastes the cell value into the Find menu.

The code is large as there is a log happening in the worksheet prior to the
concatenation of column AD or else I'd paste it in here...I suspect that the
fact that the data is pasted from an Excel download from a website has
something to do with the issue. Since I have figured out a workaround
(deleting columns AD - IV before putting the concatenation formula into AD)
and since I'm swamped on getting this project done, I've decided to pass on
trying to figure out why this is happening.

Thanks,

Robert
 
First, I'm not sure why or how you're pasting the value into the find dialog.
But why not just use cells.find(what:=somecell.value, ...)

Second, if you want to find out what's in that cell, grab this utility from Chip
Pearson:
http://www.cpearson.com/excel/CellView.aspx


Hi Dave,

Essentially, what's happening, in sequence, is the following:

- The code is using the worksheet concatenation formula in column AD to
combine the values of two other columns.
- The code then loops through column AD, copying each cell value, and then
using the worksheet find function to locate the value in another sheet.

The issue is that when the code copies the cell in column AD and pastes it
into the Find menu within Excel, the pasted value has both leading and
lagging spaces. When I click within the cell in column AD, however, there
are no leading or lagging spaces -- the spaces only show up when the codes
pastes the cell value into the Find menu.

The code is large as there is a log happening in the worksheet prior to the
concatenation of column AD or else I'd paste it in here...I suspect that the
fact that the data is pasted from an Excel download from a website has
something to do with the issue. Since I have figured out a workaround
(deleting columns AD - IV before putting the concatenation formula into AD)
and since I'm swamped on getting this project done, I've decided to pass on
trying to figure out why this is happening.

Thanks,

Robert
--
Robert

Dave Peterson said:
I'm kind of confused at what you're doing (and why the original code didn't
work, too!).

But maybe...

activecell.formular1c1 = someothercell.formular1c1
Click to expand...
Click to expand...
 
Hi Dave,

Actually I am using "cells.find(what:=A.value, ...), where A is a variable
which loops through a range of cells. The issue is that for whatever reason,
the cells which A loops through have leading and lagging values that can't be
seen when clicking witihin the cell -- the only way I seem to be able to get
these spaces to show up is by copying and pasting the values into the Find
dialogue box (which I assumed the "cells.find..." code activated).

Thanks for the link to the utility - I haven't installed it as I have a
workaround to the issue above (and just don't have the time to investigate
the root cause), but I will use it in the future as needed.

I really appreciate all your help!

--
Robert


Dave Peterson said:
First, I'm not sure why or how you're pasting the value into the find dialog.
But why not just use cells.find(what:=somecell.value, ...)

Second, if you want to find out what's in that cell, grab this utility from Chip
Pearson:
http://www.cpearson.com/excel/CellView.aspx


Hi Dave,

Essentially, what's happening, in sequence, is the following:

- The code is using the worksheet concatenation formula in column AD to
combine the values of two other columns.
- The code then loops through column AD, copying each cell value, and then
using the worksheet find function to locate the value in another sheet.

The issue is that when the code copies the cell in column AD and pastes it
into the Find menu within Excel, the pasted value has both leading and
lagging spaces. When I click within the cell in column AD, however, there
are no leading or lagging spaces -- the spaces only show up when the codes
pastes the cell value into the Find menu.

The code is large as there is a log happening in the worksheet prior to the
concatenation of column AD or else I'd paste it in here...I suspect that the
fact that the data is pasted from an Excel download from a website has
something to do with the issue. Since I have figured out a workaround
(deleting columns AD - IV before putting the concatenation formula into AD)
and since I'm swamped on getting this project done, I've decided to pass on
trying to figure out why this is happening.

Thanks,

Robert
Click to expand...
Click to expand...
 
I'm not sure where your original data is coming from--but if you're copying from
a web page, you may be "seeing" those HTML non-breaking space characters.

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")
Hi Dave,

Actually I am using "cells.find(what:=A.value, ...), where A is a variable
which loops through a range of cells. The issue is that for whatever reason,
the cells which A loops through have leading and lagging values that can't be
seen when clicking witihin the cell -- the only way I seem to be able to get
these spaces to show up is by copying and pasting the values into the Find
dialogue box (which I assumed the "cells.find..." code activated).

Thanks for the link to the utility - I haven't installed it as I have a
workaround to the issue above (and just don't have the time to investigate
the root cause), but I will use it in the future as needed.

I really appreciate all your help!
Click to expand...
 
Hi Dave,

Yeah, I tried that macro already -- what's strange is that I used the
Trimall macro but the spaces were still there...at any rate, I am using a
workaround to get around the issue. Thanks!
 
Did you try Chip Pearson's cellview workbook?

I bet that you'd know what was in the cell.


Hi Dave,

Yeah, I tried that macro already -- what's strange is that I used the
Trimall macro but the spaces were still there...at any rate, I am using a
workaround to get around the issue. Thanks!
Click to expand...
 
Back
Top