In cell G2, create a formula that will place the word "Yes" if cell F2 (Quantity in Stock) is less than 25% of H2 (Quantity in Reorder) or "No" if this condition is not met. Type your response just once, save it as a template and reuse whenever you want. Combine cells in excel (CH-AD2986) (concatenate formula)exact exam QUESTION- In cell C2 create a formula to combine the item in A2(CH-AD) and inventory ID in. Anyone who works with Excel is sure to find their work made easier. Is it possible to convert a concatenated formula to an actual formula? In this first example, we will see a basic use of the INDIRECT function to understand the principles behind its use. Thanks in Advance!! Type "=CONCATENATE(A2,B2)" and press the ENTER Key. all the way to a20+b20+c20 in all permutations in between. in cell P1 I have =CONCATENATE("I",L1) Click the FILE tab, then select PRINT. Next I would like to create a password generator, using a list of words. Split the text using "+" as a delimiter. In cell A1, type 800-555-5555 and press the ENTER key. The result of the CONCATENATE function is always a text string, even when all of the source values are numbers. While holding down the CTRL key, click cell C2. Step 1: Select cell J2 of the Supervisors worksheet Excel Formulas: Using CONCATENATE to Combine Names - GCFGlobal.org In C2 I have this formula (and then I copied it down): =COUNTIF(A$2:A2,A2)=1 In cell E2, I have this formula to count unique occurrences: =SUMPRODUCT(C2:C9*1) In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter): Font Style: Change the Font Style of the Selected Cell to Bold, Font Size: Change the Selected Cell to 20pt. ID function What used to take a day now takes one hour. Table of contents. Step 5: In the Num_chars field type: 3 Do not waste your time on composing repetitive emails from scratch in a tedious keystroke-by-keystroke way. Could I add a command in this formula to change the color of what results from 'Inv 2/25/22'!O2, or even just make it bold? Is there a formula for this? For example: =IF(B2=C2, "Same score", "") To check if the two cells contain same text including the letter case, make your IF formula case-sensitive with the help of the EXACT function. Below mentioned are the steps to get started with the process: 1. In cell C2, create a formula to combine the item in A2 and ID in B2 with no space between them, so C2 lists CH-AD2986. Try formula =CONCATENATE(A1,A2,A3,",",CONCATENATE(A1,B2,B3),",",CONCATENATE(A1,C2,C3)) this should work. Click the CHANGE button. T-14 In your Excel workbooks, the data is not always structured according to your needs. With the Merge Cells add-in included in Ultimate Suite for Excel, you can efficiently do both: The Merge Cells tool works with all Excel versions from 2016 to 365 and can combine all data types including text strings, numbers, dates and special symbols. Insert Column: Insert a Column to the left of Column F. Select Column F. Click the INSERT button. Click the VIEW Tab. Click OK. Many users wonder which is a more efficient way to join strings in Excel - CONCATENATE function or "&" operator. This comprehensive set of time-saving tools covers over 300 use cases to help you accomplish any task impeccably without errors or delays. Step 6: Click the OK button. CONCATENATE function - Microsoft Support The Number format should . (In Z to A order) and then Quantity in Reorder (Smallest to Largest). So i want to combine the name in Cell B1 coloumn.. Before we start writing the function, we'll need to . How to Merge Cells in Excel - Ultimate Guide - MyExcelOnline How can I make the information (the currency) hold its format when transferred to the next cell? VLOOKUP: In cell F2, create a VLOOKUP formula that will look up the airport code in E2 and display the appropriate city name (using columns K and L). Is there a way to concatenate with an IF statement argument in a separate column from what is being combined? Step 4: Click to select cell D2 It can be from 0 to 4. Im sorry but your task is not entirely clear to me. I hope this helps you and gives you a solution to your problem. Click the INSERT Tab. Before reading this article I concatenated cells by using the method of = and the cells I wanted, separated by spaces for the most part. The function will combine the text from A2 and B2 in a single cell C2, separated . In your worksheets, you may often need to join values in a way that includes commas, spaces, various punctuation marks or other characters such as a hyphen or slash. A + B + C in cell 1 and 1 + 2 + 3 in cell 2 (in Z to A order) and then Quantity in Reorder (Smallest to Largest). Click the PASTE drop-down menu then click VALUES. Click the BORDER drop-down arrow, then select ALL BORDERS. While holding down the SHIFT key, select the option 3 tab. after you have entered CONCATENATE formula, press.. F2 > F9 > Enter. Ex. Regards. I have a number of cells to concatenate in order to generate an unique identifier for each line item. Examples. I want the text 'Assessed: ' and then I want to have the number 18% which comes from a different tab where 2 items are divided. On Error Resume Next Click COUNTIF, then the OK button. In such a scenario, the formula to use will be: So here we used CONCAT formula to combine strings and create a sentence with the additional information. How to Combine Two Columns in Excel - Lifewire To create a formula that checks if two cells match, compare the cells by using the equals sign (=) in the logical test of IF. Method 2nd by using the "&". Please report any error in Fatskills quizzes and questions to 'simpleversity at gmail.com'Without work one finishes nothing. Afterward, select Link. Itll help me understand it better and find a solution for you. Format the selected range of cells as U.S. currency. Type B:B into the RANGE field and press TAB. Type = (equal sign). Click the PAGE LAYOUT tab. For example, the code below shows: ="Given the current inputs, there are enough supplies for "&'Inv 2/25/22'!O2&" days.". I would like to create a table with the day sequence of a month, as but it will be a concat result of ="01/"&G$2&"/2021" for the first line, for example, where G$2 will have the number of the month I want, in this case, fev 02. NOD-BBK 90670 In cell C4, enter the following formula: =CONCAT (A4,B4,B20) The result will be the text string "Nice to meet you!" Explan . Please suggest best formula to create above output. HI I am trying to work out the following formatting and was hoping you could help. Of course you can use something like this, =CONCAT("Gross sales for 2018 were highest at $",MAX(C1:C18)), Function arguments can be text, numbers, cell references, other Excel functions, How to insert a character in the middle of string/number? Could you please advise? Quantitative Aptitude and Numerical Ability For Competitive Exams, Computer Fundamentals For Competitive Exams. Hello, E10 to value of A2+9=27 D2 John L1 John, James, Dk. With .Characters(xRgLen, Len(Trim(xRgVal))).Font If you click on cell C2 and look at the formula, it should read: =A2&B2. Click on the ADVANCED button, then click the EDIT CUSTOM LISTS. Cell C1 should print "Joe Smith" skipping "Mr" from Cell A1 values. XYZ PRODUCT 2 XYZ PRODUCT 1; PRODUCT 2; PRODUCT 3. can you help simple formula like number 12345678879093879, to be =12+34+56+78+87+90+93+87+9 (546) and add "+" every 2 number after but later result sum that all number? A recommendation, though, if you don't mind: have two cells, one with first name, one with last, and then you can CONCATENATE either way. It will give you the following output: The result will be: It is ideal to have space between First Name and Last Name. Paste Special: Copy the selected cells and paste with actual values. Click the VIEW tab. Each cell reference must be listed separately. I have a sales record covering 3 years and i want to report on the highest sales made per year. Itll help me understand it better and find a solution for you. You can convert text to a formula using a VBA macro. To remove a space, use the SUBSTITUTE function as described in this instruction: How to remove all spaces in Excel. Unfortunately, the format can only be set for the whole cell, not for a single character. and in cell S1 I have =CONCATENATE("I",O1), Then in T1 I have In cell A2, I would like to use the second digit "0" at the beginning of the unique identifier, so need to skip the first digit in cell A2 and use the second, while the rest of the formula stays the same.so the result should then read 0729-SNK-34. After that, select Browse for File. Click the SORT & FILTER button then click FILTER. Click the COPY button. Is it possible? Click the PAGE LAYOUT tab. xRgLen = xRgLen + Len(Trim(xRgVal)) + 1 'Set xSRg = Application.InputBox("Please select cell columns to concatenate:", "KuTools For Excel", xAddress, , , , , 8) .Underline = xRgEach.Font.Underline Place your mouse over HIDE & UNHIDE, then click HIDE COLUMNS on the drop down menu. 'Updateby Extendoffice Find all links in your document, get them verified, correct invalid ones and remove unnecessary entries with a click to keep your document neat and up to date. Choose where you want to place the result. 1. Column 2- 30295699, The result I require is NE5699 in Column 3. PARTALOC1 Creat a chart from the selected range of cells. Step 2: On the Formulas tab, in the Function Library section, click the Text drop-down menu. E column where E1 is "IF(C1="Y", A1, "")". Click the PRINT button. Press =. B2 B3 Use a relative reference for cell F2 and an absolute cell reference to refer to the row for cell F76. You can also use the "&" operator to . Click the THEN BY Drop-Down Arrow then click QUANTITY IN REORDER. Car 5999; Car 6000. So that it reads "Gross sales for 2018 were highest at $35000", Hello! The cells contain, for example - A2: 200729, B2: SNK, C2: 34543 'xAddress = ActiveWindow.RangeSelection.Address Click the TAB checkbox. Set the Print Area to contain the selected cells A1:C7. To combine data in each individual row, column-by-column, you choose: To check how the Merge Cells add-in will cope with your data sets, you are welcome to download a fully functional trial version of our Ultimate Suite for Excel below. If they dont work for you, then please describe your task in detail, Ill try to suggest a solution. Example #2 - Use the Ampersand (&) Symbol as the Alternative. Click the BORDER Drop-Down Arrow, then select ALL BORDERS. MID(A1,1,1)&MID(A1,3,1) B4 Data Validation- List (only want to include the first character) Fill Handle: Use the fill handle to fill the empty cells from G3:G11. In this article, you will learn various ways to concatenate text strings, numbers and dates in Excel using the CONCATENATE function and "&" operator. Try the following formula: =CONCATENATE(B2,B3,LEFT(B4,1),LEFT(B5,1),LEFT(B6,1),B7,LEFT(B8,1),B9). Unfortunately, =INDIRECT(D1 & "25") is returning 0 though. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Copyright 2003 2023 Office Data Apps sp. Buy from $74.05: Introducing Estee Lauder's revolutionary Synchronized Multi-Recovery Complex - the perfect solution to combat the visible signs of ageing. Click the FILE tab, then select OPTIONS. Use a relative reference for cell F2 and a mixed cell reference to refer to the row for cell F76. Excel String Concatenation. .Value = .Value & Trim(xRgEach.Value) & " " 1. I'm looking for the exact same function. Click on B2 and hit Enter; You should see the contents of A2 and B2 combined together in C2. You can use the guidelines above. L1 John Click OK. Click the SPELLING button. a2 a20 Look for an exact match. Click the OTHER checkbox, then type ":" into the OTHER field. Yes, it's possible. Hello, Select a different account. I don't know how to thank you enough for your Excel add-ins. Click the OK button. Click TABLE STYLE LIGHT 2. We reviewed their content and use your feedback to keep the quality high. Click the RUN Button. All rights reserved. Click the 2-D column design. In this case, you can use the TRANSPOSE function to return an array of values, and then merge them together in one fell swoop. Please specify what you were trying to find, what formula you used and what problem or error occurred. Concatenate text in one cell as described in the article above. How i merged a text like (sex :) and formula like (iferror(vloop..). xRgVal = xRgEach.Value Below you will find 3 methods of quick range concatenation in Excel. Click the DESIGN checkbox then click the OK button. A comma is used as a separator. Have you tried the ways described in this blog post? Excel formula with barcode scanner. Hit FORMULAS tab, then AUTOSUM drop-down menu, then click AVERAGE. Insert the column, Change the Title to what ever. Click OK. Format Cells as Currency: Format the selected range of cells as U.S. Currency. Print Centered on Page: Perform the commands to center the selected worksheets both horizontally and vertically on the printed page. Cell E1 contains the name as text. Unlike the CONCAT function, Excel CONCATENATE does not recognize arrays. Dim xRgEachRow As Range That's how to concatenate in Excel. Edit PivotTable: Using the current worksheet's PivotTable, add the TaskName as a Column Label. You can use VBA or our ready-made solution. Type * (Shift+8 on the top row of the keyboard). bbb 123 Select the cell where you want to combine the hyperlink with text. No additional hardware or website logins are necessary. Explain your position. 2. Select the worksheet in which your code is to run. For example, if cell A1 contains the number 23.5, you can use the following formula to format the number as a dollar amount: =TEXT (A1,"$0.00") Result: $23.50. Click the COLUMN D Drop-Down Arrow. . In the Marketing Budget Plan worksheet, cell E51, insert a COUNTIF formula to count the data in cell range D43:D51 that is greater than 0. Ex: Hit autosum button again. After that, you may obtain the result, "Price . It is like having an expert at my shoulder helping me, Your software really helps make my job easier. You can get the first and third characters from the text using the MID function. Next I And vice versa if I use =("X"&A5&(SUBSTITUTE(B5,"=",""))&C5) This is still a text string of course and I want to convert it a formula to give me the actual value of cell P16 on the TM Receipts worksheet. I love the program, and I can't imagine using Excel without it! Type "-CONCATENATE(A2,B2)"and press the ENTER key. Text Alignment: Change the Alignment for the Selected Cells to the Right, Selecting Cells: Select Cells A3 through F3. please inform us the formula. There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. Click the PROJECT NAME checkbox then the TIME IN HOURS checkbox. Check if two cells are the same. Click the COL_INDEX_NUM field and type "2". Is there a way to concatenate cells across multiple rows IF you there is a repeated value in another column? Check the boxes next to HORIZONTALLY and VERTICALLY. B3 01 Click the NEXT button. How to Combine Two Columns in Google Sheets - TechCult Type a comma, and then type the address of the next cell that you want to combine with, such as B2. Header/Footer: Add the Footer "Page 1" to the current worksheet. Click the STYLES drop down menu, then select the FORMAT AS TABLE button. in cell c2 create a formula to combinemegabus cardiff to london. C2 C3. Click the FORMULAS Tab, then click INSERT FUNCTION button. If you describe in more detail your task, initial data and the desired result, I can give more detailed advice. Check the boxes next to HORIZONTALLY and VERTICALLY. Enter a date in the cell. Click the QUARTERLY SALES checkbox. You can change the format of an entire cell either manually or using conditional formatting. We will also discuss formulas to combine individual cells, columns and ranges. PARTBLOC2. Click the Spell Check Button. To combine the values of two cells into one, you use the concatenation formula in its simplest form: Please note that the values will be knit together without any delimiter like in the screenshot below. Each time I run a report the data sheet may contain a different set of combinations and appear in different orders. Some people use home remedies that don't involve chemicals to treat stains. Please check out the following article on our blog, itll be sure to help you with your task: How to copy formula in Excel with or without changing references SEP-2005 00:00:00. Column C combines all the two cells' contents. Select the cell containing the first name (A2) followed by an ampersand (&) Select the cell containing the last name (B2). Click the INSERT tab. Press enter on the keyboard. Concatenate: In Cell 2 - Create A Formula To Combine The Item In A2 (Ch-ad) And Inventory Id In B2 (2986) With No Space Between Them - So That C2 Lists "Ch-ad2986" Click The Data Tab - Then Click The Text To Columns Button. Click the BREAKS Drop-Down Arrow then click INSERT PAGE BREAK. Click the FORMAT button. And all sheets have count of students in A2 cell in perticular sheet. Click the FREEZE PANES button, then select FREEZE TOP ROW. NOD-BBK-BTD_90670 Select the cell where you want to insert the combined data. I have defined names that reference cells containing physical part subtotals in sheet WIP for each part and location combination. It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hello, T-14= Can we please check why this code is broken? TEXTJOIN Function - Examples, How to Join Text in Excel The following screenshot demonstrates how the results may look like: Tip. It's almost like the '&' function seems to negate these properties. Is there a formula for example This information together ? Click the CATEGORY Drop-Down Arrow then click STATISTICAL. While holding down the CTRL key, click cell C2. What if i want to concatenate in excel then i also want to add a open expression Look for an exact match. For me to be able to help you better, please describe your task in more detail. I hope you have studied the recommendations in the tutorial above and my answer to your previous question. In cell D2, input the formula: =B2&C2. Click the PRINT AREA button then click SET PRINT AREA. So I want to be able to eliminate all + symbols and = symbols. For example: The above formula informs the user that a certain project is completed, as in row 2 in the screenshot below. For I = 1 To xSRgRows Hello! .Name = xRgEach.Font.Name While you could do something like For example. BTD-TGD_32218 This is my formula: B2 thru B5 have: T-14 T-14+ T-14= T-14++= in which the hyphen, plus sign, and equals sign are not actually mathematical, but rather are part of the "model number" (no idea why). If I understand your task correctly, the following formula should work for you: I am trying to concatenate two separate data sets where all the data in A get's repeated for each cell in B. B9 21, B11 Formula that will combine cells B2-B9 in order. Click the SELECT ALL checkbox. If any of the arguments is invalid, the CONCATENATE function returns a #VALUE! Excel CONCATENATE function requires at least one "text" argument to work. Step 1: Select cell C2 of the Trucks worksheet. In the Profit - Loss Summary worksheet, select cell D8, in the Formulas tab, in the Function Library group click Insert Function. I have a list of words, numbers, and symbols in column I. Hello! Try the following formula: .Superscript = xRgEach.Font.Superscript eg: 12345 -> 123-45, =CONCATENATE(LEFT(B12,ROUND(LEN(B12)/2,0)),"-", RIGHT(B12,LEN(B12)-ROUND(LEN(B12)/2,0))). Is there a better way? If the value R210#P is written in cell B1, then it cannot be changed using an Excel formula. I was able to create the password jd123456 Solved Task Instructions In cell C4, create a formula using | Chegg.com Click the PAGE LAYOUT tab. Type =CONCATENATE( before the first value, then type the closing parenthesis after the last value, and press. If RANDBETWEEN selects numbers, for example, from 1 to 10, then each number can correspond to a word using the CHOOSE function. With the concatenation operator, the formula can be written this way: In the same manner, you can add a text string in the beginning or in the middle of your concatenation formula. Click OK. Click the FILE tab, then select PRINT. A space (" ") is also inserted between the combined values, so that the result displays as "Project 1" rather than "Project1". Click the FORMULAS tab, click the INSERT FUNCTION button. Please specify what you were trying to find, what formula you used and what problem or error occurred. A2 thru A5 all have the same data: AA . Click the OK button. Combine cells in excel (CH-AD2986) (concatenate formula) D2 Mary. Uncheck The Tab Checkbox. Click the PAGE SETUP dialog box launcher. Or use the formula, Hello : 0001 code when i use for concatination formula its shows only as 1, How to show as 0001. For combining them, you need to enter the following formula in cell C2: =CONCATENATE ("Price of"," ",A2," ","is ",B2) Step 2: Enter the formula in cell C2. A + B + C + D and 1 + 2 + 3 + 4 and so on.. Hi! Hello! =CONCATENATE((INDIRECT(P1)),"-",(INDIRECT(P1)),"-",(INDIRECT(Q1),"-",(INDIRECT(S1))). Grade B You are always prompt and helpful. Click the TABLE_ARRAY field and type "K:L". TT/VV Recommended Articles. Type "B:B" into the RANGE field and press TAB. Line D is a date value and I want to combine "D2" and "C2" values. And not the result that I want for Rows 4 and 5, which return these results: XAAT-14=44, XAAT-14=55. UU SS/UU TT/TT In Excel 365 - Excel 2019, the CONCAT function is also available, which is a modern successor of CONCATENATE with exactly the same syntax. B8 Data Validation-List (only want to include the first character) In this example: There's text in cell A2, with a space character at the end. If you copy and paste a formula into a new cell, Google Sheets will automatically change it o reference the right cells; for example, if I enter =A2+B2 in cell C2, then drag the formula down to C3, the formula will become =A3+B3. PARTALOC2 Is there a way to concatenate an array (obtained as a result of a formula in a cell)? Click the FINISH button. Type "E2" into the LOOKUP_VALUE field. In this case, you want to combine the data in two cells without a space between the values. The opposite of concatenate in Excel is splitting the contents of one cell into multiple cells. ARRAYFORMULA takes into account the new range (changes B5 to B6, C5 to C6, D5 to D6 in the formula), and does the calculation, unlike SUM, which is expected for Google Sheets. Thanks in advance if someone can help me please! This would combine the first name and last name with a space character in between. You cannot format a part of a cell in excel, for example, highlight it with a color. To combine the text from different cells you can use another formula too:-Select the cell C2; Enter the formula =A2&" "&B2. "Excellent" also can not describe the good work you have done and it's being explained in such a easy manner even a layman will become an expert in Excel. Step 4: Click OK. Click the VIEW tab. #N/A 123456, When concatenated I need these to read as such, "654321#N/A" and "#N/A123456". Click the DESIGN checkbox then click the OK button. goodday, please assist me i have one cell with name and last name but with a space in between. Click the OK button. I mastered these real exam questions and I am here to help you all.check this channel too; https://youtu.be/6J4U-dXYPqAcheck this channel too;https://youtu.be/uExnC0q5GjQcheck this channel too; https://youtu.be/JN0eii6OWKU EXCEL ASSESSMENT QUESTIONSQUESTION: In cell C2 create a formula to combine the item in A2(CH-AD) and inventory ID in B2(2986) with no space between them, so that lists CH-AD2986(concatenate formula) https://youtu.be/whaWKbRIR3IQUESTION:Use a function in the selected cell to calculate the average of the values in the range C2:C10 https://youtu.be/jb-jXb7QQSoQUESTION:Clear the contents of the selected cellhttps://youtu.be/fWT-8ggOym0QUESTION:How to Select cells B19 and B21 simultaneouslyhttps://youtu.be/yErrJmABbuoQUESTION:Simultaneously replace all instances of the word SUMMER in the current worksheet with the word SEASONhttps://youtu.be/oVGnJ7CjGY0QUESTION:Clear COMMENT in EXCEL https://youtu.be/LU5hL1fo9goFor subscribe button: https://www.youtube.com/channel/UC8oFXYexsRR_Str9JauzW-g/featured?disable_polymer=1 excel functions and formulas,excel, #concatenateexcel,microsoft excel (software),combine cell,combine text,excel 2016,average excel,combine, #concatenate, #concatenation,tutorial,average formu,formula,delimiter, #beginners,#15secondsuknow, #averagefunction, #select, #convert, #computer, #semicolon, #into, #cell, #learn#excel #exceltutorials #excelexam #excelfun#excelexam,#exceltutorial #excelassessment #excelguru #excelfun #excelquestions,#excel formulas #microsoftexcel #howto #mergecells #jointextinexcel#combinetextinexcel #excel2016 #combinecellsintoone #combinecellswithcomma #combinecellsinexcel #microsoftexceltutorial2016 #exceltutorial #excelconcatenate #exceljoincells #excelconcatenate cells #joindataindifferentcellsintoasinglecell #learnexcel#combine cells in excel without losing data aaa 123 If you change it to the formula = 2 + 1, then you will get the desired result. Hello! Please, Consider marking this reply as the answer to your question if it does so. Apply a filter to Task Name (column D) so that only Design Tasks are shown. Instead, you use the CHAR function to supply the corresponding ASCII code to the concatenation formula: In this example, we have the address pieces in columns A through F, and we are putting them together in column G by using the concatenation operator "&". Step 2nd. Often you may want to split the content of one cell into individual cells or do the opposite - combine data from two or more columns into a single column. Click the AUTOSUM button again. Buff Milano any ideas? Use all other defaults. In the Marketing Budget Plan worksheet, select cell E51. In the cell you will get a link to the name. Run a Macro: Run the Macro named, "Phone". Explanation: The CONCAT function will combine the text strings in cells A4, B4, and B20 into a single cell. Example 4. Click the VIEW tab. When you concatenate cells in Excel, you combine only the contents of those cells. Please try the formula. In this way i have a huge coloumn can any one solve my problem.. Is there any formula Hi Is there any way to concat General non-text fields and output the result as a numeric? Click OK. Change the Table Style to Table Style Light 2 (2nd column, 1st row). My all sheets have different number of students aaa 789 On line 1 it's A9, etc). Apply the style Heading 3 to the title Blackbread Books. Itll help me understand it better and find a solution for you. Click the HEADER/FOOTER button, then FOOTER, then PAGE 1. Click the OK button. Click the FORMULAS Tab, then click the LOOKUP & REFERENCE button then click VLOOKUP. Click the FINISH Button. .Strikethrough = xRgEach.Font.Strikethrough Concatenate: In Cell 2 - Create A Formula To Combine The Item In A2 (Ch Select worksheets Option 1, Option 2, and Option 3. In one formula, you can concatenate up to 255 strings, a total of 8,192 characters. F8 is First Name, G8 is Last Name. You cannot change the format of part of a cell using a formula. I had started with a concatenate function but couldn't get it to work for me either. Its two key advantages are simplicity and speed - any concatenation is done in a couple of clicks. The Macro Name is "Phone" and the shortcut-key to assign to the macro is "CTRL-e". Click the PRINT button. You can also use it to combine text strings to make the result more meaningful. So that's the recommendation. Create PivotChart: Using the current worksheet's selected data, create a PivotChart that displays the Project Name and Time in Hours.