how to use sub field function for multiple delimiters?

[#1]

Hi all,

I need to get Name as a separate field and I'd as a separate field from Name field by using Sub filed Function.

i have a Data like Below:

Name	College	Department
10:Muthukumar	Sathyabama	EIE
11:Ismail	AALi Mohamed Shale	Civil
12,Prakash	Hindustan	ECE
Imran-13	SRM	Civil
14,Essa	Cresent	B:Arch
15:Anurag	Sathyabama	Mech
16,Roger	Sathyabama	EIE
Pratap-17	Sathyabama	EIE
18,Kevin	Sathyabama	EIE
19,palani	Singapore university	MBA

from above table, I need to get Name and Id Separately like below table, by using Subfield Function

Id	Name	College	Department
10	Muthukumar	Sathyabama	EIE
11	Ismail	AALi Mohamed Shale	Civil
12	Prakash	Hindustan	ECE
13	Imran	SRM	Civil
14	Essa	Cresent	B:Arch
15	Anurag	Sathyabama	Mech
16	Roger	Sathyabama	EIE
17	Pratap	Sathyabama	EIE
18	Kevin	Sathyabama	EIE
19	palani	Singapore university	MBA

‌ vengadeshpalani‌ yoganantha321‌ sivabose‌

I need to implement it in the script. please make use of the below Script

My Script:

LOAD

Subfield(Name,':',1) as Id,

SubField(Name,':',2) as Name,

     College,

     Department

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,

Muthu

[tresesco · Accepted solution]

Try like:

Load 

       Keepchar(Name, '0123456789') as Id,

       Capitalize(Keepchar(lower(Name), 'abcdefghijklmnopqrstuvwxyz')) as Name

[yoganantha321]

Hi muthu,

it working great as per the Tresesco code

Kindly mark it as correct answer so that it will be useful for others

[#4]

Thanks for your response tresesco‌ and yoganantha321 it is working fine.

but if I use it in another scenario it is taking all the numbers present in the Name field

if i have a data Like this it is not working for it

for below-bolded data it is taking all the numbers but i need only 13,14 is there any way or logic to get it

Name	College	Department
10:Muthukumar	Sathyabama	EIE
11:Ismail	AALi Mohamed Shale	Civil
12,Prakash	Hindustan	ECE
22-Imran-13	SRM	Civil
14,Essa-15	Cresent	B:Arch
15:Anurag,18	Sathyabama	Mech
16,Roger	Sathyabama	EIE
Pratap-17	Sathyabama	EIE
18,Kevin	Sathyabama	EIE
19,palani	Singapore university	MBA

output for above data:

Thanks,

Muthu