Excel thêm văn bản và số vào vị trí cụ thể của ô
Trong Excel, thêm văn bản hoặc số vào ô là một công việc rất phổ biến. Chẳng hạn như thêm khoảng cách giữa các tên, thêm tiền tố hoặc hậu tố vào ô, thêm dấu gạch ngang vào số xã hội. Ở đây trong hướng dẫn này, nó liệt kê hầu hết tất cả các trường hợp thêm trong Excel và cung cấp các phương pháp tương ứng cho bạn.
Chú thích
Trong hướng dẫn này, tôi tạo một số ví dụ để giải thích các phương pháp, bạn có thể thay đổi các tham chiếu cần thiết khi sử dụng mã hoặc công thức VBA bên dưới hoặc bạn có thể tải xuống các mẫu để thử phương pháp trực tiếp.
Phần này liệt kê các phương pháp khác nhau để thêm ký tự vào đầu mỗi ô như hình minh họa bên dưới:
1.1 Thêm vào đầu các ô bằng cách sử dụng công thức
Tại đây, bạn có thể chọn một trong ba công thức dưới đây:
Toán tử kết hợp Formula1 "&"
Nối nhiều văn bản với nhau bằng ký tự "&".
"văn bản" & ô
Hàm CONCATENATE Formula2
Hàm CONCATENATE được sử dụng để nối các văn bản lại với nhau.
CONCATENATE ("văn bản", ô)
Hàm CONCAT Formula3
Đây là một chức năng mới chỉ xuất hiện trong Excel 2019, Office 365 và Excel trực tuyến.
CONCAT ("văn bản", ô)
Trong công thức chung: ô là tham chiếu ô đến văn bản mà bạn muốn thêm tiền tố vào và văn bản là văn bản được sử dụng để thêm vào ô.
Sử dụng ví dụ trên, bạn có thể sử dụng các công thức trong thanh công thức như sau:
"&"
= $ E $ 3 & B3 or = "Iphone" và B3
Hàm CONCATENATE
= CONCATENATE ($ E $ 3, B3) or = CONCATENATE ("Iphone", B3)
Hàm CONCAT
= CONCAT ($ E $ 3, B3) or = CONCAT ("Iphone", B3)
Ấn Bản đăng ký hạng mục thi để nhận kết quả, sau đó kéo chốt tự động điền xuống để thêm văn bản vào từng ô trong phạm vi B3: B6.
| Chú ý: Văn bản phải được đặt trong dấu ngoặc kép hoặc công thức trả về giá trị lỗi #NAME ?. Nếu sử dụng tham chiếu ô cho một văn bản, hãy nhớ sử dụng tham chiếu tuyệt đối, bạn có thể nhấn phím F4 để thay đổi tham chiếu tương đối thành tham chiếu tuyệt đối. |
1.2 Thêm vào đầu các ô bằng Flash Fill
Nếu bạn đang sử dụng phiên bản Excel 2013 trở lên, tính năng mới mạnh mẽ, Điền vào Flash, có thể điền vào các ô dựa trên các ô ở trên mà bạn đã nhập tự động.
Chọn một ô bên cạnh dữ liệu gốc đầu tiên, nhập thủ công dữ liệu đầu tiên với văn bản tiền tố như hình dưới đây:
Sau đó, tại ô bên dưới, tiếp tục nhập dữ liệu thứ hai với văn bản tiền tố ， trong quá trình nhập, một danh sách màu xám sẽ hiển thị, nhấn đăng ký hạng mục thi để cho phép Flash Fill tự động điền dữ liệu.
Nếu danh sách xem trước không được tạo, vui lòng chuyển đến tab Trang chủ, sau đó nhấp vào Điền > Điền vào Flash để chạy nó theo cách thủ công.
1.3 Thêm vào đầu các ô bằng cách sử dụng mã VBA
Nếu bạn đã quen với mã VBA, thì đây giới thiệu mã VBA để thêm văn bản vào đầu mỗi ô trong một phạm vi.
1. Chọn một dải ô mà bạn muốn thêm văn bản tiền tố, sau đó nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Sau đó, trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun mới trống.
3. Sao chép và dán mã VBA bên dưới vào mô-đun mới.
VBA: Thêm vào đầu ô
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Chú ý: trong tập lệnh mã, iphone là văn bản bạn muốn thêm vào đầu các ô đã chọn, hãy thay đổi nó theo nhu cầu của bạn. |
4. nhấn F5 phím hoặc bấm chạy nút 
trong cửa sổ để kích hoạt mã VBA.
Bây giờ tất cả các ô được chọn đã được thêm dòng chữ "Iphone" ở đầu.
Ngoại trừ việc thêm văn bản vào đầu ô, việc thêm văn bản vào cuối ô cũng rất phổ biến. Ở đây chúng tôi lấy thêm đơn vị vào cuối mỗi ô cho các ví dụ như ảnh chụp màn hình bên dưới:
2.1 Thêm vào cuối các ô bằng cách sử dụng công thức
Tại đây, bạn có thể chọn một trong ba công thức dưới đây:
Toán tử kết hợp Formula1 "&"
Nối nhiều văn bản với nhau bằng ký tự "&".
Ô & "văn bản"
Hàm CONCATENATE Formula2
Hàm CONCATENATE được sử dụng để nối các văn bản lại với nhau.
CONCATENATE (ô, "văn bản")
Hàm CONCAT Formula3
Đây là một chức năng mới chỉ xuất hiện trong Excel 2019, Office 365 và Excel trực tuyến.
CONCAT (ô, "văn bản")
Trong công thức chung: ô là tham chiếu ô đến văn bản mà bạn muốn thêm hậu tố vào và văn bản là văn bản được sử dụng để thêm vào ô.
Sử dụng ví dụ trên, bạn có thể sử dụng các công thức trong thanh công thức như sau:
"&"
= B3 & $ E $ 3 or = B3 & "Kg"
Hàm CONCATENATE
= CONCATENATE (B3, $ E $ 3) or = CONCATENATE (B3, "Kg")
Hàm CONCAT
= CONCAT (B3, $ E $ 3) or = CONCAT (B3, "Kg")
Ấn Bản đăng ký hạng mục thi để nhận kết quả, sau đó kéo chốt tự động điền xuống để thêm văn bản vào cuối mỗi ô trong phạm vi B3: B6.
| Chú ý: Văn bản phải được đặt trong dấu ngoặc kép hoặc công thức trả về giá trị lỗi #NAME ?. Nếu sử dụng tham chiếu ô cho một văn bản, hãy nhớ sử dụng tham chiếu tuyệt đối, bạn có thể nhấn phím F4 để thay đổi tham chiếu tương đối thành tham chiếu tuyệt đối. |
2.2 Thêm vào cuối các ô bằng Flash Fill
Nếu bạn đang sử dụng phiên bản Excel 2013 trở lên, tính năng mới mạnh mẽ, Điền vào Flash, có thể điền vào các ô dựa trên các ô ở trên mà bạn đã nhập tự động.
Chọn một ô bên cạnh dữ liệu gốc đầu tiên, nhập thủ công dữ liệu đầu tiên với văn bản hậu tố như hình dưới đây:
Sau đó, kích hoạt ô bên dưới kết quả đầu tiên, nhấp vào Ngày > Điền vào Flash, tất cả các ô bên dưới bao gồm ô hiện hoạt sẽ được điền tự động.
2.3 Thêm vào cuối các ô bằng cách sử dụng mã VBA
Đây là một mã VBA có thể thêm văn bản vào cuối mỗi ô trong một phạm vi.
1. Chọn một dải ô mà bạn muốn thêm văn bản hậu tố, sau đó nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Sau đó, trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun mới trống.
3. Sao chép và dán mã VBA bên dưới vào mô-đun mới.
VBA: Thêm vào cuối ô
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Chú ý: trong tập lệnh mã, Kg là văn bản bạn muốn thêm vào cuối các ô đã chọn, hãy thay đổi nó theo nhu cầu của bạn. |
4. nhấn F5 phím hoặc bấm chạy nút trong cửa sổ để kích hoạt mã VBA.
Bây giờ văn bản "Kg" đã được thêm vào cuối mỗi ô.
Đối với hầu hết các bạn, việc thêm văn bản vào đầu hoặc cuối ô thì dễ, nhưng thêm văn bản vào giữa ô có thể hơi khó. Ví dụ, như hình minh họa bên dưới, thêm một dấu phân cách, chẳng hạn như dấu hai chấm: ở cuối ký tự thứ hai của mỗi ô trong phạm vi B3: B7.
3.1 Thêm vào sau ký tự thứ n của chuỗi bằng công thức
Dưới đây là bốn công thức được cung cấp để bạn xử lý công việc này, hãy chọn một công thức để sử dụng:
Formula1 Kết hợp các hàm LEFT và RIGHT
LEFT (ô, n) & "text" & RIGHT (ô, LEN (ô) -n)
Công thức 2 hàm KẾT HỢP CONCATENATE (CONCAT), RIGHT và LEN
CONCATENATE (LEFT (cell, n), "text", RIGHT (cell, LEN (cell) -n))
Or
CONCAT (LEFT (ô, n), "văn bản", RIGHT (ô, LEN (ô) -n))
Formula3 Hàm REPLACE
THAY THẾ (ô, n + 1, 0, "văn bản")
Trong công thức chung: ô là tham chiếu ô đến văn bản mà bạn muốn thêm văn bản vào giữa và văn bản là văn bản được sử dụng để thêm vào ô, n là số xác định sau ký tự nào trong chuỗi văn bản bạn muốn nhập kí tự.
Để giải quyết công việc được đề cập ở trên, vui lòng sử dụng các công thức sau:
Kết hợp các hàm LEFT và RIGHT
= LEFT (B3, 2) & ":" & RIGHT (B3, LEN (B3) -2) or = LEFT (B3, 2) & $ E $ 3 & RIGHT (B3, LEN (B3) -2)
Các chức năng KẾT HỢP CONCATENATE (CONCAT), RIGHT và LEN
= CONCATENATE (LEFT (B3, 2), ":", RIGHT (B3, LEN (B3) -2)) or = CONCAT (LEFT (B3, 2), ":", RIGHT (B3, LEN (B3) -2))
Hàm REPLACE
= THAY THẾ (B3, 2 + 1, 0, ":") or = THAY THẾ (B3, 2 + 1, 0, $ E $ 3)
Ấn Bản đăng ký hạng mục thi để nhận kết quả, sau đó kéo ô điều khiển tự động điền xuống để thêm văn bản sau ký tự thứ hai của mỗi ô trong phạm vi B3: B7.
| Chú ý: Văn bản phải được đặt trong dấu ngoặc kép hoặc công thức trả về giá trị lỗi #NAME ?. Nếu sử dụng tham chiếu ô cho một văn bản, hãy nhớ sử dụng tham chiếu tuyệt đối, bạn có thể nhấn phím F4 để thay đổi tham chiếu tương đối thành tham chiếu tuyệt đối. |
3.2 Thêm vào sau ký tự thứ n của chuỗi bằng Flash Fill
Nếu bạn đang sử dụng phiên bản Excel 2013 trở lên, Điền vào Flash, có thể điền vào các ô dựa trên các ô ở trên mà bạn đã nhập tự động.
Chọn một ô bên cạnh dữ liệu gốc đầu tiên, nhập thủ công dữ liệu đầu tiên bằng dấu hai chấm sau ký tự thứ hai như hình minh họa bên dưới:
Sau đó, kích hoạt ô bên dưới kết quả đầu tiên, nhấn Ctrl + E phím để kích hoạt Điền vào Flash lệnh, tất cả các ô bên dưới bao gồm ô hiện hoạt đã được điền tự động.
| Chú ý: Nếu bạn đang ở trong hệ thống MAC, hãy nhấn tổ hợp phím Command + E. |
3.3 Thêm vào sau ký tự thứ n của chuỗi bằng cách sử dụng mã VBA
Mã VBA cũng có thể thực hiện công việc này.
1. nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Sau đó, trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun mới trống.
3. Sao chép và dán mã VBA bên dưới vào mô-đun mới.
VBA: Thêm vào giữa các ô
Sub AddToMidduleOfString()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
Next
End Sub| Chú ý: trong tập lệnh mã, 2 là số xác định vị trí mà bạn muốn thêm văn bản vào sau, : là văn bản bạn muốn thêm vào, 3 là tổng của 2 và độ dài của văn bản bạn thêm vào. Lấy một ví dụ khác, để thêm "và" vào sau ký tự đầu tiên của chuỗi "AB", tập lệnh phải là Rng.Value = VBA.Left (Rng.Value, 1) & "và"& VBA.Mid (Rng.Value, 4, VBA.Len (Rng.Value) - 1) |
4. nhấn F5 phím hoặc bấm chạy nút trong cửa sổ để kích hoạt mã VBA. Một hộp thoại bật ra để chọn các ô để thêm văn bản.
5. nhấp chuột OK. Giờ đây, văn bản ":" đã được thêm vào sau ký tự thứ hai của mỗi ô trong phạm vi B19: B23.
Nếu bạn cảm thấy mệt mỏi với các công thức và VBA, bạn có thể thử một công cụ mạnh mẽ và tiện dụng, Kutools cho Excel, có thể giúp bạn với thêm văn bản tính năng này.
Tính năng Thêm văn bản này có thể:
- Thêm văn bản trước ký tự đầu tiên
- Thêm văn bản sau ký tự cuối cùng
- Thêm văn bản tại các vị trí được chỉ định
- Extension
Thêm văn bản trước các chữ hoa
Thêm văn bản trước các chữ cái thường
Thêm văn bản trước chữ hoa / thường
Thêm văn bản trước các ký tự số
Nhấp để nhận bản dùng thử miễn phí 30 ngày ngay bây giờ.
Chọn các ô mà bạn muốn thêm văn bản, sau đó áp dụng Thêm văn bản bằng cách nhấp vào Kutools > bản văn > thêm văn bản.
4.1 Thêm văn bản trước ký tự đầu tiên hoặc sau ký tự cuối cùng
Trong tạp chí thêm văn bản , nhập văn bản mà bạn muốn thêm vào các ô trong bản văn hộp văn bản, sau đó kiểm tra Trước ký tự đầu tiên trong Chức vụ phần.
Nhấp chuột Đăng Nhập or Ok để kết thúc việc thêm.
Để thêm văn bản sau ký tự cuối cùng, hãy nhập văn bản bạn muốn thêm vào các ô trong bản văn hộp văn bản, sau đó kiểm tra Sau ký tự cuối cùng trong Chức vụ phần.
Nhấp chuột Đăng Nhập or Ok để kết thúc việc thêm.
4.2 Thêm văn bản tại (các) vị trí được chỉ định
Đôi khi, bạn có thể muốn thêm văn bản vào giữa ô hoặc thêm văn bản vào một số vị trí của ô. Giả sử thêm dấu phân tách "-" sau chữ số thứ ba và thứ bảy của số xã hội như hình ảnh chụp màn hình bên dưới:
Trong tạp chí thêm văn bản hộp thoại, nhập văn bản vào bản văn hộp văn bản và kiểm tra Chỉ định tùy chọn, sau đó nhập các vị trí bằng cách sử dụng dấu phẩy để phân tách vào hộp văn bản và sau đó nhấp vào Ok or Đăng Nhập.
4.3 Phần mở rộng của tiện ích Thêm văn bản
Bên cạnh việc thêm văn bản vào các vị trí bình thường (đầu, cuối và vị trí được chỉ định), thêm văn bản tiện ích cũng hỗ trợ thêm văn bản
- Trước chữ hoa
- Trước các chữ cái thường
- Trước chữ hoa / chữ thường
- Trước các ký tự số
Trong tạp chí thêm văn bản hộp thoại, nhập văn bản vào bản văn hộp văn bản và kiểm tra Chỉ thêm vào , sau đó nhấp vào trình đơn thả xuống bên dưới để chọn thao tác bạn cần.
Phần này cung cấp các công thức về cách thêm văn bản trước hoặc sau văn bản cụ thể xuất hiện đầu tiên trong Excel.
5.1 Thêm văn bản trước văn bản cụ thể đầu tiên
Ví dụ: bạn muốn thêm văn bản "(giảm giá)" trước văn bản đầu tiên "giá" trong ô như ảnh chụp màn hình dưới đây được hiển thị:
Trong Excel, hàm REPLACE và SUBSTITUTE có thể giải quyết công việc này.
Hàm REPLACE
REPLACE (ô, FIND ("find_text", cell), 0, "add_text")
Hàm SUBSTITUTE
SUBSTITUTE (ô, "find_text", "Replace_text", 1)
Trong trường hợp này, vui lòng sử dụng các công thức trên như sau:
= REPLACE (B4, FIND ("giá", B4), 0, "(giảm giá)")
Or
= SUBSTITUTE (B4, "price", "(sale) price", 1)
Các đối số công thức trong trường hợp này là
Ô: B4,
Find_text: giá,
Add_text: (giảm giá),
Replace_text: giá (ưu đãi).
Ấn Bản đăng ký hạng mục thi để nhận kết quả thêm đầu tiên, sau đó kéo ô điều khiển tự động điền qua các ô cần công thức này.
5.2 Thêm văn bản sau văn bản cụ thể đầu tiên
Ví dụ: bạn muốn thêm ký hiệu tiền tệ "$" sau dấu hai chấm đầu tiên ":" trong ô như ảnh chụp màn hình bên dưới được hiển thị:
Trong Excel, hàm REPLACE và SUBSTITUTE có thể giải quyết công việc này.
Hàm REPLACE
REPLACE (ô, FIND ("find_text", cell), find_text_length, "Replace_text")
Hàm SUBSTITUTE
SUBSTITUTE (ô, "find_text", "Replace_text")
Trong trường hợp này, vui lòng sử dụng các công thức trên như sau:
= REPLACE (B12, FIND (":", B12), 1, ": $")
Or
= SUBSTITUTE (B12, ":", ": $")
Các đối số công thức trong trường hợp này là
Ô: B12,
Find_text::,
Replace_text :: $.
Find_text_length: 1
Ấn Bản đăng ký hạng mục thi để nhận kết quả thêm đầu tiên, sau đó kéo ô điều khiển tự động điền qua các ô cần công thức này.
Trong phần này của hướng dẫn, nó giới thiệu ba phương pháp khác nhau (công thức, Tìm và Thay thế, VBA) để giúp bạn thêm các ký tự vào trước hoặc sau mỗi từ của ô.
| Thêm ký tự trước mỗi từ |
 |
| Thêm ký tự sau mỗi từ |
 |
6.1 Thêm (các) ký tự vào trước hoặc sau mỗi từ theo công thức
Hàm SUBSTITUTE có thể được sử dụng để giải quyết công việc này, các công thức chung như sau:
Thêm vào trước mỗi từ
"ký tự" & SUBSTITUTE (chuỗi, "", "ký tự")
Thêm vào sau mỗi từ
SUBSTITUTE (chuỗi, "", "ký tự") & "ký tự"
Giả sử thêm "+" trước mỗi từ trong danh sách B3: B6, vui lòng sử dụng công thức như sau:
= "+" & SUBSTITUTE (B3, "", "+")
Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền xuống để nhận tất cả kết quả.
Giả sử thêm ($) sau mỗi từ của danh sách B11: B14, vui lòng sử dụng công thức như sau:
= SUBSTITUTE (B11, "", "($)") & "($)"
Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền xuống để nhận tất cả kết quả.
6.2 Thêm (các) ký tự vào trước hoặc sau mỗi từ bằng cách Tìm và Thay thế
Nếu bạn muốn thêm các ký tự trước mỗi từ loại trừ chữ đầu tiên hoặc thêm các ký tự sau mỗi từ loại trừ chữ cuối cùng như ảnh chụp màn hình bên dưới, Excel tích hợp sẵn Tìm và Thay thế tiện ích có thể xử lý công việc này.
Thêm vào trước mỗi từ trừ từ đầu tiên
Thêm vào sau mỗi từ trừ từ cuối cùng
1. Chọn các ô bạn muốn thêm ký tự vào trước hoặc sau mỗi từ, sau đó nhấn Ctrl + H các phím để kích hoạt Tìm và Thay thế thoại.
2. Nhập dấu cách vào Tìm hộp văn bản, sau đó nhập các ký tự mới có khoảng trắng trong Thay thế bằng hộp văn bản, và cuối cùng nhấp vào Thay thế Tất cả .
| hoạt động | Tổng Quát | Ví dụ |
| Thêm ký tự trước mỗi từ | Thêm các ký tự mới theo sau một khoảng trắng | Thêm (bán) trước mỗi từ |
| hoạt động | Tổng Quát | Ví dụ |
| Thêm ký tự sau mỗi từ | Các ký tự mới được theo sau bằng một khoảng trắng | Thêm - sau mỗi từ |
Ở đây cung cấp hai mã VBA để thêm ký tự trước hoặc sau mỗi từ, vui lòng chạy mã bằng cách làm theo các bước dưới đây.
1. nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun trống mới.
3. Sao chép và dán mã VBA bên dưới vào mô-đun.
Thêm vào trước mỗi từ
Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = xInStr & Trim(xStr)
Else
xValue = xValue & " " & xInStr & Trim(xStr)
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End SubThêm vào sau mỗi từ
Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = Trim(xStr) & xInStr
Else
xValue = xValue & " " & Trim(xStr) & xInStr
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End Sub4. nhấn F5 phím để chạy mã (hoặc bạn có thể nhấp vào chạy nút ), một hộp thoại bật ra để yêu cầu bạn chọn các ô liên tục để làm việc, hãy nhấp vào OK.
5. Trong hộp thoại bật ra thứ hai, nhập các ký tự mà bạn muốn thêm, nhấp vào OK.
Sau đó, các ký tự sẽ được thêm vào đầu hoặc cuối mỗi từ.
Đôi khi, bạn có thể muốn thêm văn bản vào giữa mọi ký tự. Ở đây trong phần này, nó liệt kê hai kịch bản, một là thêm văn bản giữa mọi ký tự, một là thêm khoảng cách giữa mỗi số.
7.1 Thêm văn bản giữa mọi ký tự / chữ số với chức năng Do người dùng xác định
Để thêm các ký tự vào giữa mọi ký tự trong một chuỗi, chức năng Do Người dùng Xác định có thể giúp bạn.
Trong trường hợp này, chúng tôi thêm một khoảng trắng giữa mọi ký tự.
1. nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun trống mới.
3. Sao chép và dán mã VBA bên dưới vào mô-đun.
Thêm văn bản giữa các ký tự
Function AddText(Str As String) As String
Dim i As Long
For i = 1 To Len(Str)
AddText = AddText & Mid(Str, i, 1) & " "
Next i
AddSpace = Trim(AddSpace)
End Function4. Lưu mã và đóng cửa sổ để quay lại trang tính, sau đó nhập công thức bên dưới vào ô mà bạn muốn đặt kết quả thêm.
= AddText (B3)
B3 là ô chứa chuỗi mà bạn muốn thêm khoảng cách giữa các ký tự.
5. nhấn đăng ký hạng mục thi để nhận kết quả, kéo ô điều khiển tự động điền qua các ô để nhận các kết quả khác khi bạn cần.
|
Chú ý: 1） Trong mã VBA, bạn có thể thay đổi "" sang các văn bản khác khi bạn cần, giả sử thay đổi nó thành "-" để thêm - giữa các ký tự. 2） Mã sẽ thêm văn bản mới sau mỗi ký tự. Bạn có thể xóa văn bản được thêm cuối cùng nếu bạn không cần bằng cách sử dụng công thức dưới đây. Để biết thêm chi tiết về công thức, vui lòng truy cập: Cách xóa N ký tự đầu tiên hoặc cuối cùng khỏi một ô hoặc chuỗi trong Excel? LEFT (ô, LEN (ô) -text_length) |
7.2 Thêm khoảng trắng giữa mọi số với công thức
Nếu bạn muốn thêm khoảng trắng giữa các chữ số của chuỗi là giá trị số, bạn có thể sử dụng hàm TEXT.
| Chú ý: các số của dải ô bạn muốn thêm khoảng cách giữa cần có cùng độ dài, nếu không, một số kết quả có thể không chính xác. |
Giả sử đây là danh sách các số có 8 chữ số trong phạm vi B10: B12, vui lòng chọn một ô sẽ đặt kết quả, nhập công thức bên dưới:
= TEXT (B10, "# # # # # # # #")
Ấn Bản đăng ký hạng mục thi , sau đó kéo ô điều khiển tự động điền qua các ô khác.
Nếu độ dài của các số là 5, công thức sẽ được thay đổi thành = TEXT (B10, "# # # # #").
Nếu bạn muốn thêm (các) ký tự vào giữa mỗi từ, giả sử thêm khoảng cách giữa họ, tên đệm, họ như hình minh họa bên dưới, bạn có thể sử dụng chức năng Do người dùng xác định.
1. nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun trống mới.
3. Sao chép và dán mã VBA bên dưới vào mô-đun.
Thêm ký tự giữa các từ
Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function4. Lưu mã và đóng cửa sổ để quay lại trang tính, sau đó nhập công thức bên dưới vào ô mà bạn muốn đặt kết quả thêm.
= AddCharacters (B3)
B3 là ô chứa chuỗi mà bạn muốn thêm khoảng cách giữa các từ.
5. nhấn đăng ký hạng mục thi để nhận kết quả, kéo ô điều khiển tự động điền qua các ô để nhận các kết quả khác khi bạn cần.
| Chú ý: trong mã VBA, bạn có thể thay đổi "" sang các văn bản khác khi bạn cần, giả sử thay đổi nó thành "-" để thêm - giữa các từ. |
Để thêm (các) ký tự cụ thể giữa văn bản và số như hình minh họa bên dưới, đây cung cấp hai phương pháp.
9.1 Thêm ký tự giữa văn bản và số bằng công thức
Nếu các văn bản đứng trước các số, công thức chung là
TRIM (REPLACE (string, MIN (FIND ({1,2,3,4,5,6,7,8,9,0}, string & "1234567890")), 0, "-"))
Sử dụng trường hợp trên làm ví dụ, vui lòng sử dụng công thức như sau:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền trên các ô khác để điền công thức này.
Nếu các số ở phía trước các văn bản, công thức chung là
LEFT (string, SUM (LEN (string) -LEN (SUBSTITUTE (string, {0,1,2,3,4,5,6,7,8,9}, "")))) & "." & RIGHT (string, LEN (B13) - SUM (LEN (string) -LEN (SUBSTITUTE (string, {0,1,2,3,4,5,6,7,8,9}, ""))))
Sử dụng trường hợp trên làm ví dụ, vui lòng sử dụng công thức như sau:
=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(B13,LEN(B13)- SUM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, ""))))
Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển tự động điền trên các ô khác để điền công thức này.
9.2 Thêm ký tự giữa văn bản và số bằng Flash Fill
Nếu bạn đang sử dụng phiên bản Excel 2013 trở lên, Điền vào Flash cũng có thể giúp bạn thêm (các) ký tự giữa văn bản và số.
Bên cạnh dữ liệu ban đầu, hãy nhập dữ liệu mới đã được thêm (các) ký tự giữa văn bản và số. Sau đó nhấn phím Enter để chuyển đến ô tiếp theo của kết quả đầu tiên.
Và hãy nhấn Ctrl + E các phím để bật Flash Fill.
Extension
Hướng dẫn này cũng liệt kê một số trường hợp về việc thêm văn bản mà chúng ta có thể gặp trong cuộc sống hoặc công việc hàng ngày của mình.
Đôi khi, bạn cần thêm dấu phân tách như dấu gạch ngang vào một chuỗi số điện thoại hoặc số xã hội như hình minh họa bên dưới. Việc nhập thủ công sẽ được thử nếu có hàng trăm số cần được thêm vào. Trong phần này, nó giới thiệu ba cách thủ thuật để xử lý công việc này một cách nhanh chóng.
10.1 Thêm dấu gạch ngang vào số theo công thức
Đây là hai công thức có thể nhanh chóng thêm dấu gạch ngang vào số. Để thêm dấu gạch ngang sau số thứ ba và thứ sáu trong ô B3, vui lòng sử dụng công thức như sau:
Hàm REPLACE
= REPLACE (REPLACE (B3,4,0, "-"), 8,0, "-")
Hàm TEXT
= TEXT (B3, "??? - ??? - ???")
Ấn Bản đăng ký hạng mục thi để nhận kết quả đầu tiên, sau đó kéo ô điều khiển điền tự động qua các ô khác.
|
Chú ý: Bạn có thể thay đổi các đối số của công thức bên trên khi cần. Giả sử thêm "-" sau các chữ số thứ hai, thứ tư và thứ sáu, bạn có thể sử dụng các công thức như sau: = REPLACE (REPLACE (REPLACE (B3,3,0, "-"), 6,0, "-"), 9,0, "-") = TEXT (B3, "?? - ?? - ?? - ???") |
10.2 Thêm dấu gạch ngang vào số bằng cách Định dạng ô
Để thêm trực tiếp dấu gạch ngang vào các số trong ô ban đầu, bạn có thể sử dụng Format Cells tính năng này.
1. Chọn các số mà bạn muốn thêm dấu gạch ngang, sau đó nhấn Ctrl + 1 các phím để kích hoạt Format Cells thoại.
2. bên trong Format Cells hộp thoại, dưới Con số tab, chọn Tuỳ chỉnh từ Phân loại phần.
3. Sau đó ở bên phải Kiểu phần, loại ### - ### - ### vào hộp văn bản, nhấp vào OK.
Bây giờ các số được chọn đã được thêm dấu gạch ngang.
10.3 Thêm dấu gạch ngang vào số điện thoại bằng cách Thêm văn bản của Kutools cho Excel
Nếu bạn có Kutools cho Excel được cài đặt trong Excel, thêm văn bản tính năng này cũng có thể giúp bạn một việc.
1. Chọn các ô có chứa các số cần thêm dấu gạch ngang, nhấp vào Kutools > bản văn > Thêm văn bản.
2. bên trong thêm văn bản hộp thoại, nhập dấu gạch ngang hoặc dấu phân tách khác vào bản văn hộp văn bản, chọn Chỉ định , sau đó nhập các vị trí bạn muốn thêm dấu gạch ngang vào sau và phân tách chúng bằng dấu phẩy vào hộp văn bản bên dưới.
3. nhấp chuột Ok hoặc Áp dụng. Sau đó, các số được chọn đã được thêm vào dấu gạch ngang.
Tải xuống miễn phí Thêm văn bản để dùng thử 30 ngày.
Giả sử có một danh sách các số có độ dài khác nhau, bạn muốn thêm các số XNUMX ở đầu để làm cho chúng có cùng độ dài và trông gọn gàng như hình minh họa bên dưới. Đây là hai phương pháp được cung cấp cho bạn để xử lý công việc.
11.1 Thêm các số không ở đầu vào các ô bằng tính năng Định dạng Ô
Bạn có thể sử dụng Format Cells tính năng đặt định dạng tùy chỉnh để sửa độ dài ô bằng cách thêm các số không ở đầu.
1. Chọn các ô mà bạn muốn thêm các số XNUMX ở đầu, sau đó nhấn Ctrl + 1 phím để kích hoạt Format Cells thoại.
2. bên trong Format Cells hộp thoại, dưới Con số tab, chọn Tuỳ chỉnh từ Phân loại phần.
3. Sau đó ở bên phải Kiểu phần, loại 0000 vào hộp văn bản (để sửa độ dài số thành 4-dight, nhập 0000, bạn có thể thay đổi nó thành độ dài khác, chẳng hạn như 5-dight, loại 00000), nhấp OK.
Sau đó, các số đã được thêm các số XNUMX ở đầu và có cùng độ dài.
| Chú ý: nếu độ dài của dữ liệu gốc vượt quá độ dài đã đặt, nó sẽ hiển thị dữ liệu gốc mà không có các số XNUMX ở đầu. |
11.2 Thêm các số không ở đầu vào các ô có công thức
Nếu bạn không muốn thay đổi dữ liệu ban đầu, bạn có thể sử dụng công thức để thêm các số không ở đầu vào các số ở các vị trí khác.
Dưới đây là ba chức năng có thể giúp bạn.
Hàm Formula1 TEXT
TEXT (số, "00…")
Hàm Formula2 RIGHT
RIGHT ("00…" & số, độ dài)
Hàm Formula3 BASE
BASE (số, 10, chiều dài)
Số lượng các số không trong công thức bằng độ dài của số.
Ở đây bạn đặt 4 chữ số làm độ dài của các số, vui lòng sử dụng công thức như sau:
= TEXT (B10, "0000")
= RIGHT ("0000" & B10,4)
= BASE (B10,10,4)
Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền xuống.
| Chú ý: nếu độ dài của dữ liệu gốc vượt quá độ dài đã đặt, nó sẽ hiển thị dữ liệu gốc mà không có các số XNUMX ở đầu. |
Nếu bạn muốn thêm các số không ở cuối vào các số để tạo độ dài cố định, giả sử thêm các số không ở cuối vào các số trong ô B3: B7 để làm cho chúng có độ dài 5 chữ số như hình minh họa bên dưới, bạn có thể áp dụng một công thức để giải quyết công việc .
Hàm REPT
số & REPT ("0", độ dài-LEN (số))
Trong công thức, đối số "number" đại diện cho số hoặc tham chiếu ô mà bạn muốn thêm các số không ở cuối và đối số "length" là độ dài cố định mà bạn muốn.
Vui lòng sử dụng công thức như sau:
= B3 & REPT ("0", 5-LEN (B3))
Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền xuống các ô cần công thức này.
Nếu bạn muốn xuất hoặc sao chép dữ liệu từ trang tính vào sổ ghi chú, các văn bản có thể được đặt không cẩn thận vì các giá trị ô có độ dài khác nhau, để làm cho chúng trông gọn gàng như hình minh họa bên dưới, bạn cần thêm dấu cách ở cuối.
Để giải quyết công việc này, chức năng REPT có thể giúp bạn.
LEFT (text & REPT ("", max_length), max_length)
Trong công thức, đối số "text" là văn bản hoặc tham chiếu ô mà bạn muốn thêm dấu cách ở cuối và đối số "max_length" là độ dài cố định mà bạn muốn tạo văn bản, phải dài hơn hoặc bằng độ dài tối đa của các văn bản được sử dụng.
Để thêm dấu cách vào cuối văn bản trong ô B3: C6, vui lòng sử dụng công thức dưới đây:
= LEFT (B3 & REPT ("", 10), 10)
Ấn Bản đăng ký hạng mục thi , sau đó kéo chốt tự động điền vào tất cả các ô cần công thức này.
Trong trường hợp này, vì văn bản dài nhất được sử dụng là 9 ký tự, chúng tôi sử dụng 10 ở đây.
Đôi khi, bạn cần thêm dấu ngoặc kép hoặc dấu ngoặc nhọn xung quanh văn bản hoặc số trong các ô như ảnh chụp màn hình bên dưới, ngoại trừ việc nhập thủ công từng cái một, đây là hai phương pháp trong Excel có thể giải quyết nhanh chóng.
14.1 Thêm dấu ngoặc kép / dấu ngoặc nhọn xung quanh bằng Định dạng ô
Nếu bạn muốn thêm dấu ngoặc kép xung quanh văn bản trong dữ liệu gốc, tính năng Định dạng Ô trong Excel có thể giúp bạn.
1. Chọn các ô mà bạn muốn thêm dấu ngoặc kép, nhấn Ctrl + 1 các phím để kích hoạt Format Cells thoại.
2. bên trong Format Cells hộp thoại, dưới Con số tab, chọn Tuỳ chỉnh từ danh sách Phân loại, sau đó chuyển đến phần bên phải để nhập "''" @ "''" vào Kiểu hộp văn bản. Nhấp chuột OK.
Văn bản trong các ô đã chọn đã được thêm vào xung quanh bằng dấu ngoặc kép.
Nếu bạn muốn thêm dấu ngoặc nhọn xung quanh văn bản, trong hộp văn bản Loại của hộp thoại Định dạng Ô, hãy sử dụng (@).
14.2 Thêm dấu ngoặc kép / dấu ngoặc nhọn xung quanh bằng công thức
Công thức cũng có thể được sử dụng để thêm dấu ngoặc kép vào văn bản trong các ô khác.
Sử dụng & trình kết nối
""""&chữ&""""
Or
char (34) & text & char (34)
Trong trường hợp này, văn bản trong ô B11, vui lòng sử dụng các công thức như sau:
= "" "" & B11 & "" ""
Or
= char (34) & B11 & char (34)
Ấn Bản đăng ký hạng mục thi và kéo chốt tự động điền xuống để điền vào các ô.
Nếu bạn muốn thêm dấu ngoặc quanh văn bản, hãy sử dụng công thức dưới dạng "("&chữ&")".
14.3 Thêm dấu ngoặc kép / dấu ngoặc nhọn xung quanh bằng VBA
Nếu bạn cần mã VBA để giải quyết công việc này, đây là một VBA.
1. nhấn Khác + F11 các phím để kích hoạt Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Sau đó, trong cửa sổ bật lên, hãy nhấp vào Chèn > Mô-đun để chèn một mô-đun mới trống.
3. Sao chép và dán mã VBA bên dưới vào mô-đun mới.
VBA: Thêm dấu ngoặc kép xung quanh văn bản
Sub addquotationmarksorbrackets()
'UpdatebyExtendOffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = """" & Rng.Value & """"
Next
End Sub| Chú ý: trong tập lệnh mã, "" "" & Rng.Value & "" "" cho biết thêm "" xung quanh văn bản, nếu bạn muốn thêm dấu ngoặc () xung quanh văn bản, hãy thay đổi nó thành "(" & Rng.Value & ")". |
4. nhấn F5 phím hoặc bấm chạy nút trong cửa sổ để kích hoạt mã VBA. Một hộp thoại bật ra để chọn các ô để thêm dấu ngoặc kép.
5. nhấp chuột OK. Bây giờ dấu ngoặc kép đã được thêm vào xung quanh văn bản trong các ô đã chọn.
Đôi khi, để giúp những người dùng khác hiểu rõ hơn về dữ liệu, bạn có thể cần thêm văn bản vào kết quả công thức như hình minh họa bên dưới. Để bao gồm văn bản trong công thức, bạn có thể sử dụng dấu ngoặc kép xung quanh văn bản và thêm dấu & nối để kết hợp văn bản và công thức với nhau.
Trường hợp 1 thêm văn bản trước công thức
"Hôm nay là" & TEXT (TODAY (), "dddd, mmmm dd.")
Trường hợp 2 thêm văn bản sau công thức
TEXT (NOW (), "HH: MM: SS") & "là thời gian hiện tại."
Trường hợp 3 thêm văn bản ở hai cạnh của công thức
"Hôm nay đã bán" & dữ liệu & "kg".
Các hướng dẫn Excel khác: |
|
Kết hợp nhiều Workbook / Worksheets thành một Tách ô văn bản, số và ngày (Tách thành nhiều cột) Kết hợp nội dung của nhiều ô mà không làm mất dữ liệu trong Excel So sánh hai cột cho các kết quả phù hợp và khác biệt trong Excel |
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn
- Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
- Hợp nhất các ô / hàng / cột và Lưu giữ dữ liệu; Nội dung phân chia ô; Kết hợp các hàng trùng lặp và Tổng / Trung bình... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
- Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
- Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
- Yêu thích và Chèn công thức nhanh chóng, Dãy, Biểu đồ và Hình ảnh; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
- Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
- Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
- Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
- Nhóm bảng tổng hợp theo số tuần, ngày trong tuần và hơn thế nữa ... Hiển thị các ô đã mở khóa, đã khóa bởi các màu sắc khác nhau; Đánh dấu các ô có công thức / tênhữu ích. Cảm ơn !
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
