Data Split

[#1]

HI,

I have data something like the below

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

I need to display only the last four numbers that are before '-'.

Answer will be something like below.

0543

5171

0043

0005

Null

0009

Null

0011

Null

Please find the attachment.

Regards,

Keerthi KS

[robert_mika]

=KeepChar('TMD-VILLA-0005','0123456789')

or

Load

KeepChar( Your Field,'0123456789') as Order

[maxgro · Accepted solution]

load

     *,

     if(index(s, '-') > 1, SubField(s, '-', -1), null()) as t

inline [

s

0046-APT-0543

WM-APT-5171

0043

TMD-VILLA-0005

POARR-VILLA

HENNUR-0009

HENNUR

0063-0011

C1100

];

[#4]

Only the number before '-' is required. 

[robert_mika]

Sorry my bad.

Massimo nailed it.

[#6]

If i have data something like 0072-Villa-B9-M i need B9-M. Can you help me with this.

Sorry forgot to mention earlier.

[ogster1974]

something like this will handle your extra requirement.

Check for the 5th char from the right if its a "-" display the 4 chars at the end of your string.  I think this covers all your use cases.

[robert_mika]

Try:

=TextBetween('0072-Villa-B9-M','-','',2)

[maxgro]

do you mean

when there are 3 '-', get the last (right) 2 fields?

when 4 the last 3 fields?

......

if not we need some other rule to extract the correct string

[#10]

Yes Massimo Exatly that is what is required