Separate string into two fields with delimiter

[Anonymous]

Hi guys,

I'm trying to separate a field into 2 different fields where my delimiter is '-' although there are a bunch of them on the text.

I tryied the SubField function but it gets only the string before the next delimiter. For eg.

My text is (delimiter in bold red):

G820 - CNHi Comercio de Pecas Ltda-TRADE RECEIVABLES NACIONAL-ALL PC-ALL TP-GRP----0L--

Trying to get:

G820 - CNHi Comercio de Pecas Ltda

and

TRADE RECEIVABLES NACIONAL-ALL PC-ALL TP-GRP

SubField(Myfield,3) gets only 'TRADE RECEIVABLES NACIONAL'

Note that the first bit of the string and the last vary, so I cannot use the Index function searching for a string.

Thanks!

[richard_chilvers]

Hi Claudio

Its hard to answer without knowing exactly how your data is formatted.

Will the left 7 characters always be 'Annn - ' where A is an alpha and n are numeric digits ?

If so, this might make life easier because you can ignore these first 7 character (including the first '-') when you analyse the string.

[marcus_sommer · Accepted solution]

Some kind of logic will be needed. If it's always the second '-' then you could use:

mid(Field, 1, index(Field, '-', 2) - 1)

and

mid(Field, index(Field, '-', 2) + 1, 100)

- Marcus

[Anonymous]

Richard, the text before and after the delimiter I need varies in lenght, so I can't ignore measure how many characters to ignore. Thanks though!

[Anonymous]

Great approach, Marcus! Thanks!