Pulling certain data from excel in QS

[Bharathi09]

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?

[rthatcher]

Hi,

Is the 'kdl####' always at the end of the cell, with the same amount of characters (3 letters followed by 4 numbers)?

If so, you could use RIGHT(field_name, 7) to select the last 7 digits.

[Bharathi09]

[cid:227eb440-b787-4692-971e-c1fe3d22d117]
data is like this I need to pull 'kdl' from each row
Answer pls

Thanks in advance

[menta]

You can try with index('kdl') if this values is >0 the string is in the record

[vinieme12]

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 

[Bharathi09]

How to pull 10th to 12th letters in any row?
examole: empid-ssdkdl223
like 'kdl' I need to pull 10th to 12th characters from data

Please let me know
Thanks in advance

[Bharathi09]

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??

[vinieme12]

Use mid() function

Mid(empname,10,12)

[Vegar]

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)].