Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: CSV space problem (Resolved)

  1. #1
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    :: Pharma Z - Family drugstore ::

  3. #3
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers Chris

    I will give your solution a go. Thanks

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)




  5. #5
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    Board Regular Jak's Avatar
    Join Date
    Apr 2002
    Location
    Newcastle upon Tyne
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •