Đề Xuất 6/2023 # Count Words In A Cell Or Range # Top 7 Like | Beiqthatgioi.com

# Đề Xuất 6/2023 # Count Words In A Cell Or Range # Top 7 Like

Cập nhật nội dung chi tiết về Count Words In A Cell Or Range mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

First, let’s count the total number of words in a cell or range of cells. Finally, let’s count how many times a specific word occurs in a cell or range of cells.

1. The TRIM function below returns a string with only regular spaces.

2. To get the length of this string, add the LEN function.

3. The SUBSTITUTE function below returns a string without spaces.

4. To get the length of this string, add the LEN function.

5. To count the total number of words in cell A1, subtract the length of the string without spaces (formula from step 4) from the length of the string with only regular spaces (formula from step 2) and add 1.

Conclusion: to count the total number of words in a cell, simply count the number of spaces and add 1 to this result. 1 space means 2 words, 2 spaces means 3 words, etc.

6. To count the total number of words in the range A1:A2, add the SUMPRODUCT function and replace A1 with A1:A2.

Finally, let’s count how many times a specific word occurs in a cell or range of cells. This trick is pretty cool.

7. The LEN function below returns the length of the original text in cell A1.

8. The SUBSTITUTE function below returns the string without the word “dog”.

9. The LEN function below returns the length of this string.

10. Subtract the length of the string without the word “dog” (formula from step 9) from the length of the original text in cell A1 (formula from step 7).

Conclusion: after removing the word “dog” from the original text, 12 characters are missing.

11. We know the length of the word “dog” (3), so the word “dog” occurs 12 / 3 = 4 times in cell A1.

12. To count how many times the word “dog” occurs in the range A1:A2, add the SUMPRODUCT function and replace A1 with A1:A2.

13. Use the COUNTIF function in Excel to count the number of cells that contain a specific word.

## Excel Formula: Count Total Words In A Cell

Excel doesn’t have a dedicated function for counting words in a cell. However, with a little ingenuity, you can create such a formula using the SUBSTITUTE and LEN functions, with help from TRIM, as shown in the example. At a high level, this formula uses the LEN function to count the number of characters in the cell, with and without spaces, then uses the difference to figure out the word count. This works, because word count is equal to the number of spaces + 1, so long as there is one space between each word.

The first part of the formula counts the characters in cell B5, after removing extra space:

=

LEN

(

TRIM

(

B5

))

// normalize space, count characters

Inside LEN, the TRIM function first removes any extra spaces between words, or at the beginning or end of the text. This is important, since any extra spaces will throw off the word count. In this case, there are no extra space characters, so TRIM returns the original text directly to the LEN function, which returns 30:

LEN

(

"All Quiet on the Western Front"

)

// returns 30

At this point, we have:

=

30

-

LEN

(

SUBSTITUTE

(

B5

,

" "

,

""

))

+

1

Next, we use the SUBSTITUTE function to remove all space characters from the text:

SUBSTITUTE

(

B5

,

" "

,

""

)

// strip all space

Notice SUBSTITUTE is configured to look for a space character (” “), and replace with an empty string (“”). By default, SUBSTITUTE will replace all spaces. The result is delivered directly to the LEN function, which returns the count:

LEN

(

"AllQuietontheWesternFront"

)

// returns 25

LEN returns 25, the number of characters remaining after all space has been removed. We can now simplify the formula to:

=

30

-

25

+

1

// returns 6

which returns 6 as a final result, the number of words in cell B5.

Dealing with blank cells

The formula in the example will return 1 even if a cell is empty, or contains only space. This happens because we are adding 1 unconditionally, after counting space characters between words. To guard against this problem, you can adapt the formula as shown below:

Notice we've replaced 1 with this expression:

This code first trims B5, then checks the length. If B5 contains text, LEN returns a positive number, and the expression returns TRUE. If B5 is empty, or contains only space, TRIM returns an empty string ("") to LEN. In that case, LEN returns zero (0) and the expression returns FALSE. The trick is that TRUE and FALSE evaluate to 1 and zero, respectively, when involved in any math operation. As a result, the expression only adds 1 when there is text in B5. Otherwise, it adds zero (0). This logic could also be written with the IF function statement like this:

and the result would be the same. The expression above is simply more compact.

## Cách Khai Báo Và Sử Dụng Sheets, Range Và Cells Trong Excel Bằng Vba

Khi sử dụng VBA để lấy và gán dữ liệu vào Range hay Cells trong một Sheets của File Excel, điều đầu tiên bạn cần là nắm rõ cách thức khai báo và sử dụng các thành phần trên bằng lệnh VBA.

Vậy để có thể khai báo Sheets, Range và Cells của một File Excel bằng VBA, thì ngay trong bài viết này, mình sẽ chia sẻ với các bạn cách để thực hiện.

Cách sử dụng Sheets, Range và Cells bằng VBA

Để dễ hình dùng hơn về cách khai báo 1 File Excel bằng VBA thì File Excel gọi là Workbook, tiếp đến là Worksheet, và còn lại là Range và Cells.

Khi sử dụng dữ liệu trong bảng tính Excel bằng VBA, bạn sẽ có các ràng buộc về Sheets và Workbook như: Range và Cells thuộc Sheets và Sheets đó thuộc Workbook nào?

1) Workbooks

Thông thường khi làm việc với một Workbook thì bạn không cần khai báo trong VBA, nhưng khi truy vấn dữ liệu từ các File Excel khác thì các bạn có thể khai báo như sau:

'khai báo workbook biến wb Dim wb As Workbook 'gán workbook hiện tại vào biến wb Set wb = ThisWorkbook

hoặc khai báo cho một Workbook khác:

Dim wb As Workbook 'ví dụ: đường dẫn D:Desktop2018-calendar.xlsx Set wb = Workbooks.Open("đường dẫn")

2) Worksheet

Sheets trong VBA sẽ được tự động tạo ra khi bạn tạo Sheets từ File Excel. Sheet có nhiều kiểu khai báo như khai báo theo tên, khai báo theo số thức tự Index trong VBAProject.

Cách khai báo theo số Index Sheets của Excel:

Số Index của Sheets được gán theo thứ tự tạo Sheets. Khi bạn tạo thêm Sheets thứ 2 thì số Index của Sheets đó là 2 và sẽ không thay đổi khi bạn thay đổi vị trí sắp xếp của Sheets.

Dim ws As Worksheet ‘gán biến worksheet thứ 1 vào biến ws Set ws = Sheets(1) Set ws = Sheets(“ten-sheet1”)

Dim ws As Worksheet Set ws = ActiveSheet Sheet(2).Activate

hoặc:

Sheet(2).Select

3) Range và Cells

Khi truy xuất Range và Cells các bạn có một số lưu ý sau:

Truy xuất dạng tên đại diện hàng và cột. Ví dụ: A1, B1, C1 ….

Truy xuất theo vị trí hàng và cột. Ví dụ: Cells A1 sẽ có vị trí hàng 1 và cột 1, tương tự Cells B1 sẽ có bị trí là hàng 1 và cột 2.

Cột sẽ có vị trí theo số thứ tự như cột bên trái của bảng tính Excel, còn hàng sẽ qui ước theo thứ tự của chữ cái. Ví dụ: A sẽ có vị trí là 1, B là 2 và C là 3…

Cách khai báo Cells:

‘gán Cells Cells có vị trí hàng 1 và cột 1, tức là Cells A1 vào biến rg Set rg = Cells(1,1)

hoặc

Dim rg As Range Set rg = Range("A1")

Cách khai báo Range:

Dim rg As Range 'gán Range từ Cells A1 đến Cells D10 vào biến Set rg = Range("A1:D10") Dim rg As Range 'gán Range từ Cells A1 đến Cells D10 vào biến Set rg = Range(Cells(1,1),Cells(10,4))

Khai váo Range và Cells với một Sheets xác định trước.

Dim ws As Worksheet Set ws = Sheets(“ten-sheet1”) Set rg = ws.Range(Cells(1,1),Cells(10,4))

Cách lấy giá trị và gán vào Cells

Cách lấy giá trị từ một Cells vào biến

Dim giatriA2 As String ‘gán giá trị của Cell A2 của Sheet có tên là ten_sheet1 vào biến giatriA2 giatriA2 = Sheets(“ten-sheet1”).Range(“A2”).Value

🔺 Nếu bạn không chỉ đích danh Sheets thì VBA sẽ mặc định trỏ đến ActiveSheet.

Cách gán giá trị một biến vào một Cells:

'khai báo biến giatriA2 là kiểu String, tương tự như kiểu Text trong Excel Dim giatri As String 'gán giá trị vào biến giatriA2 giatri="VBA Sheet và Cells" 'gán giá trị của Cell A2 của Sheet có tên là ten_sheet1 vào biến giatriA2 Sheets("ten-sheet1").Range("A2").Value= giatri

💡 Lưu ý: Khi sử dụng Cells thay cho Range khi gán hoặc lấy giá trị thì các bạn sử dụng vị trí. Ví dụ: Sheets(“ten-sheet1”).Cells(2, 1).Value = giatriA2

Cách chọn 1 Cells hoặc 1 Range

'chọn Cells A1 Sheets(1).Range("A1").Select 'chọn Cells A2 và A4 Sheets(1).Range("A2,A4").Select 'chọn Range từ A1 đến D10 Sheets(1).Range("A1:D10").Select

💡 Lưu ý: Khi bạn thực hiện câu lệnh Select 1 Cells hay Range thì bạn phải Activate Sheets trước.

4) Một số ví dụ về Sheets, Range và Cells

Tạo 3 Sheet có tên lần lượt là: ten-sheet1, ten-sheet2, ten-sheet3.

Tạo 1 Module trong VBA.

💡 Nếu muốn truy vấn Sheets theo tên thì bạn không nên đặt tên Sheets dài và có dấu.

Ví dụ 1: Đọc tên Workbook và Sheet1

Sub doc_ten_Wb_Ws() Dim tenwb As String Dim tenws As String Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Sheets(1) tenwb = wb.Name tenws = ws.Name 'hiển thị tên Workbook và Worksheets lên Message Box MsgBox tenwb & " " & tenws 'tiếp tục ví dụ 2 ở đây End Sub

Nếu bạn đang đặt con trỏ vào hàm thì Run Macro sẽ thực hiện tại hàm đó.

Khi con trỏ chuột đang trỏ ở vị trí ngoài bảng soạn lệnh thì VBA sẽ hiện 1 bảng chọn hàm, bạn chọn vào hàm muốn thực hiện và Run để chạy lệnh.

Kết quả: Một cửa sổ thông báo hiển thị lên tên Workbook và tên Worksheet.

Tiếp theo ví dụ 1, sau khi gán tên Workbook và Worksheet vào biến thì bạn có thể ghi vào A1 và A2 của Sheet1.

Qua Sheet2 và Sheet3, vì tên Worbook không thay đổi nên bạn chỉ cần gán lại Worksheet vào biến và ghi vào Sheets tương ứng.

ws.Range("A1").Value = tenwb ws.Range("A2").Value = tenws Set ws = wb.Sheets(2) tenws = ws.Name ws.Range("A1").Value = tenwb ws.Range("A2").Value = tenws Set ws = wb.Sheets(3) tenws = ws.Name ws.Range("A1").Value = tenwb ws.Range("A2").Value = tenws

Kết quả: Ở cả 3 Sheets, tên Workbook sẽ được ghi vào Cells a1, tên Sheets được ghi vào Cell A2.

Ví dụ 3: Đọc giá trị Cell A1 và A2 ở Sheet2 và ghi sang Cells B1 và B2 của Sheet1

Sub doc_giatri_sheet2() Dim giatriA1 As String Dim giatriA2 As String Sheets(2).Activate giatriA1 = Sheets(2).Cells(1, 1).Value giatriA2 = Sheets(2).Cells(2, 1).Value Sheets(1).Activate Sheets(1).Range("B1").Value = giatriA1 Sheets(1).Range("B2").Value = giatriA2 End Sub

Kết quả: Giá trị của Cells A1 và A2 của Sheet2 sẽ được ghi vào Cells B1 và B2 của Sheet1.

OK! Trên đầy là tổng hợp các cách khai báo và sử dụng Sheets, Range và Cells trong Excel bằng VBA. Khi nắm được cách sử dụng các thành của một File Excel bằng VBA thì ứng dụng vào các hàm phức tạp bạn sẽ dễ dàng nắm bắt hơn.

## Quick Way To Count Words In Excel * Productivity Portfolio

I recently attended a workshop with an expert’s panel where people provided their best Excel tips. One tip involved filtering cells with a certain number of words. A nice tip, but it left some attendees wondering how to count words in Excel. The program doesn’t have this feature, but you can find the answer by creating a formula to count words. (Includes sample Excel worksheet and formula)

The summit tip was for a specific product, but opportunities exist where you might want to count words in an Excel cell. I do a similar process when I pull a list of site search queries from my web analytics to see people’s interests.

Spaces and Counting Words

The key to counting words in Excel is to identify the spaces between words correctly. You need to remove leading and trailing spaces in the cells, or the count will be inflated. There are a couple of ways to do this. A simple way is to use Excel add-ons such as ASAP Utilities.

Another way is to use the Excel TRIM function. The trim function removes leading and trailing spaces in a cell. This text function also removes extra spaces between words to just one space. In the screen snap below, you can see that the spaces aren’t always obvious. You have to compare the counts in Columns B and C.

LEN returns the number of characters in a string. In my case, the number will reflect the number for each cell. Since a “space” is considered a character, it is counted.

SUBSTITUTE is similar to “search and replace” on a cell, except we can specify how many times the substitution should occur. For example, you could indicate once, all, or a specific number.

For example, the formula =SUBSTITUTE(A1,"example","sample") would replace the word “example” with “sample” for cell A1.

For our purposes, we want to substitute a space ” ” with nothing. Effectively, the function removes all spaces, so the words run together. “Example text” would change to “Exampletext.”

Understanding the Word Count Formula

One nice feature of Excel is that you can nest formulas that include multiple functions. The formula below references LEN, TRIM, and SUBSTITUTE. It also starts with the IF function, which we’ve outlined before.

Let me break this down for you.

We TRIM any extra spaces in cell A2 and determine if the cell is blank by using =IF(LEN(TRIM(A2))=0,0. If A2 is blank, it assigns the word count as 0.

If A2 isn’t blank, we count the characters in the cell using LEN(TRIM(A2)). You might think of this as our starting character count inclusive of spaces.

We use LEN(SUBSTITUTE(A2)," ","") to remove the remaining spaces. We then count the characters in this new string.

We take the LEN count from Step 2 and subtract the LEN count from Step 3. We then add one to the count to adjust for the first word.

In the above example, I placed the formula in Column B. However, you can also add more columns to show various stages. This often helps when learning a nested formula. In the screen snap below, my formula is in Column F.

If you prefer word problems, think of it this way. If the cell is empty, make the word count = 0. Otherwise, remove the extra spaces and count the characters in the cell. Hold that value as “A.” Now, remove all spaces in that cell and count the characters again. Hold that value as “B.” Your word count is (A-B) + 1.

“Sample example text” = LEN count of 19. This is your “A.”

“Sampleexampletext” = LEN count of 17. This is your “B.”

(19-17)+1 = word count of 3.

One Step Further