morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

If you want to simply remove ALL of them, you can use the Replace function
like this...

v = Replace(ActiveCell.value, vbLf, " ")

where vbLf is a pre-defined VB constant for Chr(10), which is the Line Feed
character. Note I have replaced the Line Feed characters with a blank space,
otherwise the text on the two lines of text would end up butted next to each
other (last word of one line next to the first word of the line below it).
If you don't want to remove them all, then you need to tell us more about
how they are placed in your text (and how you want them to look afterwards)
so we can figure out which to remove and which to leave.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements
in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as
valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either
late
last year, or earlier this year, and made one modification to-- adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Hi Rick,
Thank you for the response.
Ok, something's not working here.....
Using your code--
v = replace(activecell.value, vbLf, " ")

It doesn't remove the vblf items.
I then tried vbCr, and vbNullChar, as well as vbCrLf in place of vbLf.
What am I missing here?


"Rick Rothstein" wrote:

If you want to simply remove ALL of them, you can use the Replace function
like this...

v = Replace(ActiveCell.value, vbLf, " ")

where vbLf is a pre-defined VB constant for Chr(10), which is the Line Feed
character. Note I have replaced the Line Feed characters with a blank space,
otherwise the text on the two lines of text would end up butted next to each
other (last word of one line next to the first word of the line below it).
If you don't want to remove them all, then you need to tell us more about
how they are placed in your text (and how you want them to look afterwards)
so we can figure out which to remove and which to leave.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements
in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as
valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either
late
last year, or earlier this year, and made one modification to-- adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Ok, I just thought of something.....
in my case, I have 10 numeric characters, two vbLf characters, then 4
numeric characters, and then 3 more vbLf characters.
i.e.,
xxx-xxx-xxchr(10)chr(10)x.xxchr(10)chr(10)chr(10)

where the x, and hyphen, or dot are the elements I need to retain.
Would the replace look only at single vbLf's or does it look at ANY vbLf's,
and remove the?


"Rick Rothstein" wrote:

If you want to simply remove ALL of them, you can use the Replace function
like this...

v = Replace(ActiveCell.value, vbLf, " ")

where vbLf is a pre-defined VB constant for Chr(10), which is the Line Feed
character. Note I have replaced the Line Feed characters with a blank space,
otherwise the text on the two lines of text would end up butted next to each
other (last word of one line next to the first word of the line below it).
If you don't want to remove them all, then you need to tell us more about
how they are placed in your text (and how you want them to look afterwards)
so we can figure out which to remove and which to leave.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements
in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as
valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either
late
last year, or earlier this year, and made one modification to-- adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson

Hi Dave,
I tried as you said, and it spit out the message saying could not find
anything to replace. I then looked at the code it created, and tweaked it to
see which characters it could find-- nothing.....
I tried it 7 different ways
Any other ideas?

"Dave Peterson" wrote:

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson

Share the code you tried.

If it worked on a selection, share the address of that selection.

And make sure that there were alt-enters in cells in that selected area.

Steve wrote:

Hi Dave,
I tried as you said, and it spit out the message saying could not find
anything to replace. I then looked at the code it created, and tweaked it to
see which characters it could find-- nothing.....
I tried it 7 different ways
Any other ideas?

"Dave Peterson" wrote:

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson


--

Dave Peterson

Sub replaceTrim()

' replaceTrim Macro

Range("J14:J21").Select

Selection.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Based on Rick's final post, I learned that it was chr 9. Once I got that,
this worked famously!!!!
Darn pesky non-descript character numbers. where's my shotgun.... oh, wait,
that's my computer... sigh...... ;-)



"Dave Peterson" wrote:

Share the code you tried.

If it worked on a selection, share the address of that selection.

And make sure that there were alt-enters in cells in that selected area.

Steve wrote:

Hi Dave,
I tried as you said, and it spit out the message saying could not find
anything to replace. I then looked at the code it created, and tweaked it to
see which characters it could find-- nothing.....
I tried it 7 different ways
Any other ideas?

"Dave Peterson" wrote:

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson


--

Dave Peterson

VBA has some constants that make the code a bit easier to read.

vbTab (chr(9))
vbLf (chr(10))
vbCr (chr(13))

Chip Pearson has a very nice addin that will help determine what characters are
in the cell:
http://www.cpearson.com/excel/CellView.aspx

Steve wrote:

Sub replaceTrim()

' replaceTrim Macro

Range("J14:J21").Select

Selection.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Based on Rick's final post, I learned that it was chr 9. Once I got that,
this worked famously!!!!
Darn pesky non-descript character numbers. where's my shotgun.... oh, wait,
that's my computer... sigh...... ;-)

"Dave Peterson" wrote:

Share the code you tried.

If it worked on a selection, share the address of that selection.

And make sure that there were alt-enters in cells in that selected area.

Steve wrote:

Hi Dave,
I tried as you said, and it spit out the message saying could not find
anything to replace. I then looked at the code it created, and tweaked it to
see which characters it could find-- nothing.....
I tried it 7 different ways
Any other ideas?

"Dave Peterson" wrote:

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi again.
Rick helped me find that it was actually chr 9. Your code worked once I got
that, so just wanted you to know.....
Again-- thank you for your help! It's deeply appreciated.
Best.

"Dave Peterson" wrote:

Record a macro when you
Select your range
Edit|Replace
what: ctrl-j (same as that alt-enter)
with: (leave blank or a space character if you want)
replace all
stop recording

Add this recorded code to the top of your existing macro that trims the values.



Steve wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend

Thank you for your helps.

--

Dave Peterson

v = Replace(ActiveCell.value, Chr(10), Chr(32))

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Hi Gord,
No, I'm not sure. And after trying Rick's idea, I'm really thinking that
they're something else, other than chr(10), or chr(13)


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Give this a try... Select one of the cell that contains your suspicious
character and then go to the Immediate window in the VB editor and execute
these two lines (copy/paste them and then hit Enter on each line)...

S = ActiveCell.Value
For X = 1 to Len(S) : ? Asc(Mid(S, X, 1)) : Next

This will list out the ASCII character codes for each character in your
cell... just count over to the first non-recognizable letter and see what
the code is for that character.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
Hi Gord,
No, I'm not sure. And after trying Rick's idea, I'm really thinking that
they're something else, other than chr(10), or chr(13)


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve

wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements
in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as
valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either
late
last year, or earlier this year, and made one modification to-- adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Ok, I tried this, and I have to be missing something, or doing it wrong.
The second line throws a compile error, stating "method not valid without
suitable object".



"Rick Rothstein" wrote:

Give this a try... Select one of the cell that contains your suspicious
character and then go to the Immediate window in the VB editor and execute
these two lines (copy/paste them and then hit Enter on each line)...

S = ActiveCell.Value
For X = 1 to Len(S) : ? Asc(Mid(S, X, 1)) : Next

This will list out the ASCII character codes for each character in your
cell... just count over to the first non-recognizable letter and see what
the code is for that character.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
Hi Gord,
No, I'm not sure. And after trying Rick's idea, I'm really thinking that
they're something else, other than chr(10), or chr(13)


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve

wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements
in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as
valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either
late
last year, or earlier this year, and made one modification to-- adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Did you run the code in the Immediate window... or did you try and put it in
a Sub/EndSub block in a code window? If the latter, you have to change the ?
(which VB probably changed to the key word Print) to the command Debug.Print
so that its output will go to the Immediate window.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
Ok, I tried this, and I have to be missing something, or doing it wrong.
The second line throws a compile error, stating "method not valid without
suitable object".



"Rick Rothstein" wrote:

Give this a try... Select one of the cell that contains your suspicious
character and then go to the Immediate window in the VB editor and
execute
these two lines (copy/paste them and then hit Enter on each line)...

S = ActiveCell.Value
For X = 1 to Len(S) : ? Asc(Mid(S, X, 1)) : Next

This will list out the ASCII character codes for each character in your
cell... just count over to the first non-recognizable letter and see what
the code is for that character.

--
Rick (MVP - Excel)


"Steve" wrote in message
...
Hi Gord,
No, I'm not sure. And after trying Rick's idea, I'm really thinking
that
they're something else, other than chr(10), or chr(13)


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve

wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10)
elements
in
it, and I'd like to remove them. Well, let me restate that-- there
are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements
as
valid
string components. I've also tried right() and it too is picking up
the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student
either
late
last year, or earlier this year, and made one modification to--
adding
trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Just tried the TRimAll.....
Nope, that didn't work either......


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Using CHAR() and MID() function try and get which character it is and then
remove..

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

Just tried the TRimAll.....
Nope, that didn't work either......


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

you're going to laugh, or cry....
It returns a #value error.....
I first did char(mid(Num,StartNum,NumChar)) and got back #value error. I
checked the calculation steps, and it shows the right character, but then in
stepping through it just goes to the error.
I then tried char(right()), and char(left()).... same error


"Jacob Skaria" wrote:

Using CHAR() and MID() function try and get which character it is and then
remove..

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

Just tried the TRimAll.....
Nope, that didn't work either......


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

If you have the data in cell A1; try the below formula to check the 5th
position
=CHAR(MID(A1,5,1))

In VBA use CHR()

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

you're going to laugh, or cry....
It returns a #value error.....
I first did char(mid(Num,StartNum,NumChar)) and got back #value error. I
checked the calculation steps, and it shows the right character, but then in
stepping through it just goes to the error.
I then tried char(right()), and char(left()).... same error


"Jacob Skaria" wrote:

Using CHAR() and MID() function try and get which character it is and then
remove..

If this post helps click Yes
---------------
Jacob Skaria


"Steve" wrote:

Just tried the TRimAll.....
Nope, that didn't work either......


"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.

Hey Gord,
Rick helped me find that it was char 9. I modified your TrimAll from
DMcRitchie, to be chr(9) and it works.....
Thank you.
Best.

"Gord Dibben" wrote:

Are you sure they are chr(10) characters?

Perhpas they are chr(160)

Try David McRitchie's TRIMALL macro.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
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
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
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


Gord Dibben MS Excel MVP

On Thu, 9 Jul 2009 07:34:02 -0700, Steve
wrote:

morning all.
I'm trying to clean out some extra spaces in my data.
Using AutoCAD 2009, I extracted some data into an excel spreadsheet.
(Autodesk has created a dataextraction tool for the latest version)

The data set that I'm trying to clean up has a bunch of chr(10) elements in
it, and I'd like to remove them. Well, let me restate that-- there are
multiple blank space characters in the strings that I want to remove.
I've tried trim, but it appears to be treating the chr(10) elements as valid
string components. I've also tried right() and it too is picking up the
chr(10) elements.

How can I clean the chr(10) elements out?

Here's my present code (It's something I got from Gary's Student either late
last year, or earlier this year, and made one modification to-- adding trim)--

Dim v As String, val As Double
While ActiveCell.value < ""
v = ActiveCell.value
v = Trim(v)
val = Right(v, 4)
ActiveCell.Offset(0, 1).value = val
ActiveCell.Offset(1, 0).Select
Wend


Thank you for your helps.