Seperating out Field

[#1]

Is there a way to separate fields that have been accidently combined in a source document?

Example: Banana-East or 15487-North

I need them to read

Field1	Field2
Banana	East
15487	North

[ciaran_mcgowan]

Subfield(Example, '-', 1) AS Field1

Subfield(Example, '-', 2) AS Field2

[#3]

Thank you but that only works for fields separated by a dash ('-'), is there a way to do this for other characters as well?

[sunny_talwar]

Is there a list of possible characters?

[#5]

From what I can see it can be a space or one of these:

% $ & _ etc.

There are others. Mostly special characters.

[sunny_talwar]

May be try with a Mapping Load:

MappingTable:

Mapping

LOAD * Inline [

SpecialChar, Default

%, |

$, |

&, |

_, |

" " , |

-, |

];

Table:

LOAD Subfield(Fieldname, '|', 1) AS Field1,

  Subfield(Fieldname, '|', 2) AS Field2;

LOAD MapSubString('MappingTable', FieldName) as Fieldname;

LOAD * Inline [

FieldName

Banana-East

Banana&West

15487$North

1548_South

1010 North

];

UPDATE:
Essentially add all possible special characters to your mapping load. This will convert all of them into pipe (or you can use any other one you would like) and then use SubField with a pipe (|)

[ciaran_mcgowan]

If you know all the characters you could still use SUBFIELD by replace all these characters to a single one.

Subfield(

    Replace(Replace(Replace(Replace(Replace(Example,'%','-'),'$','-'),'&','-'),'_','-'),' ','-')

, '-', 1) AS Field1

Subfield(

    Replace(Replace(Replace(Replace(Replace(Example,'%','-'),'$','-'),'&','-'),'_','-'),' ','-')

, '-', 2) AS Field2

This will replace all your special characters to '-' and then applies my original SUBFIELD function to it. You can add as many REPLACE functions as you need.

[sunny_talwar]

Very difficult to manage this ciaran.mcgowan‌, I would rather use a mapping load here

[ciaran_mcgowan · Accepted solution]

True. He could also try something like this:

Left(Example, FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)-1) AS Field 1

Right(Example, Len(Example)-FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)) AS Field 2

No need for Mapping Load and can put all special characters between 2 single quotes.

[antoniotiman]

Try

Left('A11&B22',FindOneOf('A11&B22','-_$%&/()=')-1)
Right('A11&B22',FindOneOf('A11&B22','-_$%&/()=')-1)