Solved: Trim extra characters from Column F

[mike31z]

I need help removing extra charters from 2 columns. The number of lines varies in each file I receive. Column E has four characters of which 2 are text, 2 are spaces. Column F has four character of which 1 to 3 are text and the rest are spaces.

Background: I get a csv formatted file emailed to me as a txt file. I use excel 2000 to convert that file to a xls file. In the new xls file 2 column E and F have extra character that effect sorting.

I can use “left (cell Name,2)” to obtain the 2 text characters for column E .
"Trim(cellName)" also works even better at the cell level.


Does any know of a code that can remove the extra non text characters from a column E and F?

I have attached sample xls file so you can see the extra characters.

Thanks for Looking

Mike in Wisconsin

[GTO]

...Background: I get a csv formatted file emailed to me as a txt file. I use excel 2000 to convert that file to a xls file. In the new xls file 2 column E and F have extra character that effect sorting.

I can use “left (cell Name,2)” to obtain the 2 text characters for column E .
"Trim(cellName)" also works even better at the cell level...

VERY quickly looked, so apologies if I glossed over anything, but it appeared to me that the text files may just have spaces (Chr(32) ) tacked in to align. This still uses trim, so cell by cell (so-to-speak), but flopping vals into an array may make it acceptably quick. No "safetys" but as quick try, maybe:

[VBA]Option Explicit

Sub example()
Dim ary
Dim x As Long, y As Long

With Sheet1
ary = .Range(.Cells(4, "E"), .Cells(.Rows.Count, "E").End(xlUp)).Resize(, 2).Value
For x = 1 To UBound(ary)
For y = 1 To 2
ary(x, y) = Trim(ary(x, y))
Next
Next
.Range(.Cells(4, "E"), .Cells(.Rows.Count, "E").End(xlUp)).Resize(, 2).Value = ary
End With
End Sub[/VBA]

[mike31z]

I got you code to work in the attached xls file.

when I put the code in another xls file named "WTAe1.xls" and try to make it work on sheet "Event2" I get a compile error: Variable not defined

How can I make the code work on different named sheets? I figured it out. 15 Minuted after posting
i MODIFIED YOUR code example below.

[VBA]Sub exampleD2()

'this defined where the code will be used.

Dim ws1 As Worksheet
Set ws1 = Workbooks("WTAe1.xls").Sheets("Event2")

Dim ary
Dim x As Long, y As Long

With ws1
ary = .Range(.Cells(4, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Resize(, 2).Value
For x = 1 To UBound(ary)
For y = 1 To 2
ary(x, y) = Trim(ary(x, y))
Next
Next
.Range(.Cells(4, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Resize(, 2).Value = ary
End With
End Sub[/VBA]

Mike In Wisconsin

[GTO]

I got you code to work in the attached xls file.

when I put the code in another xls file named "WTAe1.xls" and try to make it work on sheet "Event2" I get a compile error: Variable not defined...

Hi Mike,

I see that you got it working , so just as a possible bit of extra information.

Reference the code working for the attached wb at #1, on the worksheet named Event1, but falling over when you tried to apply it to a worksheet named Event2:

I would suspect you may have tried substituting 'Event2' or something incorrect for the worksheet's CodeName. In your attachment, Sheet1 is the CodeName for 'Event1'. In your actual wb, while in VBE, look in the Project Window, which usually has the caption of 'Project - VBAProject' in its titlebar. If not already expanded, expand 'Microsoft Excel Objects', wherein existing sheets and ThisWorkbook will be listed. Wherever you find 'Event2' listed in parenthesis, the name left of that is the sheets CodeName, which can be used just like one would use UserForm1.Show, that is, without quotes. DOes that make sense?

Mark

[mike31z]

I get it now, I just a fringe VBA code guy I took the WS1 from another VBA code some one helped me with and it worked. Your is simple and effective. which I like because its part of the (KISS principle) Keep It Simple Stupid.

Thanks again for your help.

Mike in Wisconsin