needed help

[siddhardh19]

Need to work on an excel sheet with 1300 names on it. I want to write every name first name on the top of second name in the same cell. for ex : "John Wick"  as "John

                                                                                                 Wick"  in the same cell.

I can do it by using "alt + enter", but it is very difficult to do the same for 1300 names. please help me solve it.

please find the attachment for example. 

[#2 · Accepted solution]

@siddhardh19 

If you have O365, you can use this (shown on row 3):

=LET(d,A3,pos,FIND(", ",d,1),LEFT(d,pos-1)&CHAR(10)&MID(d,pos+2,LEN(d)))

If you don't, you can use this:

=LEFT(A1,FIND(", ",A1,1)-1)&CHAR(10)&MID(A1,FIND(", ",A1,1)+2,LEN(A1))

[#3]

Thanks for the rescue. will try the fix on bulk files. Thanks again

[#4]

Hello,

Both the formulas are not working .please find the attachment and help me. Thanks .

@flexyourdata

 

[#5]

Why do you want to do this? Would it be better if each word were on a different row?

[#6]

@siddhardh19 

Try this:

=SUBSTITUTE(SUBSTITUTE(A2,",","")," ",CHAR(10))