Need help in extracting substring from string

[juixyy]

For example a column contains:

Change to Unlock mode: SW1Cleaner2 SW1Cleaner2, Card Version: 2
Change to Normal mode: 002858 ONG AI LING, Card Version: 1
Invalid User: 1403754C VERNETTA LIN YONG XUAN, Card Version: 1
Change to Normal mode: SW0001 Master Card V7, Card Version: 1
Invalid Attempt, Time Zone Error: 005255 LEE KIM YONG, Card Version: 1
Legal Access (Door Open Fail): 003837 FUA YING HUAY, Card Version: 1

From the column above, I want to create another column that only have their names like:
SW1Cleaner2
ONG AI LING
VERNETTA LIN YONG XUAN
Master Card V7
LEE KIM YONG
FUA YING HUAY

Can anyone help me with this? Thanks!

[JohnTopley]

Try

=MID(SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2),FIND("/",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))+1,FIND(",",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))-FIND("/",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))-1)

[juixyy]

Try

=MID(SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2),FIND("/",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))+1,FIND(",",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))-FIND("/",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2))-1)

Thanks JohnTopley! You helped me big time here.

Do you mind telling my the explanation behind the formula? I would like to learn more about it.

[JohnTopley]

Change to Unlock mode: SW1Cleaner2 SW1Cleaner2, Card Version: 2

SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2), finds the second space after the first ":" and substitutes it with "/"so we get

Change to Unlock mode: SW1Cleaner2/SW1Cleaner2, Card Version: 2

FIND(",",SUBSTITUTE(MID(A1,FIND(":",A1),255)," ","/",2)

This finds the position of the "," starting at the position of the "/" (the FIND formula)

It then subtracts the Postion of the "/" from the postion of the "," to get the required text

Change to Unlock mode: SW1Cleaner2/SW1Cleaner2, Card Version: 2

Hope this helps.

[XOR LX]

Hi.

Please re-read the forum rules, in particular that one related to "cross-posting".

Regards