New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Concatenate numbers and text in the same cell - using formatting

Free Excel Tips and Tutorials

Concatenate numbers and text in the same cell - using formatting

Postby Sisyphus » Tue Jan 31, 2012 9:47 pm

If you want cell A1 to show "Entrance fee $15.00" you may enter exactly that text. But when you want to retrieve the dollar amount, i.e. 15, for the purpose of further calculations you can't because the cell has assumed the value of text. However, you can create this custom format for the cell:
Code: Select all
"Entrance fee "$0.00

Now all you enter in A1 is the number, for example 15, and Excel uses the text and number format for the purpose of display while =A1 in any other cell returns the number 15.
The same method is also useful for recurring texts like "Invoice No."
Entering a simple 1 in a cell formatted as
Code: Select all
"Invoice No. "000000
would display "Invoice No. 000001"
To set the number format select the cell or range of cells you want to format,
Press Format / Cells on the Toolbar or Right-click and select 'Format Cells'
On the Number tab select Custom, and
Write the formatting mask in the Type field.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 20 guests

cron