Manipulating text string via clean, proper, right

[KoE]

Guys,

Pardon my ignorance but these got me stumped. I've received an excel file which contains name and personal id both keyed into the same cell separated by "enter" key.

I've used clean, proper and right functions separately to get to this sample output:

Excel Workbook
	B	C	D	E
2	AZURA BINTI GHAZALI841228-03-5952	AZURA BINTI GHAZALI841228-03-5952	Azura Binti Ghazali841228-03-5952	841228-03-5952
3	TAN SEW PING850909-07-5014	TAN SEW PING850909-07-5014	Tan Sew Ping850909-07-5014	850909-07-5014
Sheet

If you guys could help, could the 3 functions (clean, proper, right) be combined in a shot plus any additional function(s) so that the output will just be name and personal id in 2 separate columns?

thanks!

 

[EB08]

I could be wrong, but it seems like they were probably seperated by using alt+enter: if this is the case, to take the name, try =PROPER(TRIM(LEFT(B2,FIND(CHAR(10),B2)-1))) to get the name; And =TRIM(RIGHT(B2,LEN(B2)-FIND(CHAR(10),B2))) to get the number.


***trim() is probably not necessary in this case, I just had it in there when I wrote the formula out of habit and decided to leave it there when pasting in.

 

[Blade Hunter]

Name: =TRIM(PROPER(LEFT(A1,LEN(A1)-15)))
ID: =RIGHT(A1,14)

Cheers

Dan

 

[KoE]

Thanks a lot guys! The original cells are a bit more messy than I originally thought of (multiple carriage returns and whatnots) but the formula you guys gave help giving a good kick-start