suppose you need to truncate the leading and trailing characters from within an excel file , mainly to remove the prefixes and sufixes adding by rating engine (telecom) and the filenames attached here for example like
(Huawei.00377819.DAT.20091021185132.cc.conv
and
Huawei1.00215367.DAT.20091020101704.cc.conv
)
Huawei. and Huawei1. are the prefixes to remove and .cc.conv is the sufix that need to remove). Since the string .20091 is repeating allover suppose the list is huge like around 50000 plus entries ,we can use the following function in excel which is equivalent for substr(instr()) in Oracle SQL.
=MID(C2566,SEARCH(".20091",C2566)-12,27)
1 comment:
Date with format "201702" to be converted to "02-2017" ie. YYYYMM to MM-YYYY, try =CONCATENATE(MID(B2,SEARCH("20",B2)+4,2),"-",MID(B2,SEARCH("20",B2)+0,4))
output will be "02-2017"
Post a Comment