Forum Discussion

pspisar's avatar
pspisar
Copper Contributor
Jun 23, 2025

Help with Excel 'Format copy' does not include 'new line (Alt Enter) within a cell.

Hello, Note that Excel 'format copy' does not copy over the 'new line (Alt Enter)' within a cell. Is there another way to copy format of a cell that includes the 'new line' formatting?
excel
pspisar's avatar
pspisar
Copper Contributor
Jun 23, 2025

With Word Wrap ‘On’ within a cell I still need to force a ‘new line’ by using keys: [Alt] [Enter] (I consider this part of formatting, since it impacts how the text is ‘displayed’).

How to keep this same display when copying from cell to cell?

m_tarler's avatar
m_tarler
Bronze Contributor
Jun 25, 2025

As I said, the actual formatting (word wrap on) does get 'painted'.  If you copy a cell that has a 'new line' character, that 'new line' character will also get copied over.  the format painter can NOT arbitrarily insert a new line character into another cell.  it would have no idea where to insert it.  YOU can create a formula using SUBSTITUTE or REPLACE or other text functions to 'insert' a CHR(10) character according to what ever 'rule' you need.

  • pspisar's avatar
    pspisar
    Copper Contributor
    Jun 25, 2025

    Left side is the desired 'formatting'.

    Right side is result when I use 'format painter'.

    It seems to me that 'format painter' should be able to determine location of 'new line' (ie: CHR(10)) and place in the same locations, right?

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Jun 26, 2025

      no it will not insert text and chr(10) is a text character.  Just looking at it I don't necessarily know where either.  For exmple:

      Will it always break before the "EQ", "EVB" and the "FIB"?  if so the following formula will insert the CHAR(10) character:

      =REGEXREPLACE(A1,"_([E,F])","_"&CHAR(10)&"$1")

      Is it always after the 1st, 3rd and 6th underscore? Then maybe this:

      =REDUCE(A14,{1,3,6},LAMBDA(p,q,SUBSTITUTE(p,"_","_"&CHAR(10),q)))

       

      and if you want an arbitrary break after any "_" if the line exceeds some number of characters try this (number of characters set to 18 on line 4):

      =LET(
    in, A14,
    _LF, {"_"},
    _LL, 18,
    inArray, REDUCE(in, _LF, LAMBDA(p, q, TEXTSPLIT(p, q) & q)),
    TEXTJOIN(
        CHAR(10),
        1,
        REDUCE(
            {""; ""},
            inArray,
            LAMBDA(p, q,
                LET(
                    pp, DROP(p, -1),
                    pn, TAKE(p, -1),
                    IF(LEN(pn & q) < _LL, VSTACK(pp, "", pn & q), VSTACK(p, q))
                )
            )
        )
    )
)

      but as you can see there are lots of possible ways to 'interpret' where you want/mean for a line feed to be put.  If any of the above are correct you can use them in an adjacent column and either hide the original or copy and 'paste values only' to get your output (don't forget to make sure 'wrap text' is turned on)

       

      if you find this your best answer feel free to mark as solution and if you appreciate my responses likes are always welcome.

Resources