# Excel: Combine text and formulas in a cell

**Whenever I analyze data in Excel, **I format the spreadsheet to make it easier to read. A little attention to fonts, boxes and shading can help people understand the key data faster.

One way to give yourself some flexibility with formatting is to combine text and the results of a formula in a single cell. Just use the “&” operator to concatenate the text and the formula.

Consider this formula:

`="Quantity: "&SUM(A1:A20)`

Enter it into a cell, press enter and (assuming you have numeric values in cells A1 through A20) it will present this result in a single cell:

`Quantity: 23`

That kind of output’s pretty handy when you want to create a worksheet in your spreadsheet that aggregates data from other sheets while keeping the formatting simple.

Now that I know concatentate is a real word, I will not bother playing editor. Other than that, I like the techie color and feel. It’s good all around.

But how do you add a format to the number returned? For example, if instead of 23 my total was 5342.16, how can I add a dollar sign and comma to make it show $5,342.16?

Janna,

Good question. Adding the dollar sign is pretty easy:

Not sure how to format the number, though, to add a comma.

How to I create :

Date: 9/4/2013

using text “date” and “=TODAY()”

Thanks!

George,

Try this:

Credit to: http://stackoverflow.com/questions/6336518/concatenating-date-with-a-string-in-excel

That’s what I had already tried and it just gives me #NAME?

I managed to find =CONCATENATE(“DATE: “, TEXT(TODAY(),”dd-mmm-yyyy”)) works.

Thanks.

Janna,

You could use:

This will convert your number into text and format it to look the way you want. More about the TEXT function can be found in the help.

Janna,

Sorry that formula should have read:

Anthony,

I’m trying to put a due date in a cell based on a date in another cell minus 28 days.

I tried =”Due: “&(K234-28) where K234 is a date of 11/3/2013. I keep getting Due: 41547 as the result. It seems like it’s a formatting issue???

Thank you!!!

Kim,

Some of the other comments in this thread hint at the answer:

I have a spread sheet with over 1000 client names. One cell has the full name and one cell will say male or female. Is it possible for a third column to combine this information and say Mr. John Doe? so something like this

Mr. John Doe John Doe Male

Because there is so many however, I’m trying to have the first cell populated via formula. If this is not possible how do I manually put Mr. and then a formula to pull in the full name?

thanks!

Gavin,

You can concatenate cells using the & character in a formula. You can also explore using the IF function to read the Male/Female cell and populate another cell with Mr. or Ms. based on the value.

Hello Anthony!

Thank you for a great post. How do I:

*Choose numbers of decimals

*Add percentage sign

*Enters text also behind the formula.

FOR EXAMPLE:

If the formula gives the answer 62,408543957.

HOW DO I GET IT TO LOOK LIKE THIS:

Text here 62,41% and text here.

Johannes.

Johannes

Use the

function

Hi Anthony,

is it possible to use excel to provide data within a sentence in an excel sheet?

I have the following statement:

The Promotors and existing Investors have invested £11,2 million (€13,1 million) in the project already and are seeking to raise an additional £16,0 million (€18,7 million).

I am trying to link the monetary values to cells on another page of the worksheet so that as I change parameters, the investment values change. At the moment I have to amend the values manually. Thanks in anticipation.

Paul.

thank you so much.. 🙂

Hello,

I am trying to use a drop down list as input into a formula. For example, the drop down is in A1 of the current selected tab and it contains the names of tabs in my workbook. For now the drop down in A1 is set to Phase 0. What I want is for a formula to read the value of tab Phase 0’s cell A1. In cell A2 I have =’A1′!A1. Cell A2 throws a #ref error. If I type ‘Phase 0’!A1, the value I want from tab Phase 0 cell A1 comes up of course. What am I missing?

Need your help please

Bobby

I just solved this with the indirect() function in excel…

Alternative (IMHO easier/cleaner) way):

Right-click/Format Cells/Number –> select ‘Custom Category’

In the ‘Type:’ field, enter: “Quantity: “0

Click ‘OK’

Now in that cell, write ’23’.

Cell will display: “Quantity: 23”

Now copy/paste that format to the desired cells, or just select that format from the drop down menu next time.

Matt, thanks for solving Janna’s problem. That formula works perfectly.

Cheers.

thanks man.

dEAR ANTHONY..

I want to put in a cell like this:

Avg Fabrication MH per ton = 100 MH x 7000 Ton.

100 and 7000 are situated in different cells

So I tried so far: =”AVG FABRICATION MANHOURS PER TON = “&ROUND(‘[E0418 SUMMARY.xlsx]Super-Structure’!$I$41,1)

So it displays : AVG FABRICATION MANHOURS PER TON = 100

but how to put the rest..

Please help

Is it possible to join to lots of texts for example,

=A2 & “Panels @” B2 & “m”?????

Your help correcting this formula would be much appreciated.

Sincerely

Brad

i want to add a number in a cell which contains a text. For Example, a cell contains “P/1” then how to get “P/2” in next cell and so on.

Thanks in advance

Can u please rectified the said formula.

“After TDS(10%) actual Amount received “&(ROUND(AP16,0),(AK16-(AK16*10)%))

Regards,

Jack V.

talha

01/21/2015 at 3:30 pm

i want to add a number in a cell which contains a text. For Example, a cell contains “P/1″ then how to get “P/2″ in next cell and so on.

Thanks in advance

——————-

I have had the same problem before, and used a relative reference to the row number to “add a number” to the next cell.

If your first row has “P/1”, your next cell can contain the formula:

This will produce “P/2” if the formula is place somewhere in the second row, “P/3” if placed somewhere in the third row, etc. This formula can then be filled down to add a number to each subsequent row.

You can also add numbers within the formula to start at a desired number rather than the actual row number. For instance, =”P/”$row()+999 will result in “P/1000” if placed in the first row, “P/1001” if placed in the second row, etc. Again, this formula can then be filled down to add a number to each subsequent row.

Hope this helps.

Is there any way to have a formula extrapolate only the numerical value from a cell? I have a large spreadsheet with electrical numbers that include a watts (W) identification, is there some way to have a sum formula still tabulate the cells and simply ignore the letter?

Thanks! I’ve been fighting this for weeks

Thanks.

whats wrong about this formula ?

i input:

to get this text:

WEEKLY AVAILABILITY REPORT from 15 July to 21 July

provided that H7= 15/7/2015 and H13= 21/7/2015

H13 value is provided by another formula and H7 is the variable that i input manually

I have the following in a merged and centered cell (like a heading). Can I create a link to a separate tab in the workbook to provide the date (ie, August 31, 2015, in this case)? I have many places where dates need to be updated monthly and quarterly and it is manual and time consuming.

Combined Consolidated Rolling 12 Month Statement of Operations

For the Twelve Months Ending August 31, 2015 – LENDER VERSION

(Unaudited)

Janet,

Sure. You could set up a worksheet (tab) in your spreadsheet. Call it date or something similar, and enter the date in one of the cells. Then you could refer to that worksheet and cell from all the other locations in the file that need that date. Update the date once and it spreads across the whole thing. Handy!

The way to access another sheet is referencing it by name. e.g.:

To combine dates into text requires a little extra formatting:

Yes, I have those references working. I’m having trouble with the titles that are currently in a cell with multiple lines wrapped and centered. I was wondering if I had to break into multiple rows to get a formula to work. I am using a separate “dates” tab within the workbook. Thanks for your help!

LAUREN……Use the FIND & REPLACE function [Cntrl+H] to get rid of the “W”in your spreadsheet then you can use SUM function to sum the values

LAUREN……To be more explicit, select the range of cells you want to remove the “W” from the cell. Press Cntrl+H to bring up the FIND&REPLACE function. Put “W” (no quotes) in the FIND field and leave the REPLACE field blank. This will remove the “W” from all fields you have select

hi,

how to do solve this?

Data what I want it to be

excel #2003 myexcel#2003

#add in “my” then replace ” #” to “#”

pls help thanks

Can anyone tell me how to use a number in a cell as part of a formula. I have two cells that different numbers will be input to. One number references the sheet, the other a cell number in that sheet. example: cell B5 has the number 1 in it. cell C5 has 101 in it. I tried =’C5′!&A’B5′ since I want to reference cell A1 in sheet 101, but I know I’m way off track. Any help?

This generated the correct cell but doesn’t actually reference it.

=C5&”!A”&B5

Dewey, you were on the right track……now, in another cell use the INDIRECT formula to convert the text value to a recognizable cell reference. If the output of your first formula was in cell D5, then in another cell, let’s say E5, write =INDIRECT(D5)

Thanks! The INDIRECT function worked perfect.

How would I state:

There are 20 widgets in the box.

Cell B5 has the quantity of widgets.

Something like:

=”There are “&=($B$5)”widgets in the box.”

KB,

You’re close. Try:

Hi Anthony.

I am trying to make a ratio with actual raw data as a fraction of 2 separate cells without the fraction being reduced.

Example: Cell G7 contains a formula yielding the value 8. And cell F7 contains a formula yielding the value 16. In H7, I want to put these 2 data points in a fraction (or a ratio would also work) of 8/16 without reducing that fraction. Any way to do this?

CL,

Sure:

answer:

credit to:

Bernie Deitrick, Excel MVP 2000-2010

You have to use helper cells.

Enter the number correct in, say, cell E2. In F2, enter the number of questions, and in G2, enter the formula =E2/F2. In H2, enter the formula = E2 & “/” & F2

Use G2 for calculations, and H2 for display.

Bernie

http://answers.microsoft.com/en-us/office/forum/office_2003-excel/display-fractions-wo-reducing/0e27058a-30e3-4586-8983-3a6bc656e42b?auth=1

just saw your reply too. thanks anthony!

Is there a clean way to have the choice in the same cell to either use % or $ and have it formulate in a later cell? Could a drop down be used? Also must function within online OneDrive.

Trying to sum a range with text and numbers. Cells contain 8,W. How can add just the numeric part of the cell?

Thanks

Johnny,

Suggest you use the “Text to Columns” command to separate the cell contents into two cells — one for the number and the other for text.

Hi all,

I would like to create a label or text name for a cell that has a value i.e.

1250 = ItalianCotton

2500 = ItalianCanvas

355 = ItalianLinen

So that when I select from my drop-down list, the name displays that the cell is assigned however is able to allow calculation of the value of the cell for my formula:

=SUM(B34*C34)*DROPDOWNSELECTIONVALUE

=SUM(B34*C34)*N34