Saturday, October 15, 2011

bench3

Join Text, Date, Currency and Numbers In Excel

Excel is an electronic spread sheet program that can be used for storing, organizing and manipulating data. Excel allows one to enter numerical values or data into the rows or columns of a spread sheet, and to use these numerical entries for such things as calculations, graphs, and statistical analysis.

You know that Excel is great at calculating numbers, but did you know that Excel can also perform calculations on text as well?

In Figure below, you have a data set with first names in column A and last names in column B. You would like to join these together into a single cell in column C. Ideally, you would like the names in proper case instead of upper case.

Steps To Join Text In Excel 1

You don’t need to re-type the names in order to join them in column C. The solution is a big word called concatenation. While you use a plus sign to add numbers, you use an ampersand to join text entries.

A formula of =A2&B2 would provide the result of DENNISRAYMOND. Since you probably want a space between the names, you will use one ampersand to join A2 with a space and then another ampersand to join B2 (=A2&“ ”&B2).

In the figure below, the names are joined together, but they are still in upper case.

Steps To Join Text In Excel 2

The formula joins A2, a space, and B2. In order to convert the text to proper case, wrap the formula in the PROPER function. This function will capitalize the first character and every character that does not follow a letter. This rule works almost perfectly in the below figure – the “C” in Campbell in C22 is capitalized and the “N” in O’Neal in C23 is capitalized. You will have to go through manually looking for improper capitalization – for example, the “K” in McKenzie in C24 is not properly capitalized.

Steps To Join Text In Excel 3

=PROPER(A22&” ”&B22)

The PROPER function works well, except when an interior letter should be capitalized, as in McKenzie.

You might be tempted to delete columns A and B after joining the text in column C. Before you can do this, you must convert the formulas in column C to values. Select the range of data in column C. Use Ctrl+C to copy. Then choose Home – Paste – Paste Values to convert the formulas to their current value. There is a particular problem when you attempt to join text with a cell that is formatted as a date or as currency. Although cell F11 in Figure below is nicely formatted, you lose the formatting when you join text with cell F11, as shown in cell A13.

Steps To Join Text In Excel 4

When you join text with dates or currency, the result is an unformatted number.

The problem is worse when you join text and a date. The formatted date is converted back to a serial number. Most of your customers will not understand that a date 39,551 days after January 1, 1900 is in April 2008.

To solve the problem, you need to use the TEXT function. The first argument of the  TEXT function is a date or a number. The second function is a custom number formatting code.

The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 23.5. To format the number as a dollar amount, you can use the following formula:

Steps To Join Text In Excel 5

The red italic text in A14 is the formula used to generate A13.

The corrected formula in A13 is =“Please remit “&TEXT(F11,”$#,##0.00”)&” on or before “&TEXT(C3+C4,”dddd mmmm d, yyyy”). The difficult part of this formula is figuring out the proper custom numeric formatting code.

If you have a cell that is already properly formatted, you can discover the code for that cell. For example, select cell F11 in Figure below. Type Ctrl+1 to display the Format Cells dialog. Choose the Number tab, and then click on Custom in the Category list. The Type: box changes to display the custom number format.

You can select those characters, type Ctrl+C to copy, and then later paste them inside of quotes as the second argument of the TEXT function.

Steps To Join Text In Excel 6

After choosing the Custom category, the actual numeric formatting code for the selected cell appears in the Type: box.

bench3

About bench3 -

Haja Peer Mohamed H, Software Engineer by profession, Author, Founder and CEO of "bench3" you can connect with me on Twitter , Facebook and also onGoogle+

Subscribe to this Blog via Email :