Split() on Asterisk Delimited Text--Posing a problem with spaces

[Atroxell]

Hi,

I have a file that contains a great number of records as a single, long line of text. The records are delimited by a tilde (~), and the fields within the records are delimited by an asterisk (*).

I have second file that is a summary for records in the first file that have some kind of error. So the second file is simply a group of records saying, "There's a bad value at index xxxxxx" with xxxxx being the record number.

I'm building a process that splits the file into an array containing the records, then inspects the array to find fields flagged as errors--in this specific instance, a zip code.

If the error code says, "There's a bad zip code at index 5335", it moves to records 5335 and finds the offending value. e.g., "999994056", then splits the errored record into another array to isolate the bad value. Then, the code will loop backwards to find an index that starts with "N1" (the indicator for this particular record type) and then replace the errored zip code in 5335 with the Zip Code value from the previous record. I can do this without any issues for MOST of the Zip Code errors.

My problem comes when the split() function is called for the record containing the Zip Code. Because the fields are asterisk delimited, they split just fine when I use

arBadRec = split(arRecChk(x),"*")

​

When the record looks like:

N1*BIRMINGHAM*AL*35201* and so on....

The array looks like:

arFlds(0) = "N1"
arFlds(1) = "BIRMINGHAM"
arFlds(2) = "AL"
arFlds(3) = "35201"
and so on...

But runs into problems when the record has a space in the city name Or anywhere else) like:

N1*SAN DIEGO*CA*92093* and so on...

So this array looks like:

arFlds(0) = "N1"
arFlds(1) = "SAN"
arFlds(2) = "DIEGO"
arFlds(3) = "CA"
arFlds(4) = "92093"
and so on...​

I need the record to only split() on the asterisk and keep all the values between together.

Any suggestions? TIA!

 

[DanteAmor]

I checked your code and your example and I do not have that problem, but try something like this:

    wValue = Replace(arRecChk(X), " ", "|")
    arBadRec = Split(wValue, "*")
    
    For i = 0 To UBound(arBadRec)
        y = Replace(arBadRec(i), "|", " ")
    Next

 

[Atroxell]

Thank you, Dante! I was hoping to not have to add any more code (I'm stingy when it comes to that), but this worked out just fine. As it is, I was able to modify and fit it into my existing code with only the addition of the For...Next loop.

Always interesting to me that the seemingly complex problems have the most direct and simple answers.... Must have been the reason why I was wracking my brain for ofar too long on this one.

Thanks again,
atroxell

 

[DanteAmor]

Usually happens, you have to visualize from another perspective.

I'm glad to help you. Thanks for the feedback.