CSV space problem (Resolved)

[Jak]

I use Excel to edit and arrange data into fields that I import to an Access Database. 70% of the time the data is non problematic. On those occasions when it is it takes a bit of time to delete unwanted formatting etc. One such problem is when I receive CSV files. Sometimes there are spaces after the text portions. This means that when the file is imported as comma delimited and a comma separates each field, there are still unwanted spaces after each text portion. I began by highlighting all the data and replacing the spaces with nothing. This was fine until I noticed that names that were originally separated by a space were now joined and had to have the spaces put back in. Small file sizes are quick to edit but larger files take an age.

As an example:

John James,123,Andrew Spencer,Y,123456,A,M

The above is an example of what each field might contain. There are no spaces after each text portion. Alternatively, here is a problematic example:

John James ,123 ,Andrew Spencer ,Y ,123456 ,A ,M

This is caused because the original data has spaces after each text portion. By using replace all spaces with nothing I end up with this:

JohnJames,123,AndrewSpencer,Y,123456,A,M

There is now no space between John and James or Andrew and Spenser. What I would like is a method of replacing the unwanted spaces without joining separate texts together. Any help in this capacity would be welcomed.


[ This Message was edited by: Jak on 2002-05-15 06:29 ]

[Chris Davison]

hmmm

could you replace " ," with just ","

ie " SPACE COMMA" with just "COMMA" ?

this would only capture cases of a space where there is an associated comma seperator with your required comma

[Jak]

Cheers Chris

I will give your solution a go. Thanks

[Dave Hawley]

Hi Jack


The Text to Columns feature found under Data can help with these sort of problems.

The other helpful function is the TRIM function, eg TRIM(A1)

[Jak]

Hi Dave

I have not used the Trim function. I will have a look in Excel help menu for sample data. I have been working on a macro based on previous idea provided by Chris. The macro does remove all the spaces but causes a run time error as follows:

Run-time error '91':

object variable or with block variable not set

I am not sure how to go about debugging this error. Here is the macro code I have put together. Any help with the debug would be welcomed. I the meantime I will have a look at the Trim function. Cheers

Sub Macro2()

Application.ScreenUpdating = False

Do While ActiveCell > 1
ActiveCell.Replace What:=" ,", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:=" ,", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Loop

Application.ScreenUpdating = True

End Sub

[Dave Hawley]

Hi Jack

Here is a nice simply very fast macro. Just select your data Column before running it.

Sub GoAwaySpaces()
 Dim rCells As Range
 
 Set rCells = Range(Selection.Cells(1, 1), _
                    Selection.Cells(65536, 1).End(xlUp))
                    
 rCells.EntireColumn.Insert
 rCells.Offset(0, -1).FormulaR1C1 = "=TRIM(RC[1])"
 rCells.Offset(0, -1) = rCells.Offset(0, -1).Value
 
 End Sub

[Jak]

Many thanks Dave for the Trim code. I do like the macros name. I will try it out and advise if there are any problems.