trim, chr(10)

[Steve]

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.

 

[Rick Rothstein]

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.

 

[Dave Peterson]

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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Gord Dibben]

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

 

[Steve]

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?

 

[Steve]

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?

 

[Steve]

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)

 

[Steve]

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

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Steve]

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".

 

[Steve]

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

 

[Steve]

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?

 

[Rick Rothstein]

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.

 

[Jacob Skaria]

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

 

[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]

ah, so it did.....
hmm....
Ok, I have 20 characters, and they are.... it appears 9 is my (demon)
character....
48
49
52
45
48
51
50
45
50
55
9
9
48
46
49
55
9
9
9

Rick..... BINGO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
We've got it...... Thank you, thank you, thank you!!!!!!

 

[Steve]

Jacob,
Thank you for your help.
Rick had the fix with his ascii tool.
Turns out it was chr(9)
see my last response to Rick....
Again-- thank you for your help-- it's deeply appreciated.

 

[Steve]

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.