Results 1 to 8 of 8

Thread: Text Array (03)

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text Array (03)

    I have a cell containing an array whose fields are delimited by a comma with each field surrounded by a quote mark. There may be one or more fields containing a comma such as in a vendor's name ie "XYZ, LLC".

    I am looking for a recommendation as how to substitute an empty space so that it would read "XYS LLC". In other words stripping out the comma in those fields but remember each field is comma delimited.

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text Array (03)

    Don't worry about it, just do a find and replace. Replace , with nothing. Do it within an excel shell not notepad.....let Excel worry about the comma seperation. If you are unsure, make a back up and go for it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    Could you post a sample workbook that shows what you have? From your simple example in this post, "XYZ, LLC" could be taken care of simply by using Edit/Replace to replace commas with spaces. Your example also looks like it replaced the Z with an S, but that does not factor into your description.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    Legare,

    I have attached a sample file. I am reading one line of text at a time from a flat file and enounter a challenges if one of the fields contains a comma such as described in the sample file. I simple parsing on a comma and import into Excel will not work.

    Thanks,
    John

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    Legare,

    I'm a little confused on the oCell.Value as it is not being referenced anywhere.

    John

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    OK, now I think I understand what you need. Does the function below do what you need?

    <code>
    Public Function RemoveComma(oRng As Range) As String
    Dim I As Long
    Dim strIn As String
    Dim bInStr As Boolean
    strIn = oRng.Value
    If Len(strIn) < 1 Then Exit Function
    bInStr = False
    For I = 1 To Len(strIn)
    If Mid(strIn, I, 1) = """" Then bInStr = Not bInStr
    If bInStr Then
    If Mid(strIn, I, 1) = "," Then
    RemoveComma = RemoveComma & " "
    Else
    RemoveComma = RemoveComma & Mid(strIn, I, 1)
    End If
    Else
    RemoveComma = RemoveComma & Mid(strIn, I, 1)
    End If
    Next I
    End Function
    </code>
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    Sorry about that. I started to make a change in the function after I had tested it, and then I realized that the change was not necessary. I didn't get everything changed back to what it should have been. I have edited my previous post to correct the error.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text Array (03)

    Legare,

    It appears to be doing what I want. I need to test it further.

    Thank you,
    John

Posting Permissions

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