Formatting cell with multiple line break RRS feed

  • Question

  • Dear all, 

    I have a simple problem. I have one cell where with multiple cost options inserted with Alt + Enter as multiple lines. 

     (like euro 3.000 in one line, euro 400 inline below etc). I was trying to  format them the same way as I do for single entries (i.e euro sign aligned to the left and number aligned to the right), but failed on that. Any help would be appreciate. 

    best regards, 


    Monday, November 4, 2019 1:49 PM

All replies

  • Do you really need multiple lines in one cell? It would be easier if you could store each amount in a separate cell.

    For if a cell contains line breaks, its value is text, not a number, so applying a currency or accounting number format has no effect.

    Regards, Hans Vogelaar (

    Monday, November 4, 2019 2:15 PM
  • Thank you very much. I have not thought of that. Basically I could split the cells into cells. I wanted to avoid it as the data I got are already saved with line breaks. 

    But if I had in a cell string of surnames: Jan Eve Jane Michael  (with a  line break), would it be possible to make a custom formatting in a way that to each surname family name is added (Brown) and the final result is in formatted with each set surname plus name in separate line:

    Jan Brown

    Eve Brown

    Jane Brown

    Michael Brown

    Thank you once again . 

    best regards, 


    Monday, November 4, 2019 3:35 PM
  • Once again, this is not possible with number formatting.

    It could probably be done with a formula using worksheet functions, but it would be complicated.

    Here is a solution using a custom VBA function:

    Function CombineNames(fn, ln) As String
        Dim a
        Dim i As Long
        a = Split(fn, vbLf)
        For i = 0 To UBound(a)
            a(i) = a(i) & " " & ln
        Next i
        CombineNames = Join(a, vbLf)
    End Function

    Let's say the first names are in cell G1 and the family name in cell H1. Enter the following formula in (for example) I1:


    and turn on Wrap Text for the cell with the formula.

    The formula can be filled down if there are more names in the rows below.

    Save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open the workbook.

    Regards, Hans Vogelaar (

    Monday, November 4, 2019 6:13 PM
  • Thank you very much, it worked really nice and is really elegant.

    I am sorry for replying late-  I missed the  message. 

    with my best regards, 


    Monday, November 11, 2019 12:37 PM