SOLVED

needed help

New Contributor

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. pumka.jpg

 

5 Replies
best response confirmed by siddhardh19 (New Contributor)
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))

 

flexyourdata_0-1653241205200.png

 

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

Hello,

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

@flexyourdata

 

pic 3.jpg 

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

@siddhardh19 

 

Try this:

 

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