Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I have the data with number of symbols and i want to split the data as the output model.
I am attach the excel file.Please give the suggestion.
Thanks in advance.
Could you explain the logic (generic) how you got the output column?
Hi Suman,
I am assuming
1) you want data from first occurrence of "T"
2) the (HOL), and ($) will always at the end of string
in such scenario below formula may help you out
=subfield(PurgeChar(Right(Rtrim(LTrim(data)),Len(Rtrim(LTrim(data)))-(Index(Rtrim(LTrim(data)),'T')-1)),' .'),'(',1)
Regards,
Anupam
Hello Suman,
Try this.
=PurgeChar(Mid(data,Index(data,'T',1),Len(data)),'($)')
=PurgeChar(Mid(data,Index(data,'T',1),Len(data)),'($).')
Hi,anupam,
Thanks for giving good suggestion and one more of split 11-TS7865HFD-HTGE in this last words are shows only like HTGE instead of TS7865HFD. Please give suggestion.
Thanks.
Hi Suman,
Just add one more subfield as below
=Subfield(subfield(PurgeChar(Right(Rtrim(LTrim(data)),Len(Rtrim(LTrim(data)))-(Index(Rtrim(LTrim(data)),'T')-1)),' .'),'(',1),'-',1)
IF the answer helped you to solve your issue kindly mark it as correct and helpful.
Regards,
Anupam
Hi you can do it using MapSubString:
ReplaceMap:
mapping LOAD * INLINE [
char, replace
(, ""
), ""
”, ""
/, ""
–, ""
$, ""
., ""
" ", ""
];
TestData:
LOAD
*,
trim(MapSubString('ReplaceMap', mid(data, Index(data, 'T'), len(data)))) as ReplacedString
;
LOAD * INLINE [
data, OUTPUT
01-TS7865HFD, TS7865HFD
02-TS 65HGT, TS65HGT
03 TG 98HJ(HOL), TG98HJ
04 TG.78.KJI, TG78KJI
TS89GHT($), TS89GHT
TG90GFR, TG90GFR
];
source: