Delete all delimiting characters in string starting at third occurence to end 1

[waubain]

I am screen scraping data from our legacy system into Excel. Data is delimited by a pipe character. The number of columns is variable per record and I am working with 50,000+ rows of data.

I am trying to delete the pipe(|) character before it is written to Excel starting at the third occurence of the pipe to the last.

Here is an example of data and is captured in the variable strReadline:

Sally|119|
Bob|679|Mary had a|little lamb its|fleece was white|as snow|
Jane|345|
Karen|797|The good ship|lollipop|
Joe|234|Heigh-ho the|derry-o the|farmer in the dell|

When there is more than 2 pipes, then my code is deleting the first three fields. Maybe I do not fully understand the Replace Function. If the search starts at k then why is it deleting everything before it?

...code that opens Excel and captures strReadline


' delete all pipe characters starting at the third pipe

 i = 0
 For k = 1 To Len(strReadline)
   If Mid(strReadline, k, 1) = "|" Then
     i = i + 1
     If i > 2 Then
       strReadline = Replace(strReadline, "|", " ", k)
       Exit For
     End If
   End If
 Next k
        
 objWS.Cells(rw, "A").Formula = Readline

...code to loop back and capture next record

This is output from the above code.


Sally|119|
little lamb its|fleece was white|as snow|
Jane|345|
lollipop
derry-o the|farmer in the dell|

Once in Excel I split with TextToColumns on the pipe, but then have to Concatenate all the columns from 3 to last back which is taking a significant amount of time. This is why I am trying to remove the unnecessary pipes before sending to excel.

Any suggestions would be appreciated.

You don't know what you don't know...

 

[PHV]

This is the behaviour of the Replace function used with the start named argument.
You may use this:
strReadline = Left(strReadline, k - 1) & Replace(strReadline, "|", " ", k)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way, without loop:

...code that opens Excel and captures strReadline
' delete all pipe characters starting at the third pipe
strReadline = Replace(Replace(strReadline, "|", " "), " ", "|", 1, 2)
objWS.Cells(rw, "A").Formula = strReadline
...code to loop back and capture next record

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

PH,
Thank you for the answers. I choose the second method and it works perfectly. If I am understanding your code correctly, it is deleting all the pipes and then replacing the first 2 spaces with a pipe. I would not have thought of that solution on my own. Thanks again.

You don't know what you don't know...