Data within 1 cell to be split into multiple cells.

[Arjun Dsouza]

Dear All,

I have the following data in each cell with the "/" which separates names. (The list goes on)

BAUMANN/INGRID MARGRET MRS
AHRENS/BERND MR
WEIXELBAUM/HELMUT MR
WEIXELBAUM/BEATE MRS
DUIJM/HUBERT MR
DUIJM/UTE MRS

I want the data to be split into different cells as
CellB1 BAUMANN
CellC1 INGRID
CellD1 MARGRET
CellE1 MRS

CellB2 AHRENS
CellC2 BERND
CellD2 MR

Attached is the excel file containing all the names.

Please guide me how this can be done. Apologies if I have made it a bit confusing.

Thank you and appreciate your help in advance.

Kind regards
Arjun

 

[Asheesh]

hi

Put the below formula in B1 and drag it right and down..

TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&$A1,"/"," ")," ",REPT(" ",99)),COLUMN(A$1)*99,99))

Attached for ref.

 

[Arjun Dsouza]

Awesome!!
Thank you so much Asheesh.
Have a great day ahead.

 

[pecoflyer]

No fancy formulas needed - data Text to columns use / and space as delimiter - Done