# 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.

**Learn data analysis with SQL!**

If you like what you read here, check out my book Practical SQL: A Beginner’s Guide to Storytelling with Data from No Starch Press.

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.

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:

Hello!! I’m working on a file where I will have positive and negative numbers in columns A and B like is shown below, with those columns being calculated from various cells. I want to add an X in front of the column A values and a Y to the column B values as I want to then copy and paste the values into another file (writing simple G code for a CNC mill).

If I select cell A1 and choose Format Cell/Number/Custom and use “X”0.000 and then “Y”0.000 for cell B1, then they will appear as X3.150 and -Y1.500. The issue is, that I need to have the negative value appear as Y-1.500 instead of -Y1.500. I ended up copying the values in colA and colB to create colC and colD, and then for colD set it to =”Y”&A1 or =CONCATENATE(“Y”,I3), and that fixes the issue so the – sign will be after the Y (which is what I want), but it just lists the number to two digits (drops off the zeroes) instead of the three digits that I’d like. If I get by without needing the extra columns (like colC and colD above),then that would be great, but I need to have the negative values appear as say X-3.452 and Y-1.500 instead of having the negative sign go before the X and Y letters. Thanks!!!! – Jim

James,

What if you cast the number to text before concatenating? i.e.,

Anthony – I got it to work, but if I did as you mentioned using =”Y”&TEXT(B1,”0.000″), then for a value of -1.500 is shows #Name? with format set to general. Anyway, what I ended up doing, was to set a column with the cell set to =TEXT(B1,”0.000″) to switch it to text but to keep the number displayed to three digits, and then added another column with the cell set to =”Y”&B1. I then hid the columns that had the =TEXT(B1,”0.000″) so they would be present but wouldn’t clutter what was displayed. It worked great, so for a Y value of -1.500, the last column would read Y-1.500 with that Y-1.500 being text that I could then copy to a notepad file. It was an interesting project for me as I have always used relative cell references, but I just learned about using absolute references, so that should help me in the future as well. Thanks, and great blog!! – Jim

James,

Glad you got it sorted out!

Anthony

Hi Anthony,

Please help me sort out my things. Since I work in warehouse, I extract daily stock on hand of items from system and paste them under today()column in excel sheet1.

item 27-May-16(today)

imported apple 20 ctns

In sheet2, I have table with dates of 7 days.

27-May-16 ………….1-Jun-16

apple 20 ……

Based on date while pasting stock on hand in sheet1, I want the SOH to appear under respective date of sheet2.I’ve been long seeking to sort this out. You are requested to help me!

Many thanks,

dennis

Dennis, if I understand you correctly, you have a SINGLE worksheet “TODAY” that you use for the daily SOH tally that you want to feed to another worksheet, “WEEKLY” which would accumulate totals for the week. Getting it to feed to “WEEKLY” is no problem, but because you are using formulas and variables, you cannot retain the data from Monday when you input data on Tuesday. There are a few workarounds however, that could solve your dilemma. Probably the easiest way would be to add additional daily input worksheets (7-total)and then have them feed to the “WEEKLY” accumulated total worksheet based upon the day of the week. Other options include converting the results in the WEEKLY worksheet from formulas to fixed values at THE END OF EACH DAY (copy the results, paste special, paste as values). The other option is to use a database program like MS-ACCESS.

Thank you so much! I needed a formula to add text in front of and after a formula. The formula populates the URL’s from one column to another, I needed to add text in my second column for tags. If anyone needs the formula here it is. Make sure you close out the text with “”. It worked. I have been searching for weeks. I have to repeat URLS in my Tags columns, this makes it efficient.

Sorry. It will return this, as I have thousands of URLS from Column C that need to be in Column D, along with my tags.

Paste the formula down the entire column of D and it will populate each individual URL from C, along with text.

Hi, Jen,

Glad it was helpful. It’s cool to see how you built that comma-separated list with the formula. Great work!

Thanks Anthony! I’m not the greatest with Excel. You put it in terms that I could understand.

Need to translate Equations by formula/function/Spiffy Hack

If you copy, edit, add “=” at the beginning then Press Enter

This is the result

273000 Which is what I want to get

Needs to be able to get the result automatically- too many to do manually

How can i get “00000012345” such number in a cell, as a entry not with formula,

because excel will not sence “000000”.

Alex,

You would need to format the cell to be a text value instead of GENERAL or NUMBER.

Hi Anthony

When you add text to a formula, do other formulas within the spreadsheet using the cell with text, i.e. the one described above, not recognise the number part? I’m sure it should but don’t know how to Google this.

For example, the problem I have is I have a series of cells with this formula:

Using the above, the result for this individual cell shows:

(for ease of clarification I’ll call this cell B114)

However, if I then try to use cell B114 in another formula the number 14 isn’t recognised. Eg =”Total of Number of days = “&SUM(B114,B152)

If I remove the text (so you’d have =SUM(B115:B121) from the cell above the number 14 in the referred to formula is recognised. Is the text causing a problem?

Hope I’ve explained this OK. Sorry if this is really basic.

Cheers

Danny

Danny,

That’s right. When you concatenate text and some number in a cell, you can no longer perform a calculation using that cell.

Hi Anthony,

I have a complicated imagination. I have 1 calculation from 2 data and the result comes in 1 cell (calculating days with DAYS360). Then I already set a conditional format on it, based on the result. Now, I want to make if there no data from the first calculation (either one), then it shows a “N/A”.

Could you please help me with this?

Thanks a lot in advance.

Danny, if you want to use the calculated value as both a “text” value and a “numerical” value, you will need to utilize a second cell. One cell(A3)will have the numerical value for additional calculations. In the other cell (B3), you would utilize the TEXT function to convert the numerical value to a text value so that you can combine that text value with additional text to make it display whatever message you want. That text could be hard-coded in the cell (B3) or come from a different cell (C3). And the cell (C3) could be conditionally formatted to generate different messages based upon some other cell.

1) =”Quantity: “&SUM(A1:A20) IE: 123.123456

2) =”Amount: “&ROUND(SUM(G4:G7955),2)IE: 123.12

I am trying to create a formula that will allow me to convert text in a cell on one spreadsheet into a number on an identical spreadsheet. So if e10=x on spreadsheet 1, I would like e46=1

Frankly everywhere there is an x on spreadsheet number 1 I would like a 1 in the corresponding box on spreadsheet number 2. I’ve tried if statements without luck.

I am trying to foolproof a spreadsheet and automate it the process so that dozens of people using it will not be able to tamper with spreadsheet 2.

Linda, need more detail as to what you’re trying to do. Do you have a defined set of allowable text answers….are they words or just letters? Are you trying to utilize multiple criteria, such as Y=2, Z=3, etc?? How many different text entries will you have? And unless you “protect” the cells, other users could still tamper with your s/s. Need a lot more info to be able to help you.

Thank you for the question. There are a number of different entries and I will be protecting the cells so others can’t tamper with what is set up. So here is the list and some values overlap:

The letters correspond to a particular activity and it is important that I can identify the activity and sort on the activity.

The numerals are representative of a unit of time where 1 = 1 day and .5 = half day and 0 not counted

Just to make things harder, I have one group that actually calculates units of time in .02, .05, .1, .5, 1, and higher.

I was thinking that if I could characterize x or one of the other activities with a secondary identifier such as x1, x2, x3, etc. that I could assign one of the fractions of time to the secondary identifier.

One problem I see right off hand is that you have AA=.5 and also AA=1. Excel will always use the first value you have assigned to AA. Each set of letters must only have one value associated with it. Also, do you plan to add up the numerical values on your 2nd spreadsheet?

This may be a duplicate because I don’t think I asked for a response the first time around.

Ted, Thank you for your response. I am probably making this way more complicated then it needs to be. It would be so much easier with a picture.

Spreadsheet #1

Column 1 Column 2-32

Name Day of Month(day1, day2)

For each name the day is completed with an activity. If they are there for a full day an ‘x’ is placed in the box. If it is a weekend a ‘w’ is placed in the box, etc.

Spreadsheet 2 Same layout except

column 33 total 2:24 there are other calculations but not

meaningful for this discussion

Columns 2-32

Column 2 if x is in corresponding box on spreadsheet 1, then numeral 1 will be in the box on spreadsheet 2

Column 3 if HD is in the corresponding box on spreadsheet 1, than 0.5 will be in the corresponding box on spreadsheet 2 and so on.

So what I want is auto population of spreadsheet 2 based on known values created for the alpha characters in the corresponding boxes on spreadsheet one. I then want to be able to total the rows, total columns and do other calculations based on formulas I create.

Ok, I think I know what you want. The best way to do this is probably using VLOOKUP. It’s created to do exactly what you want……find a value for a cell based upon the content of another cell. As you said, it would be so much easier if you could post pictures, but here goes………

Spreadsheet 1 : Row1 would contain the title NAME in Col 1 and the numerical day of the month in Cols 2-32.

Row2 would contain the patient’s name in Col 1. Cols 2-32 would be the value assigned to that patient for that particular day….AA, HW, etc.

Continue repeating for as many patients that you wish to track

Spreadsheet 2: Copy Spreadsheet 1 (before you fill-in any values for the patients) and paste in Spreadsheet 2

This would simply be the values in Row 1 (NAME plus days of the month) plus the names of your patients.

Spreadsheet 3 : This is where you want to create a table of values for all your variables…AA, HW, SL, etc.

Input the variable in Column 1. Input the variable’s value in Column 2. You can have as many variables as you want, but as I mentioned previously, each variable must have a single value, otherwise Excel will always select the first value it comes to that is associated with that variable. These variables do not need to be input in any particular order.

NOTE: This table could have been created on either of the other s/s, but to keep things clean, I suggest using a separate spreadsheet for your variables

Now you’re ready to start “computin” LOL

Go to S/S #1 : Enter some test values (Row 2 / Cols 2-32) for your first patient….HW, AA, etc. for the entire month. (This is not a requirement, but it does let you see if your formulas are set-up correctly.

Go to S/S #2 : Now we want to enter our formula in Row2/Col2. The easiest way to create your formula is to use the “fx” FUNCTION button at the top of your s/s, just below the command ribbon. Click on the “fx” button and the FUNCTION WIZARD will appear. Type in VLOOKUP if it not present in the list shown and click GO or just dbl-click on it, if shown in list. This brings up the FUNCTION ARGUMENTS (F/A) screen.

LOOKUP VALUE:

STEP 1: Click on the RH button with the red arrow in it. A second smaller popup appears. STEP 2: Now click on the tab at the bottom for S/S #1. Screen will move to S/S #1.

STEP 3: Now click on the variable in Row2/Col2 (notice that that cell is now shown in the small popup).

STEP 4: Click on the RH button on the small popup. This will return your selection to the F/A screen.

TABLE VALUE:

Here you are defining where you want Excel to search for the variable you entered plus the value assigned to that variable.

STEP 1: Same as Step 1 above.

STEP 2: This time click on the tab at the bottom for S/S #3. Screen will move to S/S #3.

STEP 3: Select the entire table you have created….if you think you may expand the table in the future select additional blank rows below the table so you don’t have to redo your formula. Using your data, I selected cells A1:B12 as the contents of my table

STEP 4: Same as Step 4 above

COL_INDEX_NUM : Since I know the values I want to return are in Col 2 of my table, type in 2 in the field to the right of the title Col_Index_Num. (You could go thru the same process as we did for the other arguments above, but why waste time)

RANGE_LOOKUP : Since we only want EXACT MATCHES, type in FALSE in this field.

Click OK and you’re done………well at least for that one cell. LOL

Your forumula should look like this:

Now we need to make a couple of modifications to the formula before copying and pasting to the other cells.

MOD #1 – Since you ALWAYS want to refer back to the same table range, we need to use ABSOLUTE REFERENCES instead of RELATIVE REFERENCES for this. Add a “$”before the A, before the 1, before the B, and before the 12.

Formula should now look like this:

MOD #2 – Since you will be pasting this formula to all cells within your table on S/S #2, you will end up getting “###” error messages for days in S/S #1 which do not yet contain a value. This would be a problem if you are trying to sum rows or columns. To eliminate this problem, we will need to add an IF statement to our function.

The IF statement will tell the function to insert a zero (0) instead of the error message if no variable has been input for a particular day.

So the final function should look like this:

Sorry this is so long-winded, but I never like to assume the knowledge level of anyone who might be trying to do this.

Also, you mentioned something about some people using smaller increments of time…..didn’t exactly understand what you were attempting to do with this…..but if you supply more info, maybe we can add an additional modification.

The scary thing is I actually, sort of understand what you just explained. It is a lot of upfront work but in the end I think it will be worth the hassle. Once I get it moving I will have a better idea if there are any additional tweaks, questions or hair pulling I need to do on my end. Frankly I am trying to stop having to do rework because folks just can’t seem to follow simple directions. If it does what I want I should cut this process to a 10th of what it currently takes to do the work. Thank you.

Linda, glad I could be of help. If you run into problems or want the sample excel spreadsheet I created to verify everything worked as it should, just “holler”. LOL. I’m not a computer geek, (mech engr) but I love using Excel to solve data crunching problems.

Anthony,

will you help me with this problem? How do I incorporate the column with the “#lay/#pallet” onto the “description” column?

For example: on a spreadsheet a have columns with:

ITEM CODE DESCRIPTION #LAYER/#PALLET

Ex: 998345 sweet marmalade, 12/20 ct 18/144

Thanks in advance for your help on this!

TJ

Hi Anthony, Thanks for your help

Is it possible to have text and formula in one line of a cell followed by just text in all the other lines in that cell?

Thank you,

Tim

Is it possible to have an existing word in a cell but then have another cell on a different worksheet insert a number? For example on Sheet A it is fill in and a year is entered. This cell is linked to a cell in sheet B that already has the word Year in it but need that numeric to fill in afterwards.

I apologize I just figured it out thank you for all the awesome comments above, that got me where I needed to get.