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

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
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


Code:
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!
 
Last edited:

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,166
Office Version
2007
Platform
Windows
I checked your code and your example and I do not have that problem, but try something like this:

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

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
419
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,166
Office Version
2007
Platform
Windows
Usually happens, you have to visualize from another perspective.

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

Watch MrExcel Video

Forum statistics

Threads
1,095,669
Messages
5,445,872
Members
405,366
Latest member
Not_Really_Me

This Week's Hot Topics

Top