Formulas & Functions Excel 2007 - 7. Working with Text Functions

[BNTT]

7.9. Substituting One Substring for Another


7.9.2. The SUBSTITUTE Function — Hàm SUBSTITUTE

These extra steps make the REPLACE() function unwieldy, so most people use the more straightforward SUBSTITUTE() function:
Các bước phụ vừa nói ở bài trước làm cho hàm REPLACE() trở nên cồng kềnh, do đó hầu hết người ta sẽ sử dụng hàm SUBSTITUTE(), một hàm đơn giản hơn:

SUBSTITUTE(text, old_text, new_text [, instance_num])

In the example from the previous section, the following simpler formula does the same thing:
Trong ví dụ ở bài trước, công thức đơn giản hơn sau đây cũng làm được điều tương tự:

=SUBSTITUTE(A1, "2007", "2008")​

Viết thêm một chút.

Ở bài 7.8.4. (Xác định mẫu tự của cột), bạn đã học một công thức để biết được ký tự của cột đang chứa ô có công thức. Nhưng công thức đó quá dài dòng:

=MID(CELL("address"), 2, FIND("$", CELL("address"), 3) - 2)

Nếu sử dụng hàm SUBSTITUTE() vừa mới học đây, kết hợp với hàm ADDRESS(), một hàm cung cấp cho ta địa chỉ của một ô, và hàm COLUMN(), hàm cho biết số thứ tự của một cột, chúng ta có công thức sau đây, cũng trả về tên của cột đang chứa công thức, nhưng gọn hơn:

=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), 1, "")​

​

 

[BNTT]

7.9.2. The SUBSTITUTE Function


Removing a Character from a String
Gỡ bỏ một ký tự ra khỏi một chuỗi

Earlier, you learned about the CLEAN() function, which removes nonprintable characters from a string, as well as the TRIM() function, which removes excess spaces from a string. A common text scenario involves removing all instances of a particular character from a string. For example, you might want to remove spaces from a string or apostrophes from a name.
Ở phần trước, bạn đã học về hàm CLEAN(), gỡ bỏ những ký tự không in ra được trong một chuỗi, cũng giống như hàm TRIM() loại bỏ những khoảng trắng không cần thiết ra khỏi một chuỗi. Có một tình huống thông thường khác khi xử lý văn bản, đòi hỏi loại bỏ một ký tự cụ thể nào đó ra khỏi chuỗi. Ví dụ, bạn có thể muốn loại bỏ tất cả các khoảng trắng ra khỏi một chuỗi, hoặc loại bỏ dấu nháy đơn (') ra khỏi một cái tên...

Here’s a generic formula that does this:
Đây là một công thức chung để làm điều đó:

=SUBSTITUTE(text, character, "")

Here, replace text with the original string and character with the character you want to remove. For example, the following formula removes all the spaces from the string in cell A1:
Ở đây, chúng ta thay thế những ký tự character ra khỏi chuỗi text bằng một ký tự "" (hay nói cách khác, là xóa đi tất cả các character ở trong text). Ví dụ, công thức sau đây loại bỏ tất cả những khoảng trắng ra khỏi chuỗi ở ô A1:

=SUBSTITUTE(A1, " ", "")

NOTE: One surprising use of the SUBSTITUTE() function is to count the number of characters that appear in a string. The trick here is that if you remove a particular character from a string, the difference in length between the original string and the resulting string is the same as the number of times the character appeared in the original string.
Một điều ứng dụng lý thú của hàm SUBSTITUTE() là dùng để đếm số lấn xuất hiện của một ký tự trong một chuỗi. Thủ thuật ở đây là khi bạn gỡ bỏ một số ký tự đặc biệt ra khỏi một chuỗi, thì sự lệch nhau giữa độ dài của chuỗi gốc và chuỗi ở kết quả trả về chính là số lần xuất hiện của ký tự đã được gỡ bỏ trong chuỗi gốc.

For example, the string expenses has eight characters. If you remove all the e’s, the resulting string is xpnss, which has five characters. The difference is 3, which is how many e’s there were in the original string.
Ví dụ, chuỗi "expenses" có 8 ký tự. Nếu bạn bỏ đi những chữ e, thì kết quả bạn có là "xpnss", có 5 ký tự. Sự khác nhau về độ dài của hai chuỗi là 3, và đó chính là số lần xuất hiện của chữ "e" trong chuỗi gốc.

To calculate this in a formula, use the LEN() function and subtract the length of a string with the character removed from the length of the original string. Here’s the formula that counts the number of e’s for a string in cell A1:
Để tính toán trong một công thức, bạn sử dụng hàm LEN() và hiệu số giữa chuỗi gốc với chuỗi sau khi đã gỡ bỏ đi những ký tự. Sau đây là công thức đếm số chữ "e" trong chuỗi ở ô A1:

=LEN(A1) - LEN(SUBSTITUTE(A1, "e", ""))​

Removing Two Different Characters from a String
Gỡ bỏ hai ký tự khác nhau ra khỏi một chuỗi

It’s possible to nest one SUBSTITUTE() function inside another to remove two different characters from a string. For example, first consider the following expression, which uses SUBSTITUTE() to remove periods from a string:Có thể lồng hai hàm SUBSTITUTE() với nhau để gỡ bỏ hai ký tự khác nhau ra khỏi một chuỗi. Ví dụ, bạn xem biểu thức sau đây, dùng hàm SUBSTITUTE() để gỡ bỏ các dấu chấm (.) ra khỏi một chuỗi:

SUBSTITUTE(A1, ".", "")

Because this expression returns a string, you can use that result as the text argument in another SUBSTITUTE() function. Here, then, is a formula that removes both periods and spaces from a string in cell A1:
Bởi vì kết quả của biểu thức trên cũng là một chuỗi, nên bạtn có thể sử dụng kết quả đó như là đối số text của một hàm SUBSTITUTE() khác. Và đây là một công thức vừa gỡ bỏ các dấu chấm vừa gỡ bỏ các khoảng trắng ra khỏi một chuỗi ở ô A1:

=SUBSTITUTE(SUBSTITUTE(A1, ".", ""), " ", "")​

Removing Line Feeds
Gỡ bỏ những dấu ngắt dòng (line feed)

Earlier in this chapter, you learned about the CLEAN() function, which removes nonprintable characters from a string. In the example, I used CLEAN() to remove the line feeds from a multiline cell entry. However, you might have noticed a small problem with the result: There was no space between the end of one line and the beginning of the next line (see Figure 7.6).
Trong chương trước, bạn đã học về hàm CLEAN(), dùng để gỡ bỏ những ký tự không in ra được trong một chuỗi. Trong ví dụ, tôi đã sử dụng hàm CLEAN() để gỡ bỏ những dấu ngắt dòng trong một ô có nhiều dòng. Tuy nhiên, có lẽ bạn nhận thấy một vấn đề nhỏ ở kết quả: chẳng có khoảng trắng nào nằm giữa phần cuối ở một dòng và phần đầu của dòng tiếp theo (xem lại hình 7.6).

If all you’re worried about is line feeds, use the following SUBSTITUTE() formula instead of the CLEAN() function:
Nếu những gì mà bạn quan tâm là những dấu ngắt dòng, bạn nên sử dụng hàm SUBSTITUTE() thay vì hàm CLEAN():

=SUBSTITUTE(A2, CHAR(10), " ")

This formula replaces the line feed character (ANSI code 10) with a space, resulting in a proper string, as shown in Figure 7.14.
Công thức này thay thế những dấu ngắt dòng (có mã ANSI 10) bằng những khoảng trắng, và trả về kết quả là một chuỗi thích hợp (không bị dính nhau), như minh họa ở hình 7.14.

Figure 7.14 - Text Functions.xlsx​

 

[BNTT]

Case Study


7.10. Generating Account Numbers, Part 2
Tạo các số tài khoản, phần 2

The formula I showed you earlier for automatically generating account numbers from an account name produces valid numbers only if the first three letters of the name are letters. If you have names in which characters other than letters appear, you need to remove those characters before generating the account number.
Công thức mà tôi đã chỉ cho bạn trong phần trước để tự động tạo những số tài khoản từ một tên tài khoản, chỉ tạo ra các số hợp lệ khi ba ký tự đầu tiên của tên tài khoản là những chữ cái. Nếu bạn có những cái tên mà ngoài những chữ cái còn có những ký tự khác, bạn cần phải gỡ bỏ những ký tự (không phải là chữ cái này) trước khi tạo số tài khoản.

For example, if you have account names such as J.D. BigBelly, you need to remove periods and spaces before generating the account name. You can do this by adding the expression from the previous section to the formula for generating an account name from earlier in this chapter. Specifically, you replace the cell address in the LEFT() with the nested SUBSTITUTE() functions, as shown in Figure 7.15. Notice that the formula still works on account names that begin with three letters.
Ví dụ, giả sử bạn có những cái tên tài khoản như là J.D. BigBelly, ban cần phải bỏ bớt mấy cái dấu chấm và khoảng trắng trước khi tạo số tài khoản. Bạn có thể làm điều đó bằng cách thêm một biểu thức ở trước công thức tạo số tài khoản mà tôi đã nói ở phần trước. Cụ thể, bạn thay thế địa chỉ ô trong hàm LEFT() bằng cách lồng thêm hàm SUBSTITUTE() vào, như minh họa ở hình 7.15. Lưu ý rằng công thức này vẫn làm việc bình thường với những tên tài khoản bắt đầu với 3 chữ cái.

Figure 7.15 - Text Functions.xlsx​

----------------------------------------- End of Chapter 7 -----------------------------------------​