Microsoft Office Tutorials and References
In Depth Information
Manipulating Text with Functions
If more than one sheet is selected, the bracketed text [Group] appears after the file name in
the title bar. Any text you enter in the current cell appears in all matching cells on grouped
worksheets. Any formatting you apply to the active sheet, including column widths and cell
formats, is also applied to the same location in each grouped worksheet. Obviously, you
wouldn’t use this technique to enter data, but it’s ideal for copying row labels and column
headings.
Neither Paste nor Paste Special works with grouped worksheets. When you paste, the
Clipboard contents appear only in the active sheet. But you can do the job with one extra step.
Copy the Clipboard contents to the current sheet, and then select the cell or range you just
copied. Now click Fill (in the Editing group on the Home tab), and then click Across
Worksheets. Although you don’t have all the Paste Special options, the Fill Across Worksheets
dialog box, shown here, gives you the choice of pasting contents, formats, or both.
Manipulating Text with Functions
Data can come from the strangest of places, and it sometimes lands in your worksheet as
long strings of text instead of neatly divided chunks of data. In many cases, you can parse
the data at the same time you add it to your worksheet. (We describe these techniques in
more detail in “Importing, Exporting, and Connecting to Data Sources” on page 415.)
In this section, we introduce you to some functions specifically designed to help you break
text strings into smaller pieces and assemble pieces of text into bigger ones.
For a refresher course on this category of functions, see “Text Functions” on page 370.
You can combine text using no functions at all with the & operator. If column A is filled with
first names and column B with matching last names, you can use the formula =A1&" "&A2
(note the space inside the quotation marks) to combine each pair into a full name in
column C, with first and last name separated by a space. You can also use the CONCATENATE
function, which combines two or more pieces of text into a single text string. The
arguments for this function are separated by commas and can contain literal text (enclosed in
quotation marks), cell references, and CHAR functions such as CHAR(10), which inserts a
line break at the current location. (The single argument for the CHAR function is an ASCII
code, which specifies the character to be inserted by the function; in this case, 10 is the
ASCII code for a carriage return character.)
Search JabSto ::




Custom Search