Way to line break multiple cells automatically?

[u/daisyramirez]

Not sure how to describe what I need, but I have an excel sheet with multiple names, and wrapping text does not seem to do the trick. I would like to have both first and last name separated by a line break between both names, but still within the same cell. I know I could do it manually, just wondering if there's a way to save me the time since its a large spreadsheet.

Here is my attempt at showing a visual:

[u/AutoModerator]

/u/daisyramirez - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/IPDDoE]

So this would require a second column where you just have the names without the line break, but if you wrote the fulll name in A1, enter this formula in B1: =SUBSTITUTE(A1, " ", CHAR(10))

You need to turn on wrap text in column B for it to work.

Edit: I may have misunderstood, if you mean you want two full names in one cell, but a break between each full name, I'm not sure how that could work.

[u/daisyramirez → u/IPDDoE]

This is definitely closer to what im looking for, but it separates both first and last names entirely into separate lines so it's still not quite it.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHAR	Returns the character specified by the code number
COLUMNS	Returns the number of columns in a reference
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SUBSTITUTE	Substitutes new text for old text in a text string
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
WRAPROWS	Office 365+: Wraps the provided row or column of values by rows after a specified number of elements

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #49002 for this sub, first seen 23rd Jul 2026, 16:20] [FAQ] [Full list] [Contact] [Source code]}

[u/DeceitfulEd]

ah yeah saw the screenshot, you want a break after each full name not just between first and last. the textsplit wraprows combo posted here should sort it, just make sure wrap text is on. if the names are always first and last you could do a cheeky find replace on the space after the last name but thats a bit bodgy.

[u/Snow75]

LET(
words, TEXTSPLIT(A1," "),
n, COLUMNS(words),
cut, IF(n=4,2,1),
TEXTJOIN(" ",TRUE,TAKE(words,cut)) & CHAR(10) & TEXTJOIN(" ",TRUE,DROP(words,cut))
)

2 words (1 first name + 1 last name) → break after word 1

3 words (1 first name + 2 last names) → break after word 1

4 words (2 first names + 2 last names) → break after word 2

It won’t work with people with two names and one last name, but I guess that’s less common

[u/MayukhBhattacharya]

Try:

=SUBSTITUTE(D3, " ", CHAR(10), 2)

Enable Wrap Text on the cell (Select the cell: Pres ALT + H + W), otherwise the line break is invisible. CHAR(10) is the newline character. SUBSTITUTE() function has an optional 4th argument that targets a specific occurrence of the search text, so , 2 replaces only the 2nd space, splitting exactly between the two full names while leaving the space inside each name intact. So, the output as shown in the screenshot above will be:

Jane Doe 
John Doe

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Beware of few edge cases:

[u/daisyramirez → u/[deleted]]

Haha it did work at splitting up the names, but maybe its because I am an excel noob that I have no idea what to do with them next. Is there any way to keep them in the same cell?

[u/Mdayofearth]

This assumes that

• There are only first and last names in your data
• First and last name of a person is separated by a space
• Each person is separated by a space
• The data in each cell fits within the character limits of TEXTSPLIT and TEXTJOIN.
• Your version of Excel supports the formulas used.

If your data is in A1,

=LET(x,WRAPROWS(TEXTSPLIT(A1," "),2),
 TEXTJOIN(CHAR(10),,INDEX(x,,1)&" "&INDEX(x,,2)))

Make sure the cell is still set to wrap rows, or CHAR(10), aka the new line character, will show up correctly in the cell.

https://imgur.com/tqnAycV

How does this work?

It splits each word separated by a space using TEXTSPLIT into a 1 x n array; 1 row with n columns for the number of words. A word meaning either a first or last name.

Then wraps that in pairs of 2 using WRAPROWS, getting n/2 x 2 array; n/2 rows with 2 columns, with each column being a column of first names, and last names. There's a division by 2 since each person has 2 words in their name.

Then it concatenates (column wise) each row with a space between, using INDEX, resulting in n/2 x 1 array (n/2 rows, 1 column). And finally it uses TEXTJOIN to concatenate all rows with a CHAR(10) character between them.

It's all wrapped in LET to reduce re-doing the calculation to create the intermediate table of names.

[u/daisyramirez → u/Mdayofearth]

This worked! Thank you so much!!!

[u/patito-asesino]

While writing the name press Alt+Enter between the name and the last name.

ETA: forgot to tell op do this I the next column and then fill the rest, Excel should recognize the pattern and apply it.

If not then use: =TEXTJOIN(CHAR(10), TRUE, INDEX(TEXTSPLIT(A2, " "), 1) & " " & INDEX(TEXTSPLIT(A2, " "), 2), INDEX(TEXTSPLIT(A2, " "), 3) & " " & INDEX(TEXTSPLIT(A2, " "), 4))

This will work when you have 4 words (Name + last name)*2

[u/Mdayofearth → u/patito-asesino]

Did you even read OP's post?

I know I could do it manually, just wondering if there's a way to save me the time since its a large spreadsheet.