# Excel: Combine text and formulas in a cell

When 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 data faster.

One way to give yourself flexibility with formatting is to combine text and the results of a formula in a single cell. 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.

Anthony DeBarros is the author of Practical SQL: A Beginner’s Guide to Storytelling with Data from No Starch Press.

### 101 responses to “Excel: Combine text and formulas in a cell”

1. -L says:

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.

2. Janna says:

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?

3. Anthony says:

Janna,

Good question. Adding the dollar sign is pretty easy:

="Quantity: "&"\$"&SUM(A1:A20)

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

4. George says:

How to I create :

Date: 9/4/2013

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

Thanks!

5. Anthony says:

George,

Try this:

="Date: "& TEXT(TODAY(), "mm/dd/yyyy")
6. George says:

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.

7. Matt says:

Janna,

You could use:

="Quantity: "&TEXT(A1,"\$0,000.00")

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.

8. Matt says:

Janna,

Sorry that formula should have read:

="Quantity: "&TEXT(SUM(A1:A20),"\$0,000.00")
9. Kim says:

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!!!

10. Anthony says:

Kim,

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

="Due: "&TEXT((K234-28), "mm/dd/yyyy")
11. Gavin says:

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!

12. Anthony says:

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.

13. Johannes says:

Hello Anthony!

Thank you for a great post. How do I:
*Choose numbers of decimals
*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.

14. PAt says:

Johannes

Use the

=ROUND(62,408543957; 2)

function

15. paul says:

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.

16. janil says:

thank you so much.. 🙂

17. Bobby says:

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?

Bobby

18. Bobby says:

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

19. mh says:

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.

20. Roland says:

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

Cheers.

21. Jake Burns says:

thanks man.

22. GREG says:

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

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

24. talha says:

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.

25. Jack says:

Can u please rectified the said formula.

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

Regards,
Jack V.

26. ZJYB says:

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.

——————-

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:

="P/"&row()

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.

27. Lauren says:

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?

28. DJ says:

Thanks! I’ve been fighting this for weeks

29. Geet says:

Thanks.

30. Jegs says:
=UPPER('Your Final output with Formula'!A2)&", "&PROPER('Your Final output with Formula'!B2)&" "&LEFT('Your Final output with Formula'!C2,1))&".

31. hussam abdulalim says:

i input:

="WEEKLY AVAILABILITY REPORT from  "&TEXT(H7, "[\$-409]d mmmm;@")&" to "&TEXT(H13, "[\$-409]d mmmm;@")

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

32. Janet says:

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)

33. Anthony says:

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

=date!A1

To combine dates into text requires a little extra formatting:

="The date is "&TEXT(date!A1,"mm/dd/yyyy")&" for this report."
34. Janet says:

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!

35. Ted says:

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

36. Ted says:

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

37. Lisa says:

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

38. Dewy says:

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?

39. Dewy says:

This generated the correct cell but doesn’t actually reference it.
=C5&”!A”&B5

40. TED says:

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)

41. Dewey says:

Thanks! The INDIRECT function worked perfect.

42. KB says:

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

43. Anthony says:

KB,

You’re close. Try:

="There are "&B5&" widgets in the box."
44. CL says:

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?

45. Anthony says:

CL,

Sure:

=G7&"/"&F7
46. CL says:

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

47. CL says:

just saw your reply too. thanks anthony!

48. Sheri says:

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.

49. Johnny says:

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

Thanks

50. Anthony says:

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.

51. Lloyd says:

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
52. James Markarian says:

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

Col A    Col B   Col C   Col D
Row 1  3.150   -1.500	X3.15	Y-1.5

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

53. Anthony says:

James,

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

="X"&TEXT(A1,"0.000")
="Y"&TEXT(B1,"0.000")
54. James Markarian says:

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

55. Anthony says:

James,

Glad you got it sorted out!

Anthony

56. dennis says:

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

57. Ted says:

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.

58. Jen says:

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.

="Jennifer,article,"&MID(C4,FIND(":",C4,"4")+3,FIND("/",C4,9)-FIND(":",C4,"4")-3)&"luxury,décor,home"
59. Jen says:

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.

Jennifer,article,www.hgtv.com,luxury,decor,home

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

60. Anthony says:

Hi, Jen,

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

61. Jen says:

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

62. Steve G says:

Need to translate Equations by formula/function/Spiffy Hack

78*15*14*(50/3)	     =+INDIRECT(\$C\$100)	     #REF!
=+\$C\$100*1	             #VALUE!
=+numbervalue(\$C\$100)   #NAME?

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

63. Alex says:

How can i get “00000012345” such number in a cell, as a entry not with formula,
because excel will not sence “000000”.

64. Ted says:

Alex,

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

65. Danny says:

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:

="Total Day Duration ST 1 Fire Assist = "&SUM(B115:B121)

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

Total Day Duration ST 1 Fire Assist = 14.

(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

66. Anthony says:

Danny,

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

67. Lusya says:

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

Thanks a lot in advance.

68. Ted says:

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.

69. Harpalsinh Gohil says:

1) =”Quantity: “&SUM(A1:A20) IE: 123.123456
2) =”Amount: “&ROUND(SUM(G4:G7955),2)IE: 123.12

70. Linda Keldsen says:

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.

71. Ted says:

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.

72. Linda Keldsen says:

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:

HD = .5, NC = .5, DC = .5, AA = .5
X = 1, SL = 1, H = 1, HW = 1, AA = 1
N = 0, AL = 0, W = 0, PC = 0

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.

73. Ted says:

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?

74. linda keldsen says:

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

75. Ted says:

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:

=VLOOKUP(Sheet1!B2,Sheet3!A1:B12,2,FALSE)

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:

=VLOOKUP(Sheet1!B2,Sheet3!\$A\$1:\$B\$12,2,FALSE)

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:

=IF(Sheet1!B2>0,VLOOKUP(Sheet1!B2,Sheet3!\$A\$1:\$B\$12,2,FALSE),0)

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.

76. linda keldsen says:

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.

77. Ted says:

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.

78. TJ says:

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

79. Saravanan says:

Hi Anthony, Thanks for your help

80. Tim Ball says:

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

81. Liza says:

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.

82. Liza says:

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

83. james says:

Hi.

My formula is

Dump!\$F:\$F,"James"

and I want to add another Name

I tried

Dump!\$F:\$F,"James" & "Joseph"

but it doesn’t work.

84. Anthony says:

James,

Typically, the format for concatenating works like this:

=D1&"James"&"Joseph"

Assuming you have a value of 1 in D1, the cell will display:

1JamesJoseph
85. Karen says:

Anthony,
I want to be able to add a formula to a cell, then be able to add text on multiple lines after the formula. Need to create a return, then add text as I wish or need to. I don’t want text to be part of the formula. Every time I add the text, it wipes out the formula, even if the text is on a separate line in the same cell

86. Michael says:

Hi Anthony,

I hope to find an answer here…

i am trying to combine a text with a formula, but i am constantly getting the wrong data. The text reads “Ending Stocks ” and the formula is EOMONTH(A1;0) whereby A1 is 01.01.2018. I am trying to get the end of the month of january indicated as 31.01.2018.

But i input the following:

="Ending Stock "&TEXT(EOMONTH(A1;0);"tt.mm.jj")

and i get:

Ending Stock 31.00.18

The month of January is missing.

Thanks upfront for your help!
Michael

87. Anthony says:

Michael,

This works for me — my Excel is using the English (United States) locale:

="Ending Stocks "&TEXT(EOMONTH(A1,0),"dd.mm.yyyy")

See this post for date/time formats.

Hope that is of some help,
Anthony

88. Michael says:

Fantastic… It worked! And it was so simple…

Thanks a lot Anthony. Much appreciated.

Best,
Michael

89. monica s says:

Anthony, this entire thread was awesomely helpful … thanks!!! Great job!

90. Anthony says:

You’re very welcome!

91. eiram carl betanio says:

Hi i want to convert this number (7,568,644,763) to (7.569B).
Thanks.

92. Judy Hartzell says:

Hi,
Could you please help with creating a formula for column B and column C rows 7-12. The information starts with a letter E then numbers E30512036. E30512036 through E30512041. Sometimes there are repeat numbers. I am looking to eliminate any repeat numbers and combine the numbers for an end result like E30512036-041. Can this be done?
Thank you for your help.
Judy

93. Nuke Kid says:

Using formula =”Total Sales: “&SUM(A6:Z6)/\$AZ\$46 results in “Total Sales: 0.086861099549737”. The cell is formatted as “percentage”.

Why is the result not “Total Sales: 8.69%”?

94. Philip says:

Hi,
i am struggling to solve this issue.

i want to add in one cell ten digit numbers separated by commas like this:

25,55,30

and sum them up in another cell like :

25,55,30 –> 110

any advise on how to do that?

95. Anthony says:

Take a look at https://www.extendoffice.com/documents/excel/3959-excel-sum-numbers-with-commas.html. That might offer some ideas.

96. Philip says:

That doesnt help me at all, i already visit that link.
it separates the ten digits numbers in to columns. i need them with in one cell

97. Anthony says:

Afraid I can’t help with that one.

98. Ted says:

Phillip, are the numbers 25,55,30, entered manually by you in a single cell or is each number already in a separate cell and you want to combine them into a single cell ?

99. Philip says:

The Numbers 25,55,30 (or what other numbers) are entered manually by me in a single cell.

100. TEDDY HUMPHRIES says:

Philip, still wasn’t sure whether you were entering 10 separate numbers or the numbers were 10-digits in size. Regardless, this would work for any size number or any quantity of numbers. Hopefully, this will meet your needs.
1) INPUT your numbers into Col A
2) After inputting all your numbers into Col A, COPY Col A and PASTE into Col B
3) SELECT all the values in Col B
4) In the ribbon, CLICK on the DATA Menu.
5) On the DATA Menu, CLICK on the option, TEXT TO COLUMNS
6) PAGE 1 – TEXT TO COLUMNS FUNCTION pop-up – click on the DELIMITED option.
7) Click on the NEXT button. Page 2 will appear.
8) PAGE 2 – TEXT TO COLUMNS FUNCTION. Delimiters options: select COMMA. Un-select any other options
9) “Treat consecutive delimiters as one” option : Should be unchecked
10) “Text qualifier” option should be set to NONE.
11) Click on the NEXT button. Page 3 will appear.
12) PAGE 3 – TEXT TO COLUMNS – Column Data Format – Select GENERAL
13) “Destination” – This defaults to the first cell in your selection of “copied numbers”, which was B4
14) Change this to the next COL value, C4, so as to retain the “copied numbers”.
15) Click on the FINISH button.
16) Voila! This is the results of your TEXT TO COLUMNS conversion function.
The ten numbers you originally entered/copied have been parsed into ten columns.
17) Click on cell M4 (the next empty cell to the right of your parsed numbers
18) Enter the SUM function, =SUM(C4:L4) to get a TOTAL for that row.
19) Copy and paste that function to Col M for all rows below your function cell which have ITEM NUMBERS

If you feel the need, I have a pdf of step-by-step screenshots for this situation. Just list your email and I will forward.

101. TEDDY HUMPHRIES says:

Philip, forgot to add, …….. If you don’t want to see the “copied numbers” or the “parsed” columns, simply hide the columns when you’re finished.

This site uses Akismet to reduce spam. Learn how your comment data is processed.