Combine 2 cells into 1

[Rothrock]

I've got a decent sized list - about 2,000 rows - with a low and
high columns. For space reasons, I would like to combine the two
columns and have their contents show in one column with two lines of
text. These are dollar amounts ranging from $100 to $1,000,000.

I put this in the formula bar:

=Cell1&"-"&Cell2

(With Cell 1 an 2 referring to the correct columns.) But it combined
the values, but didn't keep the currency formatting. Is there a way to
do this without losing that? Thank you.

 

[Bob Greenblatt]

I've got a decent sized list - about 2,000 rows - with a low and
high columns. For space reasons, I would like to combine the two
columns and have their contents show in one column with two lines of
text. These are dollar amounts ranging from $100 to $1,000,000.

I put this in the formula bar:

=Cell1&"-"&Cell2

(With Cell 1 an 2 referring to the correct columns.) But it combined
the values, but didn't keep the currency formatting. Is there a way to
do this without losing that? Thank you.

Every time you refer to a cell in a formula, the value of the cell is used.
In your case, you want the displayed text, not the value. So, the best way
is with a formula similar to yours that reconstructs the text. I am using a
simple currency format here, but you can modify it to any of the standard
ones or even a custom format of your own.

Try: =text(cell1,"$0.00")&"-"&text(cell2,"$0.00")

 

[Rothrock]

Thank you so much that is exactly what I wanted.

One more question if you can. How can I include a newline/return at the
end of the dash?

 

[JE McGimpsey]

One more question if you can. How can I include a newline/return at the
end of the dash?

=text(cell1,"$0.00")&"-"&text(cell2,"$0.00")

One way:

=TEXT(cell1, "$0.00") & "-" & CHAR(13) & TEXT(cell2, "$0.00")

Make sure wrap text is turned on (check the box in the Alignment tab of
the Formatting palette, or choose Format/Cells/Alignment and check the
appropriate box).

 

[Rothrock]

Of course good ol' character 13! Thanks.