CSV space problem (Resolved)
MZ Tools makes life easier for the Excel VBA coder
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.

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
  •  

 

 
DMCA.com