Split cell to seperate ID number from Name

Attached is a sample of a book we create. Currently in Column B is the last name and ID number, a comma, and the first name. I would like to split this so in column C is the ID number, and in column D is the Last name, a comma, and the first name. I know i can do it manually, but I am hoping there is a formula that could do this for us. The length of the ID number is not always going to be the same, but the format of column B should be a constant.

Any thoughts?

Have you tried TextToColumns? I had to do it twice; fixed width for the first layer and delimited for the second layer.

Try in C2:

Please Login or Register  to view this content.

D2:

Please Login or Register  to view this content.

To get NS

=MID(TRIM(B2),FIND(" ",TRIM(B2))+1,6)+0

for Name

=SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(B2),C2,""))," ,",",")

Here is my way
For NS

Formula:

Please Login or Register  to view this content.

Name

Formula:

Please Login or Register  to view this content.

Check the attached file:-

Thank you all! Problem solved!!