I have an Excel problem, and I hope everyone can help me with it. Thank you.
So I have a column as below full of phone numbers and full names:
*People had option to put their full names or just their phone numbers when joining a Zoom meeting.
| Full_Name | Department |
|---|---|
| 14044445555 | A |
| Bob Max | A |
| 16788889999 | B |
| Sarah Harrison | C |
How can I separate them into a table as below:
| First_Name | Last_Name | Phone_Number | Department |
|---|---|---|---|
| 14044445555 | A | ||
| Bob | Max | A | |
| 16788889999 | B | ||
| Sarah | Harrison | C |
I have tried to use "Text to Columns", but it only worked with people who put their full names.
Thank you very much.
CodePudding user response：
If you want to do it by formulas only then add three columns before department. As per my below screenshot use formulas like
B2=IF(ISNUMBER(A2),"",TRIM(LEFT(SUBSTITUTE(A2," ", REPT(" ",100)),100)))
C2=IF(ISNUMBER(A2),"",TRIM(RIGHT(SUBSTITUTE(A2," ", REPT(" ",100)),100)))
D2=IF(ISNUMBER(A2),A2,"")
Then drag down and across right as needed.
