Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
=KeepChar('TMD-VILLA-0005','0123456789')
or
Load
KeepChar( Your Field,'0123456789') as Order
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
];
Only the number before '-' is required.
Sorry my bad.
Massimo nailed it.
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.
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.
Try:
=TextBetween('0072-Villa-B9-M','-','',2)
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
Yes Massimo Exatly that is what is required