Hi,
I've an unusual request but I'm almost there and just need some help getting over the final hurdle. I have a file containing a few thousand records that contains strings I need to split in two. The thing is, the strings have varying lengths and no clear distinctive character I can use to separate them, apart from an upper case character appearing after the first character. As the strings were originally concatenations of two strings with nothing in between, I've managed to split them using a series of loops searching for 2 capital letters and returning the number of the subfield of the first word in the string that contains 2 upper case letters and 1 or more lower case records.
Example:
| Original | Subfield_Split | Header | Description |
|---|
| Route A - DavidReturn journey | 4 | Route A - David | Return journey |
| James B - Driver - Part Time.No more than 4 hours round trip | 7 | James B - Driver - Part Time. | No more than 4 hours round trip |
I understand it's strange but I need to display as close to the Header & Description fields as I can. I've already worked out the subfield I need to split the string on, now I need to find a way to display it.Do I create another series of loops that concat the correct number of subfields together or is there a better way?
So the first field would be:
Subfield(Original, ' ', 1) & ' ' & Subfield(Original, ' ', 2) & ' ' & Subfield(Original, ' ', 3) & ' ' & Subfield(Original, ' ', 4) AS Header,
Subfield(Original, ' ', 4) & ' ' & Subfield(Original, ' ', 5) & ' ' & Subfield(Original, ' ', 6) AS Description
And the second would be:
Subfield(Original, ' ', 1) & ' ' & Subfield(Original, ' ', 2) & ' ' & Subfield(Original, ' ', 3) & ' ' & Subfield(Original, ' ', 4) & ' ' & Subfield(Original, ' ', 5) & ' ' & Subfield(Original, ' ', 6) & ' ' & Subfield(Original, ' ', 7) AS Header,
Subfield(Original, ' ', 7) & ' ' & Subfield(Original, ' ', 😎 & ' ' & Subfield(Original, ' ', 9) & ' ' & Subfield(Original, ' ', 10)........etc. AS Description
