tailieunhanh - formulas and functions with microsoft excel 2003 phần 3

Bảng chứa các giá trị trong cột A mà không thể được tổng hợp bởi vì các ký tự đầu tiên trong mỗi tế bào là một dấu nháy đơn. Làm thế nào để bạn giải quyết vấn đề này? Sử dụng công thức THAY THẾ để thay thế các ký tự cụ thể trong văn bản hoặc một tế bào. THAY THẾ | Use the SUBSTITUTE function to substitute characters A worksheet contains values in column A that cannot be summed up because the first character in each cell is an apostrophe. How do you solve this problem Use the SUBSTITUTE formula to replace specific characters in text or a cell. SUBSTITUTE text old_text new_text instance_num text The text or the reference to a cell containing text in which characters are substituted. old_text Text that should be replaced. new_text Text that replaces old_text. instance_num Specifies which instance of old_text is to be replaced by new_text. If omitted every instance of old_text is replaced. To use SUBSTITUTE and force Excel to calculate 1. Format column A as text. 2. Enter a series of numbers in cells A2 A10. Notice that Excel tags them with green triangles in the upper-left corner to indicate the numbers have been entered as text. 3. Select cells B2 B10 and type the following formula VALUE SUBSTITUTE A2 . . 4. Press Ctrl Enter . 5. Select cell A12 type the following formula SUM A2 A10 and press Enter . 6. Select cell B12 type the following formula SUM B2 B10 and press Enter . Text Functions 65 B12 V A- SUM B2 B10 A B c D 1 old values new values 2 8607 6607 3 4 Ì298Ũ 12980 5 Ì 6 7 8 567912 567912 9 10 11 12 0 746671 4l 13 Figure 3-7 66 Chapter 3 Use the SUBSTITUTE function to substitute parts of a cell In this example the - character needs to be replaced with a blank space. But only the first occurrence of this character should be replaced. To do this type any kind of text and numbers in column A as shown in the screenshot below using the - character in different positions and in a variety of occurrences. To substitute parts of a cell 1. Select cells B2 B10 and type the following formula SUBSTITUTE A2 - 1 . 2. Press Ctrl Enter . B2 V__A- SUBSTITUTE A2 - 1 A B c D 1 old new 2 OW-345-XY OW345-XY 3 AW-111-B41 AW111-B41 4 AW-111-B9