CSV space problem (Resolved)

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
833
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Hi Jack

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


Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top