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 functions

Free Excel Tips and Tutorials

Concatenate numbers and text in the same cell - using functions

Postby Sisyphus » Tue Jan 31, 2012 10:01 pm

Say, you have a list of names in column A and a list of each person's income in column B, and in column C you want to create a sentence like "John Smith earns $3500.00 per month". You can use this formula in column C:
Code: Select all
=CONCATENATE($A8," earns ",$B8," per month")

However, the result would be
John Smith earns 3500 per month
.
In order to format the number in your text string you can nest the TEXT() function within the CONCATENATE() function like this:
Code: Select all
=CONCATENATE($A8," earns ",TEXT($B8, "$#,##0.00")," per month")
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 21 guests