Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have excel sheet, with field name EMP(NAME/ID),
The data in some rows in that field be like 'Jake-twikdl0003', and in some rows data be like 'twikdl0003'
From both rows I need to pick 'kdl'.....how to pull this from two different types of rows?
You can try with index('kdl') if this values is >0 the string is in the record
Option1, using index() ,
using lower as index is case sensitive but will match the search string in any position of the string
Where Index( lower(EmpName),'kdl') ;
Option2, using Wildmatch() , case insensitive but will only find search string as per position in string
Where Wildmatch( EmpName,'*kdl*') ; // finds kdl in the middle of the string
Where Wildmatch( EmpName,'kdl*') ; // finds kdl in the beginning of the string
Where Wildmatch( EmpName,'*kdl') ; // finds kdl in the end of the string
Where Wildmatch( EmpName,'kdl*','*kdl*','*kdl') ; // finds kdl in all above scenarios
Some rows are like empid-ssdkdl223
some rows are like ssdkdl223
from every row I need to pull 3 letters after ssd...how to do it??
Use mid() function
Mid(empname,10,12)
As @vinieme12 mentioned, the index function can be useful in this case. I'm combining it with mid() which is also mentioned.
mid( [EMP(NAME/ID)], Index( lower( [EMP(NAME/ID)] ),'ssd') + 3, 3)
This will pull the first three letter after the first occurrence of the string 'ssd' in the field [EMP(NAME/ID)].