split text from one cell

**duda** · 01-17-2011, 09:20 AM

Now, my problem is that I have to fix a file that is wrong filled in.
The format is for example :
Prof.dr.ir.A.G.C van Doornenburg ; and I need to split the cell into title (Prof.dr.ir.), first name/initials (A.G.C), suffix(van) and the lastname (Doornenburg).

Please help me,

Duda

**arthurbr** · 01-17-2011, 09:33 AM

Hi and welcome to the board

Do all the strings have the same format ?
Maybe add a small sample sheet with some other examples

**duda** · 01-17-2011, 09:54 AM

The problem is that is not the same format; it looks something like this

Ing. P.H.L.J.P. Miller
Prof. dr. ir. A.E. Smith
Mr. P.F.M. van der Mier Broek
Drs. J.P. Bartok

Thanks for helping me,

Duda

**NBVC** · 01-17-2011, 11:25 AM

Assuming your list starts in A2, try these formulas in B2:E2, respectively:

Please Login or Register  to view this content.

confirm this with CTRL+SHIFT+ENTER not just ENTER (you should see { } brackets appear)

Please Login or Register  to view this content.

confirmed with just ENTER as normal

Please Login or Register  to view this content.

confirm this with CTRL+SHIFT+ENTER not just ENTER (you should see { } brackets appear)

Please Login or Register  to view this content.

confirmed with just ENTER as normal

then copy all down.

**duda** · 01-17-2011, 04:24 PM

Thank you! It works great. I have almost give up the hope and try to solve it manually.

Duda

**NBVC** · 01-17-2011, 04:40 PM

You are welcome,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save