Formulas & Functions Excel 2007 - 7. Working with Text Functions (1 người xem)

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part II: HARNESSING THE POWER OF FUNCTIONS - Tận dụng sức mạnh của các hàm

• Chapter 6: Understanding Functions - Tìm hiểu các hàm
  
  
• Chapter 7: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản
  
  
• Chapter 8: Working with Logical and Information Functions - Làm việc với các hàm luận lý và tra cứu thông tin
  
  
• Chapter 9: Working with Lookup Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11284 - Làm việc với các hàm tìm kiếm
  
  
• Chapter 10: Working with Date and Time Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11365 - Làm việc với các hàm ngày tháng và thời gian
  
  
• Chapter 11: Working with Math Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11551 - Làm việc với các hàm toán học
  
  
• Chapter 12: Working with Statistical Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11623 - Làm việc với các hàm thống kê

---------------------------------------------------------------------------------------



PART II - HARNESSING THE POWER OF FUNCTIONS
Phần II - Tận dụng sức mạnh của các hàm


Chapter 7 - WORKING WITH TEXT FUNCTIONS
Chương 7 - Làm việc với các hàm xử lý chuỗi văn bản

In Excel, text is any collection of alphanumeric characters that isn’t a numeric value, a date or time value, or a formula. Words, names, and labels are all obviously text values, but so are cell values preceded by an apostrophe (‘) or formatted as Text. Text values are also called strings, and I’ll use both terms interchangeably in this chapter.
Trong Excel, văn bản là một tập hợp gồm những ký tự chữ và số nhưng không phải là một giá trị số, một giá trị ngày tháng và thời gian, hoặc là một công thức. Các từ, các tên, các nhãn, đều là những giá trị text, và tất cả các giá trị trong một ô mà có dấu nháy đơn (') đứng ở trước cũng là những giá trị text. Các giá trị văn bản (text value) còn được gọi là các chuỗi (string), và tôi sẽ dùng cả hai thuật ngữ đó thay thế cho nhau trong suốt chương này.

In Chapter 3, “Building Basic Formulas,” you learned about building text formulas in Excel — not that there was much to learn. Text formulas consist only of the concatenation operator (&) used to combine two or more strings into a larger string.
Ở chương 3, "Thiết lập những công thức (cơ bản)", bạn đã học về các thiết lập những công thức xử lý văn bản trong Excel, nhưng chưa học được bao nhiêu, mới chỉ là các công thức dùng dấu "và" (&) để nối hai hoặc nhiều chuỗi nhỏ thành một chuỗi lớn hơn.

Excel’s text functions enable you to take text formulas to a more useful level by giving you numerous ways to manipulate strings. With these functions, you can convert numbers to strings, change lowercase letters to uppercase (and vice versa), compare two strings, and more.
Các hàm xử lý văn bản trong Excel cho phép bạn đưa các công thức liên quan đến văn bản lên một cấp độ hữu dụng hơn, bằng việc cho bạn rất nhiều cách để xử lý các chuỗi. Với những hàm này, bạn có thể chuyển đổi một con số thành một chuỗi, đổi những chữ thường thành những chữ hoa (và ngược lại), so sánh hai chuỗi, và nhiều điều hơn nữa.​

IN THIS CHAPTER
Trong chương này:

1. Excel’s Text Functions
   Các hàm xử lý chuỗi văn bản trong Excel
   
   
2. Working with Characters and Codes
   Làm việc với các ký tự và mã ký tự (code)
   
   
3. Converting Text
   Chuyển đổi kiểu chữ trong văn bản
   
   
4. Formatting Text
   Định dạng văn bản
   
   
5. Manipulating Text
   Xử lý các chuỗi văn bản
   
   
6. Removing Unwanted Characters from a String
   Loại bỏ các ký tự không mong muốn ra khỏi một chuỗi văn bản
   
   
7. Extracting a Substring
   Trích xuất một chuỗi con
   
   
8. Generating Account Numbers
   Tạo các số tài khoản
   
   
9. Searching for Substrings
   Tìm kiếm một chuỗi con
   
   
10. Substituting One Substring for Another
    Thay thế một chuỗi con bằng một chuỗi khác

You can download the workbook that contains this chapter’s examples here:
Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây:

www.mcfedries.com/Excel2007Formulas/​

Trong chương này, do hầu hết các hàm tôi đã trình bày chi tiết ở topic Các hàm xử lý văn bản và chuỗi, nên tôi sẽ không trình bày lại cú pháp và chú giải các đối số của hàm nữa (không theo như nguyên bản cuốn sách này). Trong các bài dịch tiếp theo đây, khi nói đến một hàm nào, tôi sẽ tạo liên kết (link) đến bài viết về hàm đó. Nếu muốn tìm hiểu kỹ hơn về cú pháp và cách sử dụng các đối số (argument), các bạn theo những liên kết này để xem.

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.1. Excel’s Text Functions
Các hàm xử lý văn bản trong Excel

Table 7.1 summarizes Excel’s text functions, and the rest of this chapter gives you the details and example uses for most of them.
Bảng 7.1. tóm tắt các hàm xử lý văn bản trong Excel, và phần còn lại của chương này sẽ trình bày chi tiết và những công dụng mẫu của hầu hết các hàm này.


Table 7.1 - Excel’s Text Functions
Bảng 7.1 - Các hàm văn bản trong Excel

• CHAR (number) : Returns the character that corresponds to the ANSI code given by number.
  Trả về một ký tự tương ứng với mã ANSI đã được cho bởi number.
  
  
• CLEAN (text) : Removes all nonprintable characters from text.
  Loại bỏ tất cả những ký tự không in ra được trong chuỗi text.
  
  
• CODE (text) : Returns the ANSI code for the first character in text.
  Trả về mã ANSI của ký tự đầu tiên trong chuỗi text.
  
  
• CONCATENATE (text1 [, text2], ...) : Joins the specified strings into a single string.
  Nối nhiều chuỗi đã xác định thành một chuỗi đơn.
  
  
• DOLLAR (number [, decimals]) : Converts number to a string that uses the Currency format.
  Chuyển đổi number thành một chuỗi sử dụng định dạng kiểu Currency (tiền tệ).
  
  
• EXACT (text1, text2) : Compares two strings to see whether they are identical.
  So sánh hai chuỗi để xem chúng có giống nhau hay không.
  
  
• FIND (find_text, within_text [, start_num]) : Returns the character position of the text find_text within the text within_text. FIND() is case sensitive.
  Trả về vị trí bắt đầu của chuỗi find_text trong chuỗi within_text. FIND() phân biệt chữ thường và chữ hoa.
  
  
• FIXED (number [, decimals] [, no_commas]) : Converts number to a string that uses the Number format.
  Chuyển đổi con số number thành một chuỗi, sử dụng loại định dạng Number.
  
  
• LEFT (text [, num_chars]) : Returns the leftmost num_chars characters from text.
  Trả về một hay nhiều ký tự đầu tiên bên trái của chuỗi text
  
  
• LEN (text) : Returns the length of text.
  Trả về độ dài (số ký tự) của chuỗi text.
  
  
• LOWER (text) : Converts text to lowercase.
  Chuyển đổi tất cả các ký tự trong chuỗi text thành chữ thường.
  
  
• MID (text, start_num, num_chars) : Returns num_chars characters from text starting at start_num.
  Trả về num_chars ký tự của chuỗi text, bắt đầu tại vị trí start_num.
  
  
• PROPER (text) : Converts text to proper case (first letter of each word is capitalized).
  Đổi ký tự đầu tiên trong chuỗi text thành chữ in hoa, và đổi các ký tự còn lại thành chữ in thường.
  
  
• REPLACE (old_text, start_num, num_chars, new_text) : Replaces the old_text string with the new_text string.
  Thay thế một phần của chuỗi old_text bằng chuỗi new_text, với số lượng các ký tự được chỉ định.
  
  
• REPT (text, times) : Repeats text time times.
  Lặp lại chuỗi text với time lần.
  
  
• RIGHT (text [, num_chars]) : Returns the rightmost num_chars characters from text.
  Trả về một hay nhiều ký tự tính từ bên phải của một chuỗi text.
  
  
• SEARCH (find_text, within_text [, start_num]) : Returns the character position of the text find_text within the text within_text. SEARCH() is not case sensitive.
  Tìm vị trí bắt đầu của chuỗi con find_text trong chuỗi within_text. SEARCH() không phân biệt chữ thường, chữ hoa.
  
  
• SUBSTITUTE (text, old_text, new_text [, instance_num]) : In text, substitutes the new_text string for the old_text string instance_num times.
  Thay thế chuỗi old_text trong chuỗi text bằng chuỗi new_text, instance_num lần.
  
  
• T (value) : Converts value to text.
  Chuyển đổi value thành một chuỗi.
  
  
• TEXT (value, format_text) : Formats value and converts it to text.
  Định dạng value và chuyển đổi nó thành chuỗi.
  
  
• TRIM (text) : Removes excess spaces from text.
  Xóa tất cả những khoảng trắng vô ích trong chuỗi text.
  
  
• UPPER (text) : Converts text to uppercase.
  Chuyển đổi tất cả các ký tự trong chuỗi text thành chữ in hoa.
  
  
• VALUE (text) : Converts text to a number.
  Chuyển đổi chuỗi text thành một số.

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.2. Working with Characters and Codes
Làm việc với các ký tự và mã ký tự

Every character that you can display on your screen has its own underlying numeric code. For example, the code for the uppercase letter A is 65, whereas the code for the ampersand (&) is 38. These codes apply not only to the alphanumeric characters accessible via your keyboard, but also to extra characters that you can display by entering the appropriate code. The collection of these characters is called the ANSI character set, and the numbers assigned to each character are called the ANSI codes.
Mỗi ký tự mà bạn có thể hiển thị lên màn hình của bạn đều mang một mã số (numeric code) riêng của nó. Ví dụ, mã số của chữ A (hoa) là 65, mã số cho dấu và (&) là 38. Những mã số áp dụng không chỉ cho những ký tự có thể nhập từ bàn phím, mà còn cho cả những ký tự mở rộng mà bạn có thể hiển thị bằng cách nhập những mã thích hợp. Tập hợp những ký tự này được gọi là tập hợp ký tự ANSI, và những con số được gán cho mỗi ký tự được gọi là mã ANSI.

For example, the ANSI code for the copyright character (©) is 169. To display this character, press Alt+0169, where you use your keyboard’s numeric keypad to enter the digits (always including the leading zero for codes higher than 127).
Ví dụ, mã ANSI của ký tự bản quyền © là 169. Để hiển thị ký tự này, bạn nhấn Alt+0169, với những phím mà bạn thường dùng để nhập những con số ở nhóm phím số (luôn luôn thêm số 0 ở trước cho những mã lớn hơn 127).

The ANSI codes run from 1 to 255, although the first 31 codes are nonprinting codes that define characters such as carriage returns and line feeds.
Bảng mã ANSI chạy từ 1 đến 255, tuy nhiên 31 mã đầu tiên là những mã ký tự không in ra được như những mã ký tự trở lui lại đầu dòng (carriage return), những mã ký tự xuống hàng (line feed).​

 

[BNTT]

7.2. Working with Characters and Codes


7.2.1. The CHAR Function — Hàm CHAR

Excel enables you to determine the character represented by an ANSI code using the CHAR() function:
Excel cho phép bạn chuyển đổi một mã số ANSI thành một ký tự tương ứng, bằng cách sử dụng hàm CHAR():

CHAR(number)

For example, the following formula displays the copyright symbol (ANSI code 169):
Ví dụ, công thức sau đây hiển thị ký hiệu bản quyền (có mã ANSI là 169):

=CHAR(169)​

Generating the ANSI Character Set
Tạo tập hợp ký tự ANSI

Figure 7.1 shows a worksheet that displays the entire ANSI character set (excluding the first 31 nonprinting characters — note, too, that ANSI code 32 represents the space character). In each case, the character is displayed by applying the CHAR() function to the value in the cell to the left.
Hình 7.1 minh họa một bảng tính hiển thị tập hợp các ký tự ANSI (ngoại trừ 31 ký tự không in ra được — lưu ý thêm: mã ANSI 32 đại diện cho một ký tự trống, một khoảng trắng). Trong mỗi trường hợp, ký tự được hiển thị bằng cách áp dụng hàm CHAR() cho giá trị của ô bên trái.

Figure 7.1 - Text Functions.xlsx

NOTE: The actual character displayed by an ANSI code depends on the font applied to the cell. The characters shown in Figure 7.1 are the ones you see with normal text fonts, such as Arial. However, if you apply a font such as Symbol or Wingdings to the worksheet, you’ll see a different set of characters.
Ký tự thực tế hiển thị bởi một mã ANSI phụ thuộc vào loại font được áp dụng cho ô. Những ký tự minh họa ở hình 7.1 mà bạn thấy là một trong những loại font bình thường, như Arial chẳng hạn. Tuy nhiên, nếu bạn áp dụng một font như Symbol hoặc Wingdings, bạn sẽ thấy một tập hợp những ký tự khác.

To build the character set shown in Figure 7.1, I entered the ANSI code and CHAR() function at the top of each column, and then filled down to generate the rest of the column.
Để làm được bảng tập hợp các ký tự như minh họa ở hình 7.1, tôi nhập mã ANSI và hàm CHAR() và đầu của mỗi cột, và rồi kéo xuống (fill down) để tạo ra phần còn lại của cột.

A less tedious method takes advantage of the ROW() function, which returns the row number of the current cell. Assuming that you want to start your table in row 2, you can generate any ANSI character by using the following formula:
Một phương pháp ít tốn thời gian hơn là tận dụng hàm ROW(), là hàm trả về số thứ tự hàng của ô hiện hành. Giả sử bạn muốn bắt đầu bảng này ở hàng 2, bạn có thể tạo bất kỳ ký tự ANSI nào bằng công thức sau đây:

=CHAR(ROW() + 30)

Figure 7.2 shows the results. The values in column A are generated using the formula:
Hình 7.2 minh họa những kết quả. Các giá trị trong cột A được tạo bằng công thức:

=ROW() + 30

Figure 7.2 - Text Functions.xlsx​

(To be continued)

 

[BNTT]

7.2.1. The CHAR() Function

(continue)

Generating a Series of Letters
Tạo một chuỗi ký tự liên tục

Excel’s Fill handle and Home, Fill, Series command are great for generating a series of numbers or dates, but they don’t do the job when you need a series of letters (such as a, b, c, and so on). However, you can use the CHAR() function in an array formula to generate such a series.
Công cụ Fill handle của Excel và lệnh Home/Fill/Series là công cụ tuyệt vời cho việc tạo ra một chuỗi liên tục các con số hoặc ngày tháng, nhưng nó không thể làm công việc tạo ra một chuỗi liên tục các ký tự (như a, b, c, v.v...). Tuy nhiên, bạn có thể dùng hàm CHAR() trong một công thức mảng để tạo ra một chuỗi như vậy.

We’re concerned with the characters a through z (which correspond to ANSI codes 97 to 122), and A through Z (codes 65 to 90). To generate a series of these letters, follow these steps:
Chúng ta quan tâm đến những ký tự từ a đến z (tương ứng với các mã ANSI từ 97 đến 122), và từ A đến Z (mã 65 đến 90). Để tạo một chuỗi liên tục các ký tự, làm theo các bước sau:

1. Select the range you want to use for the series.
   Chọn dãy mà bạn muốn tạo chuỗi này.
   
   
2. Activate in-cell editing by pressing F2.
   Kích hoạt chức năng sửa trực tiếp trong ô bằng cách nhấn phím F2.
   
   
3. Type the following formula:
   Nhập công thức sau:
   
   =CHAR(97 + ROW(range) - ROW(first_cell))
   
   In this formula, range is the range you selected in step 1, and first_cell is a reference to the first cell in range. For example, if the selected range is B10:B20, you would type this:
   Trong công thức này, range là dãy mà bạn đã chọn ở bước 1, và first_cell là một tham chiếu đến ô đầu tiên trong range. Ví dụ, nếu dãy được chọn là B10:B20, bạn sẽ nhập như vầy:
   
   =CHAR(97 + ROW(B10:B20) - ROW(B10))
   
   

   NOTE: I’m assuming that you’ve selected a column for your series. If you’ve selected a row, replace the ROW() functions in the formula with COLUMN().
   Tôi đang giả sử rằng bạn chọn một cột cho chuỗi này. Nếu bạn chọn một hàng, thì thay thế hàm ROW() trong công thức bằng hàm COLUMN().

4. Press Ctrl+Shift+Enter to enter the formula as an array.
   Nhấn Ctrl+Shift+Enter để nhập công thức dưới dạng một mảng.

Because you entered this as an array formula, the ROW(range) - ROW(first_cell) calculation generates a series of numbers (0, 1, 2, and so on) that represent the offset of each cell in the range from the first cell. These offsets are added to 97 to produce the appropriate ANSI codes for the lowercase letters, as shown in Figure 7.3. If you want uppercase letters, replace the 97 with 65 (in Figure 7.3, see the series in row 12).
Bởi vì bạn nhập công thức này ở dạng công thức mảng, cho nên phép tính ROW(range) - ROW(first_cell) sẽ tạo một chuỗi số (0, 1, 2, v.v...) tượng trưng cho khoảng dịch chuyển của mỗi ô so với ô đầu tiên trong dãy. Những khoảng dịch chuyển này cộng thêm 97 để tạo ra các mã ANSI thích hợp cho các ký tự viết ở dạng chữ thường, như minh họa ở hình 7.3. Nếu bạn muốn các chữ hoa, thay thế con số 97 bằng con số 65 (trong hình 7.3, xem chuỗi ở hàng 12).

Figure 7.3 - Text Functions.xlsx​

 

[BNTT]

7.2. Working with Characters and Codes


7.2.2. The CODE Function — Hàm CODE

The CODE() function is the opposite of CHAR(). That is, given a text character, CODE() returns its ANSI code value:
Hàm CODE() trái ngược với hàm CHAR(). Nghĩa là, cho trước một ký tự, CODE() trả về giá trị mã ANSI của ký tự đó.

CODE(text)

For example, the following formulas both return 83, the ANSI code of the uppercase letter S:
Ví dụ, cả hai công thức sau đây đều trả về 83, là mã ANSI của chữ S (viết hoa).

=CODE("S")

=CODE("Spacely Sprockets")​

Generating a Series of Letters Starting from Any Letter
Tạo một chuỗi các ký tự liên tục bắt đầu bằng bất kỳ ký tự nào

Earlier in this section, you learned how to combine CHAR() and ROW() in an array formula to generate a series of letters beginning with the letters a or A. What if you prefer a different starting letter? You can do that by changing the initial value that plugged into the CHAR() function before the offsets are calculated. I used 97 in the previous example to begin the series with the letter a, but you could use 98 to start with b, 99 to start with c, and so on.
Trong phần trước, bạn đã học cách kết hợp giữa hàm CHAR() và hàm ROW() trong một công thức mảng để tạo một chuỗi lien tục các ký tự bắt đầu bằng ký tự a hoặc ký tự A. Nếu bạn muốn bắt đầu bằng một ký tự khác thì sao? Bạn có thể làm bằng cách thay đổi giá trị ban đầu đã được đưa vào hàm CHAR() trước khi nó tính toán các khoảng dịch chuyển. Trong ví dụ trước tôi đã dùng 97 để bắt đầu chuỗi liên tục với ký tự a, nhưng bạn có thể dùng 98 để bắt đầu với b, hoặc 99 để bắt đầu với c, v.v...

Instead of looking up the ANSI code of the character you prefer, however, use the CODE() function to have Excel do it for you:
Thay vì phải tìm mã ANSI cho ký tự mà bạn thích dùng để bắt đầu cho chuỗi liên tục các ký tự, bạn sử dụng hàm CODE() để Excel làm điều đó dùm cho:

=CHAR(CODE("letter") + ROW(range) - ROW(first_cell))

Here, replace letter with the letter you want to start the series with. For example, the following formula begins the series with uppercase N (remember to enter this as an array formula in the specified range):
Ở đây, bạn thay thế letter bằng ký tự mà bạn muốn dùng để bắt đầu cho chuỗi liên tục. Ví dụ, công thức sau đây bắt đầu chuỗi liên tục với ký tự N (bạn nhớ nhập công thức này với dạng một công thức mảng trong một dãy đã xác định trước):

=CHAR(CODE("N") + ROW(A1:A13) - ROW(A1))​

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.3. Converting Text
Chuyển đổi kiểu chữ trong văn bản

Excel’s forte is number crunching, so it often seems to give short shrift to strings, particularly when it comes to displaying strings in the worksheet. For example, concatenating a numeric value into a string results in the number being displayed without any formatting, even if the original cell had a numeric format applied to it. Similarly, strings imported from a database or text file can have the wrong case or no formatting.
Sở trường của Excel là xử lý số, do đó dường như các chuỗi thường bị Excel đối xử không công bằng, đặc biệt là khi nó hoàn thiện các chuỗi trong bảng tính. Ví dụ, việc ghép các giá trị số vào trong một chuỗi làm cho nó chỉ hiển thị mà không có bất kỳ định dạng nào, ngay cả khi ô gốc đã được áp dụng một kiểu định dạng số. Tương tự, các chuỗi nhập vào từ một cơ sở dữ liệu hoặc từ một tập tin văn bản có thể có kiểu chữ sai hoặc không có định dạng.

However, as you’ll see over the next few sections, Excel offers a number of worksheet functions that enable you to convert strings to a more suitable text format, or to convert between text and numeric values.
Tuy nhiên, như bạn sẽ thấy trong các phần tiếp theo đây, Excel cũng có cung cấp một số hàm cho phép bạn chuyển đổi các chuỗi thành một định dạng văn bản thích hợp hơn, hoặc chuyển đổi giữa các giá trị text và các giá trị số.​

7.3.1. The LOWER Function — Hàm LOWER

The LOWER() function converts a specified string to all-lowercase letters:
Hàm LOWER() chuyển đổi một chuỗi xác định thành một chuỗi gồm toàn chữ thường.

LOWER(text)

For example, the following formula converts the text in cell B10 to lowercase:
Ví dụ, công thức sau đây chuyển đổi những chữ trong ô B10 trở thành chữ thường:

=LOWER(B10)

The LOWER() function is often used to convert imported data, particularly data importedfrom a mainframe computer, which often arrives in all-uppercase characters.
Hàm LOWER() thường được sử dụng để chuyển đổi dữ liệu nhâp từ ngoài vào, đặc biệt là dữ liệu được nhập từ một máy tính "mainframe", thường chứa toàn những chữ viết hoa. ​

7.3.2. The UPPER Function — Hàm UPPER

The UPPER() function converts a specified string to all-uppercase letters:
Hàm UPPER() chuyển đổi một chuỗi xác định thành một chuỗi gồm toàn chữ hoa.

UPPER(text)

For example, the following formula converts the text in cells A5 and B5 to uppercase and concatenates the results with a space between them:
Ví dụ, công thức sau đây chuyển đổi những chữ trong ô A5 và B5 trở thành chữ hoa hết và nối chúng lại với một khoảng trắng ở giữa chúng:

=UPPER(A5) & " " & UPPER(B5)​

7.3.3. The PROPER Function — Hàm PROPER

The PROPER() function converts a specified string to proper case, which means the first letter of each word appears in uppercase and the rest of the letters appear in lowercase:
Hàm PROPER() chuyển đổi một chuỗi xác định thành dạng viết hoa chữ đầu (proper case), nghĩa là ký tự đầu tiên của mỗi chữ sẽ được viết hoa và phần còn lại của chữ thì viết thường.

PROPER(text)

For example, the following formula, entered as an array, converts the text in the range A1:A10 to proper case:
Ví dụ, công thức sau đây, nhập ở dạng công thức mảng, chuyển đổi các chữ trong dãy A1:A10 thành dạng proper case:

=PROPER(A1:A10)​

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.4. Formatting Text
Định dạng văn bản

You learned in Chapter 3 that you can enhance the results of your formulas by using built-in or custom numeric formats to control things such as commas, decimal places, currency symbols, and more. That’s fine for cell results, but what if you want to incorporate a result within a string? For example, consider the following text formula:
Bạn đã học ở Chương 3 rằng bạn có thể cải tiến kết quả của các công thức bằng cách dùng các định dạng số có sẵn hoặc tùy biến để kiểm soát những thứ như dấu phẩy, dấu thập phân, đơn vị tiền tệ, v.v... Với những kết quả bình thường trong những ô thì điều đó thực hiện được, nhưng với các kết quả ở bên trong một chuỗi thì sao? Ví dụ, bạn xem công thức sau đây:

➔ For the details on numeric formats, see "Numeric Display Formats".
Xem bài "Các định dạng hiển thị số" để biết thêm chi tiết về những định dạng số.​

="The expense total for this quarter in 2007 is " & F11

No matter how you’ve formatted the result in F11, the number appears in the string using Excel’s General number format. For example, if cell F11 contains $74,400, the previous formula will appear in the cell as follows:
Cho dù bạn đã định dạng cho kết quả trong F11 như thế nào, thì con số xuất hiện trong chuỗi vẫn sử dụng loại định dạng General của Excel. Ví dụ, giả sử F11 là $74,000, thì công thức ở trên trả về trong ô như sau:

The expense total for this quarter in 2007 is 74400

You need some way to format the number within the string. The next three sections show you some Excel functions that let you do just that.
Bạn thấy rằng mình cần phải có cách nào đó để định dạng cho con số ở trong chuỗi. Ba phần tiếp theo đây sẽ chỉ cho bạn một số hàm Excel để thực hiện điều này.​

7.4.1. The DOLLAR Function — Hàm DOLLAR

The DOLLAR() function converts a numeric value into a string value that uses the Currency format:
Hàm DOLLAR() chuyển đổi một giá trị số thành một giá trị chuỗi, với kiểu định dạng Currency (tiền tệ).

DOLLAR(number [,decimals])

To fix the string example from the previous section, you need to apply the DOLLAR() function to cell F11:
Để sửa lại chuỗi trong ví dụ ở trên, bạn áp dụng hàm DOLLAR() cho ô F11:

="The expense total for this quarter in 2007 is " & DOLLAR(F11, 0)

In this case, the number is formatted with no decimal places. Figure 7.4 shows a variation of this formula in action in cell B16. (The original formula is shown in cell B15.)
Trong trường hợp này, con số được định dạng không có phần thập phân. Hình 7.4 minh họa một biến thể của công thức này ở ô B16 (công thức gốc ở ô B15).

Figure 7.4 - Expenses.xlsx​

 

[BNTT]

7.4. Formatting Text


7.4.2. The FIXED Function — Hàm FIXED

For other kinds of numbers, you can control the number of decimals and whether commas are inserted as the thousands separator by using the FIXED() function:
Đối với những loại số khác, bạn có thể cho nó có thêm số thập phân và các dấu phân cách hàng ngàn, bằng cách dùng hàm FIXED():

FIXED(number [,decimals] [,no_commas])

For example, the following formula uses the SUM() function to take a sum over a range and applies the FIXED() function to the result so that it is displayed as a string with commas and no decimal places:
Ví dụ, công thức sau đây sử dụng hàm SUM() để tính tổng của một dãy và áp dụng hàm FIXED() vào kết quả để cho nó hiển thị dưới dạng một chuỗi với các dấu phân cách hàng ngàn, và không có các số thập phân:

="Total show attendance: " & FIXED(SUM(A1:A8), 0, FALSE) & " people."​

 

[BNTT]

7.4. Formatting Text


7.4.3. The TEXT Function — Hàm TEXT

DOLLAR() and FIXED() are useful functions in specific circumstances. However, if you want total control over the way a number is formatted within a string, or if you want to include dates and times within strings, the powerful TEXT() function is what you need:
Hàm DOLLAR() và hàm FIXED() là những hàm hữu dụng trong những tình huống riêng biệt. Tuy nhiên, nếu bạn muốn hoàn toàn làm chủ cách định dạng một con số trong một chuỗi, hoặc nếu bạn muốn đưa ngày tháng và thời gian vào trong chuỗi, thì hàm TEXT(), một hàm rất mạnh, là cái bạn cần:

TEXT(number, format)

The power of the TEXT() function lies in its format argument, which is a custom format that specifies exactly how you want the number to appear. You learned about building custom numeric, date, and time formats back in Chapter 3.
Sức mạnh của hàm TEXT() nằm trong đối số format của nó, là một loại định dạng tùy biến xác định chính xác bạn muốn con số được hiển thị như thế nào. Bạn đã học về cách tự tạo các loại định dạng số, ngày tháng và thời gian ở chương 3.

➔ To learn about custom numeric formatting, see "Customizing Numeric Formats"
Xem bài "Tùy biến các định dạng số"

➔ To learn about custom date and time formatting, see "Customizing Date and Time Formats"
Xem bài "Tùy biến các định dạng ngày tháng và thời gian"​

For example, the following formula uses the AVERAGE() function to take an average over the range A1:A31, and then uses the TEXT() function to apply the custom format #,##0.00°F to the result:
Ví dụ, công thức sau đây sử dụng hàm AVERAGE() để lấy trung bình của dãy A1:A31, và sử dụng hàm TEXT() để áp dụng kiểu định dạng #,##0.00°F cho kết quả:

="The average temperature was " & TEXT(AVERAGE(A1:A31), "#,##0.00°F")​

Displaying When a Workbook Was Last Updated
Hiển thị thời gian cập nhật lần sau cùng của một bảng tính

Many people like to annotate their workbooks by setting Excel in manual calculation mode and entering a NOW() function into a cell (which returns the current date and time). The NOW() function doesn’t update unless you save or recalculate the sheet, so you always know when the sheet was last updated.
Nhiều người thích chú thích (điều này) trong bảng tính của họ bằng cách sử dụng chế độ tính toán thủ công (manual calculation mode) của Excel, và nhập một hàm NOW() vào một ô (hàm này trả về ngày tháng và thời gian hiện hành). Hàm NOW() sẽ không tự cập nhật cho đến khi bạn lưu bảng tính hoặc tính toán lại bảng tính, do đó bạn luôn luôn biết được lần cập nhật sau cùng của bảng tính là lúc nào.

Instead of just entering NOW() by itself, you might find it better to preface the date with an explanatory string, such as "This workbook last updated:". To do this, you can enter the following formula:
Nhưng thay vì chỉ nhập mỗi hàm NOW(), bạn có thể thấy rằng đặt thêm một câu giải thích ở trước cái ngày tháng sẽ đẹp hơn, như là "This workbook last updated:". Để làm thế, bạn nhập công thức sau đây:

="This workbook last updated: " & NOW()

Unfortunately, your output will look something like this:
Thật không may, kết của sẽ như thế này:

This workbook last updated: 38572.51001

The number 38572.51001 is Excel’s internal representation of a date and time (the number to the left of the decimal is the date, and the number to the right of the decimal is the time). To get a properly formatted date and time, use the TEXT() function. For example, to format the results of the NOW() function in the MM/DD/YY HH:MM format, use the following formula:
Con số 38572.51001 là đại diện cho một giá trị ngày tháng và thời gian trong Excel (phần bên phải dấu thập phân là con số chỉ ngày tháng, và phần ơ bên trái dấu thập phân là con số chỉ thời gian). Để có được một kiểu định dạng đàng hoàng của ngày tháng và thời gian, bạn dùng hàm TEXT(). Ví dụ, để định dạng cho kết quả của hàm NOW() theo dạng MM/DD/YY HH:MM, bạn dùng công thức sau:

="This workbook last updated: " & TEXT(NOW(), "mm/dd/yy hh:mm")​

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS

The rest of this chapter takes you into the real heart of Excel’s text-manipulation tricks. The functions you’ll learn about over the next few pages will all be useful, but you’ll see that, by combining two or more of these functions into a single formula, you can bring out the amazing versatility of Excel’s text-manipulation prowess.
Phần còn lại của chương này đưa bạn vào trung tâm thật sự của những thủ thuật xử lý văn bản trong Excel. Những hàm mà bạn sẽ học trong các trang tiếp theo sẽ rất hữu dụng, và bạn sẽ thấy rằng, bằng cách kết hợp hai hoặc nhiều hàm này trong một công thức lớn, bạn có thể khai thác tối đa việc xử lý văn bản trong Excel.


7.5. Removing Unwanted Characters from a String
Loại bỏ những ký tự không cần thiết ra khỏi chuỗi

Characters imported from databases and text files often come with all kinds of string baggage in the form of extra characters that you don’t need. These could be extra spaces in the string, or line feeds, carriage returns, and other nonprintable characters embedded in the string. To fix these problems, Excel offers a couple of functions: TRIM() and CLEAN().
Những ký tự nhập vào từ những cơ sở dữ liệu và những tập tin văn bản thường bao gồm cả những ký tự dư thừa mà bạn không cần. Đây có thể là những khoảng trống dư thừa trong chuỗi, hoặc những dấu ngắt dòng (line feed), dấu thụt đầu dòng (carriage return), và những ký tự không in ra được. Để giải quyết những vấn đề này, Excel cung cấp cho bạn hai hàm: TRIM() và CLEAN().​

7.5.1. The TRIM() Function — Hàm TRIM

You use the TRIM() function to remove excess spaces within a string:
Bạn sử dụng hàm TRIM() để loại bỏ những khoảng trống dư thừa ra khỏi chuỗi:

TRIM(text)

Here, excess means all spaces before and after the string, as well as two or more consecutive spaces within the string. In the latter case, TRIM() removes all but one of the consecutive spaces.
Ở đây, "dư thừa" có nghĩa là rất cả những khoảng trống nằm ở trước và ở sau của chuỗi, hay là nhiều khoảng trống liên tiếp nhau trong chuỗi (lỗi này xảy ra thường là do nhấn phím Space liên tục). Trong trường hợp thứ hai, TRIM() loại bỏ tất cả và chỉ chừa lại một khoảng trống có ích (là khoảng trống nằm giữa hai chữ).

Figure 7.5 - Text Functions.xlsx​

Figure 7.5 shows the TRIM() function at work. Each string in the range A2:A7 contains a number of excess spaces before, within, or after the name. The TRIM() functions appear in column C. To help confirm the TRIM() function’s operation, I use the LEN() text function in columns B and D. LEN() returns the number of characters in a specified string, using the following syntax:
Hình 7.5 minh họa cách hàm TRIM() làm việc. Mỗi chuỗi trong dãy A2:A7 đều có chứa một số khoảng trống dư thừa ở trước, ở trong và ở sau chuỗi. Hàm TRIM() nằm ở cột C. Để xác nhận hàm TRIM() có làm việc hay không, tôi dùng hàm LEN() trong cột B và cột D. Hàm LEN() trả về số ký tự trong một chuỗi xác định, sử dụng cú pháp sau:

LEN(text)​

7.5.2. The CLEAN() Function — Hàm CLEAN

You use the CLEAN() function to remove nonprintable characters from a string:
Bạn sử dụng hàm CLEAN() để loại bỏ những ký tự không in ra được ra khỏi chuỗi:

CLEAN(text)

Recall that the nonprintable characters are the codes 1 through 31 of the ANSI character set. The CLEAN() function is most often used to remove line feeds (ANSI 10) or carriage returns (ANSI 13) from multiline data. Figure 7.6 shows an example.
Nhắc lại, những ký tự không in ra được có mã từ 1 đến 31 trong bộ mã ANSI. Hàm CLEAN() thường dùng để loại bỏ những dấu ngắt dòng (line feed, mã ANSI là 10) hoặc dấu thụt đầu dòng (carriage return, ANSI 13) từ những dữ liệu có nhiều dòng.

Figure 7.6 - Text Functions.xlsx​

 

[BNTT]

7.5. Removing Unwanted Characters from a String


7.5.3. The REPT Function: Repeating a Character
Hàm REPT - Lập lại một ký tự

The REPT() function repeats a string a specified number of times:
Hàm REPT() lập lại một chuỗi với một số lần đã xác định trước:

REPT(text, number)​

Padding a Cell
Đệm vào một ô

The REPT() function is sometimes used to pad a cell with characters. For example, you can use it to add leading or trailing dots in a cell. Here’s a formula that creates trailing dots after a string:
Hàm REPT() đôi khi được dùng để đệm (pad) vào một ô bằng những ký tự. Ví dụ, bạn có thế dùng nó để thêm những cái chấm đứng ở trước hay ở sau (một chuỗi) trong một ô. Đây là công thức tạo các dấu chấm ở sau một chuỗi:

="Advertising" & REPT(".", 20 - LEN("Advertising"))

This formula writes the string Advertising and then uses REPT() to repeat the dot character according to the following expression: 20 - LEN("Advertising"). This expression ensures that a total of 20 characters is written to the cell. Because Advertising is 11 characters, the expression result is 9, which means that nine dots are added to the right of the string. If the string was “Rent” instead (4 characters), 16 dots would be padded. Figure 7.7 shows how this technique creates a dot follower effect. Note that, for best results, the cells need to be formatted in a monotype font, such as Courier New.
Công thức này viết chuỗi Advertising và rồi dùng hàm REPT() để lập lại những ký tự dấu chấm (.), số lượng các dấu chấm tùy thuộc vào biểu thức: 20 - LEN("Advertising"). Biểu thức này bảm đảm rằng có 20 ký tự trong ô. Bởi vì chữ Advertising có 11 ký tự, nên kết quả của biểu thức là 9, có nghĩa là có 9 dấu chấm được thêm vào ở bên phải của chuỗi. Nếu chuỗi là chữ "Rent" (4 ký tự), thì có 16 dấu chấm được đệm thêm vào. Hình 7.7 minh họa cách làm kỹ thuật tạo hiệu ứng dot followed (dấu chấm theo sau) như thế nào. Lưu ý rằng, để đạt được kết quả tốt nhất, các ô cần được định dạng với loại font monotype (loại font này có bề ngang của mỗi ký tự luôn luôn bằng nhau), như Courier chẳng hạn.

Figure 7.7 - Text Functions.xlsx​

Building Text Charts
Lập các biểu đồ bằng những ký tự

A more common use for the REPT() function is to build text-based charts. In this case, you use a numeric result in a cell as the REPT() function’s number argument, and the repeated character then charts the result.
Một công dụng nữa của hàm REPT() là dùng để lập các biểu đồ bằng những ký tự. Với trường hợp này, bạn dùng một con số là kết quả trong một ô làm đối số number cho hàm REPT(), và sau đó, và kết quả bạn có là một biểu đồ được vẽ bằng những ký tự lập đi lập lại.

A simple example is a basic histogram, which shows the frequency of a sample over an interval. Figure 7.8 shows a text histogram in which the intervals are listed in column A and the frequencies are listed in column B. The REPT() function creates the histogram in column C by repeating the vertical bar (|) according to each frequency, as in this example formula:
Một ví dụ đơn giản là một biểu đồ tần số (histogram) cơ bản, là loại biểu đồ trình bày tần số xuất hiện của một interval (một khoảng, một đoạn...) nào đó. Hình 7.8 minh họa một biểu đồ tần số bằng những ký tự, trong đó các độ tuổi được liệt kê ở cột A, và tần số (ở đây là con số thống kê theo độ tuổi) liệt kê ở cột B. Hàm REPT() tạo một biểu đồ tần số trong cột C bằng cách lập lại những ký tự sổ dọc (|) dựa theo mỗi tần số, như trong công thức mẫu sau đây:

=REPT("|", B4)

Figure 7.8 - Text Functions.xlsx​

With a simple trick, you can turn the histogram into a text-based bar chart, as shown in Figure 7.9. The trick here is to format the chart cells with the Webdings font. In this font, the letter g is represented by a block character, and repeating that character produces a solid bar. (To get the repeat value, I multiplied the percentages in column B by 100 to get a whole number. To keep the bars relatively short, I divided the result by 5.)
Với một thủ thuật đơn giản, bạn có thể biến cái biểu đồ tần số trên thành một biểu đồ dạng thanh (bar chart), cũng dựa vào những ký tự, như minh họa ở hình 7.9. Thủ thuật ở đây là định dạng cho những ô chứa biểu đồ bằng font chữ Webdings (một loại font chứa toàn những symbol). Với loại font này, chữ "g" được đại diện bằng một ký tự hình khối, và việc lập lại những hình khối này sẽ làm cho thanh biểu đồ giống như là một thanh đặc. (Để có số lần lập lại, tôi đã nhân con số phần trăm trong cột B cho 100 để lấy con số tròn, và để giữ cho các thanh biểu đồ này không quá dài, tôi đã chia kết quả đó cho 5).

Figure 7.9 - Text Functions.xlsx

➔ Excel 2007 offers a new feature called data bars that enables you to easily add histogram-like analysis to your worksheets without formulas. See "Adding Data Bars"
Excel 2007 có đưa ra một tính năng mới gọi là thanh dữ liệu, cho phép bạn dễ dàng thêm một phân tích theo dạng biểu đồ vào trong bảng tính của bạn mà không cần dùng đến các công thức. Xem bài "Thêm các Data Bar (thanh dữ liệu)".
​

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.6. Extracting a Substring
Trích xuất một chuỗi con

String values often contain smaller strings, or substrings, that you need to work with. In a column of full names, for example, you might want to deal with only the last names so that you can sort the data. Similarly, you might want to extract the first few letters of a company name to include in an account number for that company. Excel gives you three functions for extracting substrings, as described in the next three sections.
Những giá trị chuỗi thường bao gồm nhiều chuỗi nhỏ, hay còn gọi là chuỗi con, mà bạn cần làm việc với chúng. Ví dụ, trong một cột họ và tên đầy đủ (full name), bạn chỉ muốn làm việc với những cái Họ (last name) mà thôi, để sắp xếp (theo a, b, c) lại dữ liệu. Tương tự, bạn muốn trích ra vài ký tự đầu tiên của một cái tên công ty, là những ký tự mã số của công ty đó... Excel cung cấp cho bạn ba hàm chuyên dùng để trích xuất ra những chuỗi con, như được trình bày trong ba phần tiếp theo đây.​

7.6.1. The LEFT Function — Hàm LEFT

The LEFT() function returns a specified number of characters starting from the left of a string:
Hàm LEFT() trả về một số ký tự đã được xác định tính từ bên trái của một chuỗi.

LEFT(text [,num_chars])

For example, the following formula returns the substring Karen:
Ví dụ, công thức sau đây trả về chuỗi con Karen

=LEFT(“Karen Elizabeth Hammond”, 5)​

7.6.2. The RIGHT Function — Hàm RIGHT

The RIGHT() function returns a specified number of characters starting from the right of a string:
Hàm RIGHT() trả về một số ký tự đã được xác định tính từ bên phải của một chuỗi.

RIGHT(text [,num_chars])

For example, the following formula returns the substring Hammond:
Ví dụ, công thức sau đây trả về chuỗi con Hammond

=RIGHT(“Karen Elizabeth Hammond”, 7)​

7.6.3. The MID Function — Hàm MID

The MID() function returns a specified number of characters starting from any point within a string:
Hàm MID() trả về một số ký tự đã được xác định tính từ bất kỳ chỗ nào trong một chuỗi.

MID(text, start_num, num_chars)

For example, the following formula returns the substring Elizabeth:
Ví dụ, công thức sau đây trả về chuỗi con Elizabeth

=MID(“Karen Elizabeth Hammond”, 7, 9) ​

 

[BNTT]

7.6. Extracting a Substring


7.6.4. Converting Text to Sentence Case
Chuyển đổi chuỗi văn bản thành một câu hoàn chỉnh

Microsoft Word’s Change Case command has a sentence case option that converts a string to all-lowercase letters, except for the first letter, which is converted to uppercase (just as the letters would appear in a normal sentence). You saw earlier that Excel has LOWER(), UPPER(), and PROPER() functions, but nothing that can produce sentence case directly. However, it’s possible to construct a formula that does this using the LOWER() and UPPER() functions combined with the LEFT() and RIGHT() functions.
Lệnh Chance Case của Microsoft Word có một tùy chọn Sentence Case dùng để chuyển đổi một chuỗi thành một câu toàn chữ thường, ngoại trừ ký tự đầu tiên được viết hoa (tương tự như một câu văn bình thường mà ta thường thấy). Trong các bài trước bạn đã tấy Excel có các hàm LOWER(), UPPER() và PROPER(), nhưng không có hàm nào có thể tạo ra một câu hoàn chỉnh cách trực tiếp. Tuy nhiên, có thể tạo ra một công thức để làm điều này, bằng cách sử dụng các hàm LOWER() và UPPER() kết hợp với hàm LEFT() và hàm RIGHT().

You begin by extracting the leftmost letter and converting it to uppercase (assume that the string is in cell A1):
Bạn bắt đầu bằng cách trích ra ký tự đầu tiên và đổi nó thành chữ hoa (giả sử chuỗi này đang nằm ở ô A1):

UPPER(LEFT(A1))

Then, you extract everything to the right of the first letter and convert it to lowercase:
Sau đó, bạn trích mọi thứ còn lại ở bên phải của chữ đầu tiên và chuyển nó thành chữ thường:

LOWER(RIGHT(A1, LEN(A1) - 1))

Finally, you concatenate these two expressions into the complete formula:
Cuối cùng, bạn ghép hai biểu thức trên thành một công thức hoàn chỉnh:

=UPPER(LEFT(A1)) & LOWER(RIGHT(A1, LEN(A1) - 1))

Figure 7.10 shows a worksheet that puts this formula through its paces.
Hình 7.10 minh họa một bảng tính dùng công thức này.

Figure 7.10 - Text Functions.xlsx​

7.6.5. A Date-Conversion Formula
Một công thức chuyển đổi thời gian

If you import mainframe or server data into your worksheets, or if you import online service data such as stock market quotes, you’ll often end up with date formats that Excel can’t handle. One common example is the YYYYMMDD format (for example, 20070823).
Khi bạn nhập dữ liệu từ máy chủ của mạng vào trong bảng tính, hoặc nhập dữ liệu trực tuyến, chẳng hạn như các bảng niêm yết giá thị trường chứng khoán, bạn sẽ thường nhận được trong dữ liệu của mình những kiểu định dạng ngày tháng mà Excel không thể xử lý được. Một ví dụ phổ biến là kiểu định dạng YYYYMMDD (ví dụ như 20070823).

To convert this value into a date that Excel can work with, you can use the LEFT(), MID(), and RIGHT() functions. If the unrecognized date is in cell A1, LEFT(A1, 4) extracts the year, MID(A1,3,2) extracts the month, and RIGHT(A1,2) extracts the day. Plugging these functions into a DATE() function gives Excel a date it can handle:
Để chuyển đổi giá trị đó thành một ngày tháng mà Excel có thể làm việc với nó, bạn có thể dùng hàm LEFT(), MID() và RIGHT(). Giả sử như giá trị ngày tháng đó đang nằm ở ô A1, LEFT(A1, 4) sẽ trích ra phần năm, MID(A1,3,2) trích ra phần tháng và RIGHT(A1,2) trích ra phần ngày. Đưa cả 3 hàm này vào trong hàm DATE(), sẽ cho Excel một ngày tháng mà nó có thể xử lý:

=DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))
​

 

[BNTT]

Case Study


7.7. Generating Account Numbers
Tạo các số tài khoản

Many companies generate supplier or customer account numbers by combining part of the account’s name with a numeric value. Excel’s text functions make it easy to generate such account numbers automatically.
Nhiều công ty tạo các số tài khoản (account number) của nhà cung cấp hoặc của khách hàng bằng cách kết hợp một phần tên của tài khoản với một giá trị số. Các hàm xử lý văn bản của Excel sẽ giúp cho việc tạo tự động hàng loạt số tài khoản trở nên dễ dàng.

To begin, let’s extract the first three letters of the company name and convert them to uppercase for easier reading (assume that the name is in cell A2):
Để bắt đầu, hãy trích ra 3 ký tự đầu tiên của tên mỗi công ty và chuyển đổi chúng thành chữ hoa để dễ đọc (giả sử cái tên này đang ở ô A2):

UPPER(LEFT(A2, 3))

Next, we’ll generate the numeric portion of the account number by grabbing the row number: ROW(A2). However, it’s best to keep all account numbers a uniform length, so we’ll use the TEXT() function to pad the row number with zeroes:
Tiếp theo, chúng ta sẽ tạo phần số của tài khoản bằng cách lấy con số chỉ hàng (đang chứa tên): ROW(A2). Tuy nhiên, tốt nhất là giữ cho tất cả các số tài khoản có cùng một độ dài (có cùng số ký tự), vậy bạn sẽ sử dụng hàm TEXT() để đệm thêm các số không (0) vào con số chỉ hàng:

TEXT(ROW(A2),"0000")

Here’s the complete formula, and Figure 7.11 shows some examples:
Đây là công thức hoàn chỉnh, và hình 7.11 minh họa một số ví dụ:

=UPPER(LEFT(A2, 3)) & TEXT(ROW(A2), "0000")

Figure 7.11 - Text Functions.xlsx​

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.8. Searching for Substrings
Tìm kiếm một chuỗi con

You can take Excel’s text functions up a notch or two by searching for substrings within a given text. For example, in a string that includes a person’s first and last name, you can find out where the space falls between the names and then use that fact to extract either the first name or the last name.
Bạn có thể đưa các hàm xử lý văn bản của Excel lên một hoặc hai bậc bằng việc sử dụng chúng để tìm kiếm một chuỗi con trong một chuỗi cho trước. Ví dụ, trong một chuỗi có bao gồm Họ và Tên của một người, bạn có thể tìm xem cái khoảng trắng giữa Họ và Tên nằm ở đâu, và rồi sử dụng việc đó để tách riêng ra Họ hoặc Tên.​

7.8.1. The FIND and SEARCH Functions — Hàm FIND và Hàm SEARCH

Searching for substrings is handled by the FIND() and SEARCH() functions:
Việc tìm kiếm chuỗi con (trong một chuỗi) được thực hiện bởi hàm FIND() và hàm SEARCH():

FIND(find_text, within_text [, start_num])

SEARCH(find_text, within_text [, start_num])

Here are some notes to bear in mind when using these functions:
Sau đây là một số lưu ý khi sử dụng các hàm này:

• These functions return the character position of the first instance (after the start_num character position) of find_text in within_text.
  Những hàm này trả về vị trí xuất hiện ký tự đầu tiên (sau vị trí của ký tự start_num) của find_text trong within_text.
  
  
• Use SEARCH() for non-case-sensitive searches. For example, SEARCH("e", "Expenses") returns 1.
  Sử dụng hàm SEARCH() khi nội dung bạn tìm không cần phân biệt chữ thường hay chữ hoa. Ví dụ, SEARCH("e", "Expenses") trả về kết quả là 1.
  
  
• Use FIND() for case-sensitive searches. For example, FIND("e", "Expenses") returns 4.
  Sử dụng hàm FIND() khi nội dung bạn tìm có phân biệt chữ thường hay chữ hoa. Ví dụ, FIND("e", "Expenses") trả về kết quả là 4.
  
  
• These functions return the #VALUE! error if find_text is not in within_text.
  Những hàm này trả về lỗi #VALUE! nếu find_text không nằm trong within_text
  
  
• In the find_text argument of SEARCH(), use a question mark (?) to match any single character.
  Trong đối số find_text của hàm SEARCH(), sử dụng dấu chấm hỏi (?) để tìm bất kỳ ký tự đơn lẻ nào (mỗi dấu ? đại diện cho 1 ký tự).
  
  
• In the find_text argument of SEARCH(), use an asterisk (*) to match any number of characters.
  Trong đối số find_text của hàm SEARCH(), sử dụng dấu sao (*) để tìm bất kỳ một số ký tự nào đó (dấu * đại diện cho nhiều ký tự).
  
  
• To include the characters ? or * in a SEARCH() operation, precede each one in the find_text argument with a tilde (~).
  Để tìm kiếm chính ký tự ? hoặc * trong hàm SEARCH(), đặt thêm một dấu ngã (~) trước chúng trong đối số find_text.

 

[BNTT]

7.8. Searching for Substrings


7.8.2. Extracting a First Name or Last Name
Trích xuất Họ hoặc Tên

If you have a range of cells containing people’s first and last names, it can often be advantageous to extract these names from each string. For example, you might want to store the first and last names in separate ranges for later importing into a database table. Or, perhaps you need to construct a new range using a Last Name, First Name structure for sorting the names.
Nếu bạn có một dãy ô đang chứa danh sách họ và tên của nhiều người, việc trích xuất ra các tên từ mỗi dãy này có thể rất có ích. Ví dụ, bạn lưu trữ họ và tên trong hai dãy riên biệt để sau đó sẽ nhập vào trong một bảng cơ sở dữ liệu; hoặc có thể bạn sẽ cần có một dãy mới sử dụng riêng Họ, Tên trong từng cột để tiện cho việc sắp xếp (sort).

The solution is to use the FIND() function to find the space that separates the first and last names, and then use either the LEFT() function to extract the first name or the RIGHT() function to extract the last name.
Giải pháp cho việc này là sử dụng hàm FIND() để tìm cái khoảng trắng phân cách giữa họ và tên, rồi dùng hàm LEFT() để trích ra phần Tên (first name) hoặc dùng hàm RIGHT() để trích ra phần Họ (last name).

For the first name, you would use the following formula (assuming that the full name is in cell A2):
Đối với Tên (first name), bạn dùng công thức sau đây (giả sử rằng cái tên đầy đủ đang ở trong ô A2):

=LEFT(A2, FIND(" ", A2) - 1)

Notice how the formula subtracts 1 from the FIND(" ", A2) result, to avoid including the space in the extracted substring. You can use this formula in more general circumstances to extract the first word of any multiword string.
Để ý rằng công thức trên trừ bớt đi 1 từ kết quả của FIND(" ", A2), là để đưa thêm một khoảng trắng vào trong cái tên đã trích ra. Bạn có thể dùng công thức này mỗi khi cần phải trích ra từ đầu tiên trong một chuỗi có nhiều từ.

For the last name, you need to build a similar formula using the RIGHT() function:
Đối với phần Họ (last name), bạn tạo một công thức tương tự, sử dụng hàm RIGHT():

=RIGHT(A2, LEN(A2) - FIND(" ", A2))

To extract the correct number of letters, the formula takes the length of the original string and subtracts the position of the space. You can use this formula in more general circumstances to extract the second word in any two-word string. Figure 7.12 shows a worksheet that puts both formulas to work.
Để trích ra chính xác con số các chữ, công thức trên lấy chiều dài nguyên thủy của chuỗi trừ bớt đi vị trí của khoảng trắng. Bạn có thể dùng công thức này mỗi khi cần phải trích ra từ thứ hai trong một chuỗi có nhiều từ. Hình 7.12 minh họa một bảng tính sử dụng hai công thức vừa nói ở trên.

Figure 7.12 - Text Functions.xlsx​

CAUTION: These formulas cause an error in any string that contains only a single word. To allow for this, use the IFERROR() function:
Cẩn thận: Những công thức trên có thể gây ra lỗi nếu có bất kỳ chuỗi nào chỉ chứa có một từ. Để ngăn ngừa việc này, dùng hàm IFERROR():

=IFERROR(LEFT(A2, FIND(" ", A2) - 1), A2)

If the cell doesn’t contain a space, the FIND() function returns an error, so IFERROR() returns just the cell text, instead.
Nghĩa là: Nếu trong ô không có một khoảng trắng nào, hàm FIND() trả về một lỗi, và IFERROR() trả về kết quả là chính nội dung trong ô đó.

Ghi chú: Cuốn sách này viết bằng tiếng Anh/Mỹ, ở đó người ta đặt tên theo kiểu khác với Việt Nam: Họ nằm ở sau Tên (với chúng ta thì Họ nằm ở trước Tên). Do đó, chúng ta phải hiểu rằng First Name là Tên, còn Last Name là Họ. Khi áp dụng bài viết trên đây vào những cái tên Việt Nam, cần chú ý đến điều này để trích ra cho đúng.

 

[BNTT]

7.8. Searching for Substrings


7.8.3. Extracting First Name, Last Name, and Middle Initial
Trích xuất Họ, Tên và chữ lót

If the full name you have to work with includes the person’s middle initial, the formula for extracting the first name remains the same. However, you need to adjust the formula for finding the last name. There are a couple of ways to go about this, but the method I’ll show you utilizes a useful FIND() and SEARCH() trick. Specifically, if you want to find the second instance of a substring, start the search one character position after the first instance of the substring. Here’s an example string:Nếu bạn phải làm việc với cái tên đầy đủ có bao gồm cả tên lót (middle initial - thành phần ở giữa họ và tên), công thức dùng để trích xuất vẫn giống ở bài trước. Tuy nhiên bạn cần điều chỉnh lại công thức tìm cái phần Họ (last name). Có hai cách để làm việc này, nhưng cách mà tôi sẽ chỉ cho bạn ở đây là sử dụng thủ thuật của hàm FIND() và SEARCH(). Cụ thể, nếu bạn muốn tìm thành phần thứ hai của một chuỗi con, bạn hãy tìm vị trí của ký tự đứng ngay sau thành phần thứ nhất của chuỗi con đó. Ví dụ với chuỗi sau:

Karen E. Hammond

Assuming that this string is in A2, the formula =FIND(" ", A2) returns 6, the position of the first space. If you want to find the position of the second space, instead set the FIND() function’s start_num argument to 7 — or more generally, to the location of the first space, plus 1:
Giả sử rằng chuỗi đó đang nằm ở ô A2, công thức =FIND(" ", A2) trả về kết quả là 6, là vị trí của khoảng trắng thứ nhất (sau chữ Karen). Nếu bạn muốn tìm vị trí của khoảng trắng thứ hai (sau chữ E.), thay vì đặt tham số start_num cho hàm FIND() là 7, bạn đặt cho nó bằng vị trí của khoảng trắng thứ nhất cộng thêm 1:

=FIND(" ", A2, FIND(" ",A2) + 1)

You can then apply this result within the RIGHT() function to extract the last name:
Rồi bạn áp dụng kết quả của công thức trên vào trong hàm RIGHT() để trích ra phần Họ (last name):

=RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) +1))

To extract the middle initial, search for the period (.) and use MID() to extract the letter before it:
Để trích ra phần tên lót, bạn tìm vị trí của dấu chấm (.) và sử dụng hàm MID() sẽ trích ký tự ở trước dấu chấm này: (Trong bài này, phần tên lót chỉ là một chữ cái viết hoa, theo sau là một dấu chấm, chứ không phải là trường hợp chữ lót viết nguyên cả chữ)

=MID(A2, FIND(".", A2) - 1, 1)

Figure 7.13 shows a worksheet that demonstrates these techniques.
Hình 7.12 minh họa một bảng tính áp dụng những kỹ thuật vừa trình bày:

Figure 7.13 - Text Functions.xlsx
​

 

[BNTT]

7.8. Searching for Substrings


7.8.4. Determining the Column Letter
Xác định mẫu tự của cột (Column Letter)

Excel’s COLUMN() function returns the column number of a specified cell. For example, for a cell in column A, COLUMN() returns 1. This is handy, as you saw earlier in this chapter (“Generating a Series of Letters”), but in some cases you might prefer to know the actual column letter.
Hàm COLUMN() trả về số thứ tự cột của một ô đã xác định. Ví dụ, với một ô đang nằm trong cột A, COLUMN() trả về kết quả là 1. Điều này cũng hay, như bạn đã thấy trong bài "Tạo một chuỗi ký tự liên tục", nhưng trong một số trường hợp, bạn sẽ muốn biết tên của cột (mẫu tự ở trên đầu cột) hơn là số thứ tự của cột.

This is a tricky proposition because the letters run from A to Z, then AA to AZ, and so on. However, Excel’s CELL() function can return (among other things) the address of a specified cell in absolute format — for example, $A$2 or $AB$10. To get the column letter, you need to extract the substring between the two dollar signs. It’s clear to begin with that the substring will always start at the second character position, so we can begin with the following formula:
Đây là một vấn đề khó, bởi vì những ký tự chỉ cột chạy từ A đến Z, rồi từ AA đến AZ, v.v... Tuy nhiên, một trong những kết quả mà hàm CELL() có thể trả về là địa chỉ tuyệt đối của một ô đã xác định — ví dụ, $A$2 hoặc $AB$10. Để có được mẫu tự chỉ cột, bạn cần trích ra chuỗi con nằm ở giữa hai dấu đô-la ($). Rõ ràng là chuỗi con này luôn luôn bắt đầu ở vị trí thứ hai (của một địa chỉ ô), nên chúng ta có thể bắt đầu với công thức sau đây:

=MID(CELL("Address", A2), 2, num_chars)

The num_chars value will either be 1, 2, or 3, depending on the column. Notice, however, that the position of the second dollar sign will either be 3, 4, or 5, depending on the column. In other words, the length of the substring will always be two less than the position of the second dollar sign. So, the following expression will give the num_chars value:
Giá trị num_chars sẽ là 1, 2, hay 3, tùy vào cột nằm ở đâu (ở đầu hay ở cuối bảng tính). Bạn để ý rằng, vị trí của dấu $ thứ hai sẽ là 3, 4, hay 5 tùy thuộc vào cột nằm ở đâu; hay nói cách khác, độ dài của chuỗi con luôn luôn nhỏ hơn con số chỉ vị trí của dấu $ thứ hai 2 số. Do đó, biểu thức sau đây sẽ cho ta giá trị của num_chars.

FIND("$", CELL("address",A2), 3) - 2

Here, then, is the full formula:
Và đây là công thức hoàn chỉnh:

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

Getting the column letter of the current cell is slightly shorter:
Để lấy mẫu tự của cột hiện hành, thì công thức ngắn hơn chút:

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

 

[BNTT]

Chapter 7 - WORKING WITH TEXT FUNCTIONS


7.9. Substituting One Substring for Another
Thay thế một chuỗi con cho một chuỗi khác

The Office programs (and indeed most Windows programs) come with a Replace command that enables you to search for some text and then replace it with some other string. Excel’s collection of worksheet functions also comes with such a feature in the guise of the REPLACE() and SUBSTITUTE() functions.
Các chương trình Office (và hầu hết các chương trình Windows khác) đều có lệnh Replace nhằm cho phép bạn tìm kiếm một vài chữ và rồi thay thế nó bằng những chữ khác. Tập hợp hàm của Excel cũng có một tính năng giống như vậy, dưới dạng hàm REPLACE() và hàm SUBSTITUTE(). ​

7.9.1. The REPLACE Function — Hàm REPLACE

Here’s the syntax of the REPLACE() function:
Đây là cú pháp của hàm REPLACE():

REPLACE(old_text, start_num, num_chars, new_text)

The tricky parts of this function are the start_num and num_chars arguments. How do you know where to start and how much to replace? This isn’t hard if you know the original string in which the replacement is going to take place and if you know the replacement string. For example, consider the following string:
Cái khó của hàm này là các đối số start_num (vị trí bắt đầu thay thế) và num_chars (số ký tự sẽ thay thế bằng chuỗi khác). Làm thế nào để bạn biết được nơi để bắt đầu thay thế và thay thế bao nhiêu (ký tự)? Điều này không khó nếu như bạn biết được chuỗi gốc sẽ được thay thế và chuỗi dùng để thay thế. Ví dụ, bạn xem chuỗi sau đây:

Expense Budget for 2007

To replace 2007 with 2008, and assuming that the string is in cell A1, the following formula does the job:
Để thay thế 2007 bằng 2008, và giả sử rằng chuỗi trên đây đang nằm ở ô A1, công thức sau đây sẽ làm việc này:

=REPLACE(A1, 20, 4, "2008")

However, it’s a pain to have to calculate by hand the start_num and num_chars arguments. And in more general situations, you might not even know these values. Therefore, you need to calculate them:
Tuy nhiên, việc phải tính bằng tay các đối số start_num và num_chars thì thật là khổ sở. Và trong những tình huống thông thường hơn, có thể bạn chẳng biết được các giá trị này. Do đó, bạn cần phải tính chúng:

• To determine the start_num value, use the FIND() or SEARCH() functions to locate the substring you want to replace.
  Để xác định được giá trị start_num, bạn dùng hàm FIND() hoặc hàm SEARCH() để tìm chuỗi con mà bạn muốn thay thế.
  
  
• To determine the num_chars value, use the LEN() function to get the length of the replacement text.
  Để xác định giá trị num_chars, bạn dùng hàm LEN() để tính được số ký tự của chuỗi mà bạn sẽ thay thế nó (bằng một chuỗi khác).

The revised formula then looks something like this (assuming that the original string is in A1 and the replacement string is in A2):
Công thức ở trên sau khi chỉnh sửa lại sẽ giống như sau (giả sử rằng chuỗi cần được thay thế nằm ở ô A1 và chuỗi dùng để thay thế nằm ở A2):

=REPLACE(A1, FIND("2007", A1), LEN("2007"), A2)​