Google Sheets Conditional Formatting based on selected item in drop down.1 Excel for Mac Text Functions HOW TO CLEAN UP TEXT IN A FLASH This document looks at some of the tools available in Excel 2008 and Excel 2011 for manipulating text. Is there a way to type a word in column A and have it autofill column B Is. Once youve done this, follow these steps: Select the whole table you are working on. This lesson assumes you have already created a table that spans two or more pages (or will do by the time youve finished creating it). Tell Microsoft Word not to split table rows across pages. Note that this lesson covers Microsoft Word 2007, 20 for Windows, and Microsoft Word 2011 for Mac.Move to previous cell in row. This document and other Information Services documents are held online on our website:Select column. You can download the exercise files for this workbook from our website with the course code is141e. A basic knowledge of using functions in Excel like SUM and COUNT would be an advantage. This workbook assumes a working knowledge of Excel.7 Paste Values to avoid errors when deleting columns. 5 Merging columns into one (CONCATENATE). 4 Splitting one column of text into two or more columns (Convert Text to Columns). 3 Autofill to copy a formula down a column.
Hold down Shift and press an arrow key repeatedly. Select the preceding cell's contents. Select the next cell's contents. 3 Excel for MAC Text Functions Find and Replace to update multiple entries in one hit If you need to replace all instances of a certain string of text with another, use find and replace: Click on Edit > Replace on the menu bar. Shortly the pointer should change to a ghost insertion point with a small box next. Click on the highlighted row or column, and hold down the mouse button. Select the entire row or column that you want to move. To move a row or column using the mouse, follow these steps: 1. Click in the column's top or bottom cell.One such task involves moving rows or columns within a table. Select the column containing the membership types by clicking on the column header for column F. We want to therefore replace any instances of the word Life with the word Super. We have renamed our Life membership to now be called Super. For this exercise, imagine that we have changed the branding of our membership types. This is a membership list. Exercise A Using Find and Replace Open up the exercise file Find_Replace.xlsx. Excel will tell you how many replacements it has made. This tells Excel you want to replace the word Life with the word Super. Type Super in the Replace with box. This asks Excel to find all instances of the word Life. Type Life in the Find what box. Where the rows are divided by Mblocks and the columns are divided by Nblocks.4 University of Brighton Information Services Autofill to copy a formula down a column When you want a formula to appear in every cell within a column: Build the formula in the first cell in the column Use autofill to copy the formula down the column We will use the autofill technique throughout this booklet. Printed Wednesday, 29 July 2015 Lasso Studio is a popular, trial version program only available for Mac. Click Close to close the Find and Replace window. Cell I2 contains a formula to calculate the monthly payment for that row, based on the annual fee in column G. Exercise B Using Autofill Open up the exercise file Autofill.xlsx and double click on cell I2. Click on to change the type of fill (series, copy, formula only etc.) Double click on one of the newly-populated cells within the column to check that the copied cell references are correct. Click and drag to copy the data OR Double-click to autofill all rows. Point at the fill handle until it becomes a cross. The fill handle will appear at the bottom right of the selection. Double click on one of the newly populated cells to check the cell references for that row. Double-click only works if the column to the left has data in every cell. Click and drag down column I to copy the data OR double-click to autofill all rows in the column. Point at the fill handle until it becomes a black cross. The fill handle will appear at the bottom right of the cell. Press Esc on your keyboard to exit the function. Exercise C Using Text to Columns Open up the exercise file Text_to_Columns.xlsx. In the Data preview at the bottom of the window, check that the proposed split is what you are expecting and then click on Finish. Printed Wednesday, 29 July 2015 6 University of Brighton Information Services If the character separating your text is not in the list, choose Other and then type the character used as the delimiter in the box as shown on the left and click Next. So a space is the delimiter. For instance, in the example at the top of the page, a space separates the first name and the surname in column B. For sections of text separated by commas, spaces, semi-colons or other characters, check that the Delimited option is selected and click Next In the Delimiters section, choose what character separates the two sections of text in your column. Select column B by clicking on the column letter. Insert a blank column to the right of column B. So: We need 2 columns for the split (including column B itself) The delimiter (which tells Excel where to make the split) is a space. Our Name column has 2 words in each cell, separated by a space. In the next step of the wizard, we must tell Excel what the delimiter is. Our text has a delimiter, so we do not change anything in the first step of the wizard, we simply click Next. The Convert Text to Columns wizard appears. In Excel 2008, click on the menu path Data > Text to columns. Move Highlighted Column To First Column 2011 Full Name AsYour function will now read =CONCATENATE(B2, We want a space to appear between the first name and surname. In cell D2, start your CONCATENATE function by typing =CONCATENATE( Printed Wednesday, 29 July 2015 8 University of Brighton Information Services Use your mouse to click on cell B2 and then type a comma (to say you have finished identifying the first part of the text string). Insert a new column to the right of column C and type Full Name as the column heading. In this exercise we are going to merge the Name and Surname columns into one column. Merging columns into one (CONCATENATE) This involves three steps: Insert a new column In the new column, use the CONCATENATE function to build the new string of text original columns) Use Paste Values to strip the CONCATENATE formula from the new column, leaving just the string of text (only needed if you want to delete the Format Example Result =CONCATENATE(text1, text2, ) Where text can be: A cell reference Fixed text (always contained within quotation marks ) =CONCATENATE(B2,C2) =CONCATENATE( Patient s name is,b2,c2) Roger Wilson Patient s name is Roger Wilson Exercise D Using CONCATENATE Open up the exercise file CONCATENATE_1.xlsx. Last Updated 15 th July 20157 Excel for MAC Text Functions In the Data preview at the bottom of the window, check that the proposed split is OK: check that each name has been split as you were expecting. Gamecube mac emulatorBefore we can delete the referenced columns, we must first remove any functions that reference the columns you want to delete. If a text function like CONCATENATE, PROPER or LEFT references cells in other columns, simply deleting those columns will result in an error as the text function will no longer be able to find the cells it references. For example, in the exercise on the previous page, we may want to delete column B (First Name) and column C (Last Name) and just leave the new column D (Full Name). To enable us to delete columns B and C we will now need to Paste Values, as explained in the next section Paste Values to avoid errors when deleting columns After using a text function like CONCATENATE, we often want to delete the columns referenced in the function, leaving just the new column containing the result of the function. Your function will now read =CONCATENATE(B2,C2 Press ENTER on your keyboard to complete the function and copy the formula down the column using autofill.
0 Comments
Leave a Reply. |
AuthorAnn ArchivesCategories |