Chia sẻ công thức Excel tách từng ký tự trong chuỗi

 Trong nhiều tình huống từ các chuỗi số  như: Mã số thuế, Số chứng minh nhân nhân, Số đăng ký kinh doanh,... cần tách từng số trong mã số này ra các ô riêng biệt để phục vụ lấy thông tin lên thông tin hóa đơn, chứng từ,...

CÔNG THỨC EXCEL TÁCH CHUỖI KÝ TỰ SỐ THÀNH CÁC KÝ TỰ RIÊNG BIỆT

Giả sử ô A1 nhập chuỗi số thì công thức tách chuỗi số sang chiều ngang là:

=MID($A1,COLUMN(A:A),1)

=> Kéo công thức sang ngang cho các ô còn lại. Nếu chuỗi ở ô khác thì chỉ thay $A1 trong công thức, không thay A:A trong công thức

Tách chuỗi số sang chiều dọc:

=MID(A$1,ROW(1:1),1)

=> Kéo công thức xuống dưới cho các ô còn lại. 

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Các hàm Excel phân biệt chữ hoa và chữ thường

 Hầu hết các hàm và phép tính trong Excel đều không phân biệt chữ hoa và chữ thường. Tuy nhiên, Excel có một số hàm hoạt động có phân biệt chữ hoa và chữ thường.

HÀM FIND

Hàm FIND và hàm SEARCH là 2 hàm có cấu trúc và cách sử dụng giống nhau. Hai hàm này có điểm khác biệt như sau:

- Hàm FIND: Tìm kiếm phân biệt chữ hoa và chữ thường

- Hàm SEARCh: Tìm kiếm không phân biệt chữ hoa và chữ thường. Nhưng hàm này cho phép tìm kiếm theo ký tự đại diện kiểu Wildcard

Ví dụ: Ô A1 nhập chữ "Excel Thỉnh Vũ"

Nếu dùng hàm FIND với cấu trúc là: =FIND("T",A1) thì kết quả là 7 (ở vị trí ký tự số 7 tính từ trái sang phải). Nhưng nếu sửa lại thành =FIND("t",A1) thì kết quả là #VALUE! => Vì trong chuỗi ô A1 thì chữ T viết hoa

HÀM EXACT

Thông thường khi so sánh 2 giá trị văn bản với nhau thì chỉ cần dùng phép tính = để so sánh. Tuy nhiên cách so sánh này không phân biệt chữ hoa và chữ thường

Hàm EXACT hỗ trợ so sánh chữ hoa với chữ thường.

Ví dụ: A1 nhập là EXCEL Thỉnh vũ, B1 nhập là Excel Thỉnh Vũ

- Nếu đưa công thức so sánh bằng: =A1=B1 thì kết quả trả về là TRUE

- Nếu dùng hàm EXACT: =EXACT(A1,B1) thì kết quả trả về là FALSE

HÀM SUBSTITUTE

Hàm SUBSTITUTE là hàm thay thế đoạn văn bản trong chuỗi để xử lý số liệu được dùng rất phổ biến. Và hàm này khi thực hiện thay thế có phân biệt chữ hoa và chữ thường

Ví dụ: Ô A1 nhập: Excel Thỉnh Vũ

Tại ô B1 nhập công thức là: =SUBSTITUTE(A1,"e","f") thì kết quả trả về là Excfl Thỉnh Vũ. 

HÀM CODE

Hàm CODE bản chất là trả về mã số code của 1 ký tự trong bảng ASCII và hàm này có phân biệt chữ hoa và chữ thường.

Ví dụ: =CODE("A") thì kết quả trả về là 65 nhưng =CODE("a") thì kết quả trả về 97

Hàm UNICODE

Cách hoạt động của hàm UNICODE giống với hàm CODE, nhưng hàm UNICODE mạnh hơn ở điểm là tìm ra được mã số code của 1 ký tự có dấu

Ví dụ: =CODE("Ấ") thì kết quả trả về là 63 => Tức hàm CODE ko hiểu được chữ có dấu và tự động chuyển về ký tự của dấu ?. Nhưng với hàm UNICODE =UNICODE("Ấ") thì kết quả trả về là 7844, và nếu viết thường thì =UNICODE("ấ") thì kết quả trả về là 7845

Tương tự người dùng có thể test các chữ cái viết hoa, viết thường với hàm CODE và UNICODE

Hàm TEXTAFTER, TEXTBEFORE và TEXTSPLIT

Các hàm này có ở trong Microsoft 365. Trong đó:

- TEXTAFTER: lấy chuỗi đằng sau ký tự

- TEXTBEFORE: lấy chuỗi đằng trước ký tự

- TEXTSPLIT: Tách chuỗi ra thành mảng căn cứ theo ký tự

3 hàm này đều hoạt động phân biệt chữ hoa và chữ thường.

Ví dụ: Ô A1 nhập "Excel", ô B1 lập công thức là: =TEXTBEFORE(A1,"e") thì kết quả trả về là Exc

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel đọc ngày tháng thành chữ

 Trong nhiều tình huống tạo form chứng từ, hợp đồng, hơn hàng, phiếu,... trên Excel thì người dùng thường phải đưa giá trị ngày tháng sang dang chữ. Dưới đây là một số tình huống đọc giá trị ngày tháng thành chữ

Tình huống 1: Chuyển giá trị ngày tháng sang giá trị văn bản thuần (Chuyển Date sang Text)

Công thức chuyển: =TEXT(A1,"dd/mm/yyyy") hoặc =TEXT(A1,"mm/dd/yyyy") .... tùy theo hiển thị người dùng mong muốn

Thông thường cách chuyển này được áp dụng trong ghép chuỗi. Ví dụ:

="Ngày ký: " & TEXT(A1,"dd/mm/yyyy")

Tình huống 2: Đọc giá trị ngày tháng Tiếng Việt

Công thức đọc có Tỉnh/Thành đi cùng:

=TEXT(A1,"""Hà Nội, ngày "" dd ""tháng"" mm ""năm"" yyyy")

Công thức đọc có Thứ đi cùng:

=TEXT(A1,"[$-42A]dddd, ""ngày "" dd ""tháng"" mm ""năm"" yyyy")

Tình huống 3: Đọc giá trị ngày tháng Tiếng Anh

Công thức đọc có thứ và tháng đầy đủ:

=TEXT(A1,"dddd dd mmmm yyyy")

Trường hợp tên thứ và tháng được viết tắt thì công thức là:

=TEXT(A1,"ddd dd mmm yyyy")

Công thức đọc có phân biệt st, nd, rd, th:

=TEXT(A1,"dd")&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"}) & TEXT(A1," mmmm yyyy")

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Sử dụng hàm FORMULATEXT để lấy thông tin trong công thức Excel

 Trong nhiều tình huống người dùng lập công thức và sau đó cần lấy thông tin trong công thức ra ô bên ngoài để phục vụ kiểm tra thông tin hoặc lấy thông tin trong công thức để phục vụ tính toán tiếp

HÀM FORMULATEXT HỖ TRỢ ĐƯA CÔNG THỨC VỀ DẠNG TEXT

Hàm FORMULATEXT có từ Office 2013 trở lên để lấy toàn bộ cấu trúc công thức (bắt đầu từ dấu = đến hết công thức) thành chuỗi văn bản Text

Ví dụ ô B1 lập công thức =SUM(A1:A10) và ô C1 lập công thức =FORMULATEXT(B1) thì kết quả là chuỗi "=SUM(A1:A10)"

Như vậy, từ 1 chuỗi văn bản thì hoàn toàn có thể dùng các hàm văn bản và tìm kiếm trong văn bản để lấy thông tin bên trong công thức được

LẤY THÔNG TIN BÊN TRONG CÔNG THỨC VỚI HÀM FORMULATEXT

Ví dụ 1: Cột A nhập liệu theo cấu trúc =Số tiền*Tỷ giá  và cần tách số tiền và tỷ giá thành 2 cột

Công thức tách số tiền gốc:

=VALUE(MID(FORMULATEXT(A2),2,FIND("*",FORMULATEXT(A2))-2))

Công thức tách tỷ giá:

=VALUE(MID(FORMULATEXT(A2),FIND("*",FORMULATEXT(A2))+1,99))

Ví dụ 2: Sử dụng hàm FORMULATEXT để kiểm tra sự khác biệt nhau cả công thức trong cả 1 cột

Xem chi tiết TẠI ĐÂY

Ví dụ 3: Kiểm tra trong công thức trong công thức của cột có sử dụng hàm nào đó không. 

Giả sử kiểm tra xem kiểm tra trong cột A (A1:A100) có ô nào sử dụng hàm SUBTOTAL hay không.

Công thức đếm số ô sử dụng hàm SUBTOTAL trong vùng từ A1:A1000

=SUMPRODUCT(--ISNUMBER(FIND("SUBTOTAL",FORMULATEXT(A1:A100))))

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel lấy chuỗi ký tự trong dấu đóng mở ngoạc

 Trong nhiều tình huống nhập liệu thì nhiều thông tin văn bản được đặt trong dấu đóng mở ngoạc () mà người dùng cần lấy riêng thông tin ra

(Hỉnh ảnh dữ liệu mẫu)

CÁCH TÁCH CHUỖI KÝ TỰ TRONG DẤU ĐÓNG MỞ NGOẠC VỚI FLASH FILL

Lưu ý: Cách này chỉ áp dụng với Office 2013 trở lên

Bước 1: Nhập tay 2-3 kết quả mẫu ở cột bên cạnh

Ví dụ: Dữ liệu từ ô A1 thì cột bên cạnh sẽ nhập kết quả mẫu ở ô B1 và B2

Bước 2: Bôi đen từ ô đầu tiên chứa kết quả mẫu xuống dưới và nhấn tổ hợp phím Ctrl+E

CÔNG THỨC TÁCH ĐOẠN KÝ TỰ VĂN BẢN TRONG DẤU ĐÓNG MỞ NGOẠC

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Cách dùng: Thay A1 trong công thức bằng địa chỉ ô dữ liệu tương ứng

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách chuỗi theo ký tự ngăn cách

 Trong thực tế có nhiều tình huống cần xử lý tách chuỗi ký tự theo ký tự ngăn cách như: Tách họ tên, tách đoạn ký tự trong mã, tách địa chỉ, tách từ danh sách nhập vào trong 1 ô,...

Tình huống 1: Tách chuỗi thành 2 phần (lấy bên trái và bên phải)

Ví dụ: ô A1 nhập: HH001-Máy tính Macbook

Yêu cầu: Tách ký tự trước và sau dấu -

Công thức lấy đoạn ký tự trước dấu -:

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

Công thức lấy đoạn ký tự sau dấu -

=MID(A1,FIND("-",A1)+1,500)

Tình huống 2: Tách chuỗi danh sách nhập trong 1 ô

Ví dụ: Các đoạn ký tự trong một ô sử dụng dấu phẩy để ngăn cách

Công thức tách sử dụng các hàm lồng ghép: TRIM, MID, SUBSTITUTE, REPT, COLUMN

=TRIM(MID(SUBSTITUTE(","&$A1,",",REPT(" ",99)),COLUMN(A:A)*99,99))

Thực hiện kéo công thức sang ngang cho để lấy các phần còn lại. Nếu sử dụng dấu khác thì thay 2 dấu phẩy trong công thức bằng dấu tương ứng (dấu phẩy thay thế màu đỏ)

Công thức tác sử dụng hàm lồng ghép: IFERROR, INDEX, FILTERXML, COLUMN

=IFERROR(INDEX(FILTERXML("<s><f>"&SUBSTITUTE($A1,",","</f><f>")&"</f></s>","//f"),COLUMN(A:A)),"")

Thực hiện kéo công thức sang ngang cho các ô còn lại. Nếu sử dụng dấu khác thì thay 1 dấu phẩy trong công thức bằng dấu tương ứng (dấu phẩy thay thế màu đỏ)

Tình huống 3: Tách họ tên

Tham khảo chi tiết TẠI ĐÂY

Tình huống 4: Tách địa chỉ

Tham khảo chi tiết TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách tên tỉnh thành, quận huyện, phường xã từ chuỗi địa chỉ

 Trong thực tế thì nhu cầu tách tỉnh thành, quận huyện, phường xã từ chuỗi địa chỉ có ý nghĩa khá quan trọng trong quản trị thông tin. Tuy nhiên, có địa chỉ thì có số nhà và ngõ xóm, có địa chỉ thì chỉ có từ tên xã,...nên số lượng thông tin trong 1 chuỗi địa chỉ không đồng đều

Giả lập tình huống: Thông tin trong chuỗi địa chỉ sử dụng dấu phẩy để ngăn cách

Công thức tách tên tỉnh thành:

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

Công thức tách tên quận huyện:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99*2),99))

Công thức tách tên phường xã:

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99*3),99))

Lưu ý: 

- Nếu dấu ngăn cách trong thông tin địa chỉ không đồng nhất (ô thì dùng dấu -, ô thì dùng dấu phẩy,..) thì sử dụng chức năng Find and Replace của Excel (phím tắt CTRL+H) để đưa về đồng nhất dấu ngăn cách

- Công thức sử dụng dấu phẩy là ký tự ngăn cách (màu đỏ trong công thức), nếu sử dụng dấu khác thì thay dấu tương ứng

- Trường hợp chuỗi địa chỉ người dùng nhập thiếu. Ví dụ: Có tên quận huyện, phường xã nhưng không có tỉnh thành,... => thì công thức trả về kết quả sai. Trường hợp này người dùng nên sửa lại dữ liệu

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Sự khác nhau giữa hàm SUBSTITUTE và REPLACE dùng thay thế chuỗi ký tự trong Excel

 Trong Excel thì việc xử lý thay thế chuỗi ký tự được dùng khá phổ biến. Trong đó có 2 hàm SUBSTITUTE và hàm REPLACE

HÀM SUBSTITUTE

Cấu trúc: =SUBSTITUTE(text,old_text,new_text,[instance_num])

Trong đó:

- Text là văn bản cần xử lý

- Old_text là đoạn ký tự bên trong cần thay thế (có phân biệt chữ hoa, chữ thường)

- New_text là đoạn ký tự thay thế cho old_text

- Instance_num: Thay thế lần thứ bao nhiêu. Nếu tham số này bỏ trống thì mặc định thay thế hết. Nếu không bỏ trống thì nó sẽ thay thế cho lần xuất hiện tương ứng

Ví dụ: ô A1 nhập chuỗi ký tự là "1 kilomet là 1000 met"

Công thức tại ô B1 thay thế "met" bằng "m": 

- Old_text là "met" và bỏ trống instance_num: =SUBSTITUTE(A1,"met","m") thì kết quả là "1 kilom là 1000 m"

- Old_text là "Met" và bỏ trống instance_num: =SUBSTITUTE(A1,"Met","m") thì kết quả như văn bản gốc. Do SUBSTITUTE có phân biệt chữ hoa và chữ thường

- Old_text là "met" và instance_num là 2: =SUBSTITUTE(A1,"met","m",2) thì kết quả là "1 kilomet là 1000 m" => Tức thay thế ở lần xuất hiện thứ 2

HÀM REPLACE

Cấu trúc: =REPLACE(old_text,start_num,num_chars,new_text)

Trong đó:

- Old_text: là văn bản cần xử lý.

- Start_num: Thay thế từ ký tự số mấy (tính từ trái sang phải)

- Num_chars: Số kỳ tự cần thay thế

- New_text: Đoạn ký tự thay thế vào

Ví dụ: Ô A1 nhập chuỗi VT-VLC-001, ô A2 nhập chuỗi VT-VLP-003. Cần thay thế 3 ký tự ở giữa thành NL thì công tức tại ô B1 là:

=REPLACE(A1,4,3,"NL")

Như vậy, sự khác nhau giữa hàm SUBSTITUTE và hàm REPLACE là:

- Hàm SUBSTITUTE sẽ tìm kiếm đoạn ký tự có trong văn bản hay không, nếu có sẽ thực hiện thay thế

- Hàm REPLACE thì sẽ thay thế từ vị trí nào trong chuỗi và thực hiện thay thế bao nhiêu ký tự

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách số điện thoại từ chuỗi

 Trong nhiều trường hợp thông tin số điện thoại được nhập trong chuỗi (như trong phần diễn giải, ghi chú) và người dùng cần tách thông tin số điện thoại riêng ra. Đặc biệt hay xuất hiện trong bán hàng, ngân hàng,...

Ví dụ tình huống: Tách số điện thoại trong chuỗi nhưng giữa các con số trong sđt lúc thì nhập liền nhau, lúc thì có ký tự ngăn cách như dấu cách, dấu chấm, lúc thì thiếu số 0 ở đầu

CHIA SẺ CÔNG THỨC EXCEL TÁCH SỐ ĐIỆN THOẠI TRONG VĂN BẢN

Bước 1: Dùng hàm SUBSTITUTE để loại bỏ các ký tự ngăn cách số (2 dấu: dấu cách và dấu chấm)

Công thức tại ô B1

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

Nếu có nhiều dấu ngăn cách hơn thì tiếp tục lồng thêm hàm SUBSTITUTE bên ngoài

Bước 2: Dùng công thức tách số điện thoại từ ô B1

Công thức tại ô C1

=TEXT(AGGREGATE(14,6,--MID(B1,ROW($1:$99),9),1),"0000000000")

Lưu ý: Trong thực tế việc nhập thông tin sđt trong chuỗi phát sinh rất nhiều tình huống khác như trong chuỗi có cả số tiền lớn, có mã số thuế,....thì công thức có thể trả về sai kết quả

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel chèn số 0 phía trước cho đủ số ký tự

 Trong nhiều trường hợp các mã số có số 0 đằng trước, nhưng do người dùng không để dạng text trước khi nhập lên các số 0 này sẽ tự mất khi nhấn Enter, hoặc do người dùng cố tình bỏ số 0. Ví dụ: Số điện thoại, mã số thuế, số hóa đơn,...khi đó người dùng cần đưa về số ký tự cho đồng bộ

CHIA SẺ CÔNG THỨC EXCEL CHÈN SỐ 0 PHÍA TRƯỚC CHO ĐỦ SỐ KÝ TỰ

Trường hợp 1: Dữ liệu nhập hoàn toàn là các con số (không có ký tự text trong chuỗi)

Giả sử muốn hiển thị đủ 7 chữ số

Công thức dùng hàm TEXT:

=TEXT(A2,"0000000")

Công thức dùng hàm RIGHT:

=RIGHT(REPT(0,7)&A2,7)

Trường hợp 2: Dữ liệu nhập có chứa cả ký tự text và số

Giả sử muốn hiển thị đủ 7 ký tự

Công thức:

=RIGHT(REPT(0,7)&A2,7)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách số ra khỏi chuỗi

 Trong nhiều tình huống bố cục dữ liệu trong nhập liệu, tạo mã,...mà có những dữ liệu dạng text chuỗi và trong đó có chứa con số, cần tách số riêng khỏi chuỗi

CHIA SẺ CÔNG THỨC EXCEL TÁCH SỐ TỪ TRONG CHUỖI KÝ TỰ

Trường hợp 1: Các con số ở bên trái chuỗi ký tự

Công thức:

=LEFT(A2,AGGREGATE(14,6,FIND(ROW($1:$10)-1,A2),1))

Trường hợp 2: Các con số bên phải chuỗi ký tự

Công thức: 

=MID(A2,AGGREGATE(15,6,FIND(ROW($1:$10)-1,A2),1),99)

Trường hợp 3: Các son số và text nằm lộn xộn trong chuỗi ký tự

Công thức:

TEXTJOIN("",TRUE,IFERROR(--MID(A2,ROW($1:$99),1),""))

Lưu ý: Công thức tách số từ chuỗi ký tự lộn xộn này chỉ áp dụng với Office 2019 trở lên

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách chuỗi ngăn cách bởi ký tự #&

 Trong nhiều hệ thống phần mềm, khi xuất dữ liệu chuỗi ra Excel thì các đoạn ký tự được ngăn cách nhau bởi dấu #&. Khi đó người dùng cần tách dữ liệu trước, giữa và sau các ký tự này

Có một số phương pháp tách như:

- Flash Fill

- Text To Columns

- VBA

- Power Query

CHIA SẺ CÁCH TÁCH CHUỖI KÝ TỰ NGĂN CÁCH BỞI DẤU #&

Tình huống giả lập như hình ảnh, bắt đầu dữ liệu từ A2

Công thức tách chuỗi ký tự bên trái

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

Công thức tách chuỗi ký tự ở giữa

=TRIM(MID(SUBSTITUTE(A2,"#&",REPT(" ",999)),999,999))

Công thức tách chuỗi ký tự bên phải

=TRIM(RIGHT(SUBSTITUTE(A2,"#&",REPT(" ",999)),999))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel loại bỏ ký tự thừa trùng lặp

 Trong nhiều trường hợp xuất dữ liệu từ phần mềm, do copy paste hoặc do người dùng nhập liệu thì có xuất hiện ký tự thừa trùng lặp

Ví dụ: Ở cột A giữa các ký tự ngăn cách nhau bởi dấu phẩy nhưng có quá nhiều dấu phẩy thừa ở giữa. Yêu cầu chỉ đưa về 1 dấu phẩy

CÔNG THỨC LOẠI BỎ KÝ TỰ THỪA TRÙNG LẶP

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,","," "))," ",",")

Trường hợp dữ liệu sử dụng dấu khác, ví dụ dấu trừ (-) thì người dùng thay thế dấu "," trong công thức bằng dấu "-"

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,"-"," "))," ","-")

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách tên bị dính chữ

 Trong nhiều tình huống dữ liệu xuất ra từ phần mềm, do copy hoặc lỗi dính chữ do khác phiên bản Excel thì phần họ tên bị dính liền

Bài viết chia sẻ về dùng công thức tách tên trong trường hợp không dính chữ TẠI ĐÂY

Trường hợp họ và tên bị dính chữ, giả dữ dữ liệu bắt đầu tại ô A2 thì:

Công thức tách tên tại ô B2 là:

=MID(A2,AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(A2)))/EXACT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),1),99)

Công thức tách phần còn lại họ và tên lót sau, bỏ phần tên tại C2: 

=LEFT(A2,LEN(A2)-LEN(B2))

Công thức tách tên lót 1 tại D2:

=MID(C2,AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(C2)))/EXACT(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1),UPPER(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1))),1),99)

Công thức lấy phần còn lại sau khi bỏ tên lót 1 tại ô E2:

=LEFT(C2,LEN(C2)-LEN(D2))

Công thức tách tiếp tên lót 2 nếu có tại ô F2:

=IF(MID(E2,AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(E2)))/EXACT(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),UPPER(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1))),1),99)=E2,"",MID(E2,AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(E2)))/EXACT(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),UPPER(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1))),1),99))

Công thức lấy ra phần họ còn lại tại ô G2:

=IF(LEN(LEFT(E2,LEN(E2)-LEN(F2)))=LEN(E2),E2,LEFT(E2,LEN(E2)-LEN(F2)))

Công thức gộp thành họ và tên đầy đủ không bị dính tại ô H2:

=TRIM(G2&" " & F2 &  " " & D2 & " " & B2)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Cách nối chuỗi ký tự từ nhiều ô trong Excel

 Trong Excel, nhiều tình huống người dùng nhập liệu ở các ô rời rạc và có nhu cầu nối lại thành 1 ô để tạo danh sách trong ô đó

Videos dưới dây sẽ hướng dẫn 3 cách nối dữ liệu từ các ô như sau:

Cách 1: Dùng phím F9 (Fn+F9)

Cách 2: Dùng công thức nối &

Cách 3: Dùng tính năng Justify trong Excel

Ngoài ra, với người dùng sử dụng bản Excel 2019 trở lên sẽ có hàm TEXTJOIN thì công thức đơn giản như sau

=TEXTJOIN(",",TRUE,A6:A20)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Các hàm xử lý ký tự thừa trong Excel

Một số tình huống có xuất hiện ký tự thừa trong bảng tính Excel như:

- Người dùng vô tình nhấn nhiều lần dấu cách ở trong chuỗi, hoặc ở cuối chuỗi mà mắt thường không nhìn thấy

- Do dữ liệu xuất ra từ phần có khoảng trống thừa, copy từ trên web,...

- Nhập liệu Alt xuống dòng nhiều lần

....

Trong các trường hợp này thì cần phải xử lý các ký tự thừa để phục vụ làm công thức, báo cáo, in ấn,...

Các hàm xử lý ký tự thừa hay dùng như sau:

Hàm TRIM: Hàm TRIM loại bỏ khoảng trống thừa ở đầu, cuối văn bản. Ở giữa đoạn văn bản nếu từ 2 khoảng trống nằm cạnh nhau thì hàm TRIM sẽ đưa về 1 khoảng trống

Ví dụ: =TRIM("   Excel   Thỉnh  Vũ    ") thì kết quả sẽ trả về là Excel Thỉnh Vũ

Lưu ý: Hàm TRIM chỉ loại được khoảng trống loại 32 (CHAR(32)) - Thường với dữ liệu có phát sinh khoảng trống thừa do gõ tay. Còn trường hợp khoảng trốn loại 160 (CHAR(160)) thì hàm TRIM không loại được. => Đọc tiếp bên dưới dùng hàm SUBSTITUTE xử lý cho trường hợp này

Hàm CLEAN: Hàm CLEAN loại bỏ những ký tự thừa không in được

Các ký tự không in được hàm CLEAN xử lý thuộc dải ký tự từ 1-32, trong đó ký tự không in được thừa hay lặp là dấu Alt+Enter xuống dòng - Tương đương với ký tự CHAR(10). Ví dụ ở cuối văn bản người dùng nhấn Alt +Enter xuống dòng thừa

Hàm SUBSTITUTE: Hàm này bản chất là thay thế ký tự. Để loại bỏ ký tự không mong muốn hiển thị thì thay thế bằng khoảng trống. Ví dụ trường hợp khoảng trống loại CHAR(160) mà hàm TRIM không xử lý được thì công thức như sau

=SUBSTITUTE(A1,CHAR(160),"")

Nhưng công thức này sẽ thay thế toàn bộ khoảng trống. Nếu muốn giữ trong chuỗi có 1 khoảng trống ngăn cách giữa các từ thì công thức là:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách chuỗi theo ký tự xuống dòng trong ô Alt Enter

 Trong nhập liệu thực tế thì thường phát sinh tình huống nhập liệu vào trong ô nhưng ngăn cách giữa các khối bởi dấu xuống dòng trong ô (Alt+Enter). Ví dụ như nhập các số điện thoại, danh sách người phụ thuộc, mô tả các đặc tính sản phẩm,....Và khi đó sẽ cần tách khối này ra các ô độc lập. 

Ngoài cách tách phổ biến hay dùng là Text To Columns thì bài viết này sẽ hướng dẫn tách bằng công thức. 

Giả sử ô A1 chứa các danh sách các số điện thoại được ngăn cách nhau bởi dấu Alt+Enter

Trong Excel thì dấu Alt Enter xuống dòng tương đương với mã code là 10 và hàm CHAR(10) đưa mã code 10 sang dấu xuống dòng. 

Thì công thức tại ô B1 như sau:

=TRIM(MID(SUBSTITUTE(CHAR(10)&$A1,CHAR(10),REPT(" ",500)),500*COLUMN(A:A),500))

Trong công thức này thì người dùng chỉ cần thay địa chỉ $A1 bằng địa chỉ tương ứng của dữ liệu trên bảng tính, các thông số khác dữ nguyên. => Thực hiện kéo công thức sang ngang để tách dữ liệu ra các cột khác nhau

Trường hợp muốn tách ra các dòng khác nhau thì công thức là:

=TRIM(MID(SUBSTITUTE(CHAR(10)&A$1,CHAR(10),REPT(" ",500)),500*ROW(1:1),500))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel đếm danh sách giá trị trong 1 chuỗi

 Trong nhập liệu Excel, nhiều trường phát sinh dạng nhập liệu kiểu danh sách trong 1 ô, mỗi một đối tượng (phần tử) ngăn cách nhau bởi dấu phẩy hoặc theo ký tự nào đó

Lúc này đặt ra bài toán: Cần đếm trong chuỗi danh sách đó có bao nhiêu phần tử. Giả sử sử dụng dấu phẩy (,) để ngăn cách giữa các phần tử thì SỐ PHẦN TỬ = SỐ DẤU PHẨY +1

Khi đó công thức như sau:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

Khi áp dụng công thức này thì người dùng chỉ cần thay địa chỉ ô tham chiếu đến và dấu phẩy bằng dấu ngăn cách tương ứng là được

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn ghép chuỗi với hàm TEXT mà không mất định dạng trường dữ liệu trong Excel

 Trong ứng dụng Excel vào công việc thì người dùng thường xuyên phải sử dụng phương pháp ghép chuỗi ký tự từ nhiều ô với dấu &, hàm CONCATENATE, hàm CONCAT hoặc TEXTJOIN. Tuy nhiên khi dữ liệu cần phép vào chuỗi là con số, ngày tháng hoặc thời gian thì hay xảy ra tình trạng mất định dạng

Ví dụ: 

- Khi ghép 1 con số vào chuỗi thì con số đó sẽ bị mất dấu ngăn cách phần hàng ngàn và phần thập phân

- Khi ghép ngày tháng vào chuỗi thì định dạng ngày tháng sẽ bị mất và biến thành 1 con số

- Khi ghép thời gian vào chuỗi thì định dạng thời gian cũng bị mất

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM TEXT ĐỂ GHÉP CHUỖI

Tình huống 1: Ghép 1 con số vào chuỗi

Giả sử ô A1 là con số thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"#,##0")

Trường hợp A1 là một con số thập phân (có 2 số thập phân đằng sau) thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"#,##0.00")

Tình huống 2: Ghép ngày tháng Date vào chuỗi

Giả sử ô A1 là giá trị ngày tháng Date thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"dd/mm/yyyy")

Trường hợp ghép chuỗi dạng từ ngày .... đến ngày ...., A1 và B1 là 2 ô chứa giá trị ngày tháng thì công thức là:

="Từ ngày " & TEXT(A1,"dd/mm/yyyy") & " đến ngày " & TEXT(B1,"dd/mm/yyyy")

Trường hợp ghép chuỗi hiển thị đầy đủ ngày...tháng...năm... thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"""Ngày"" dd ""tháng"" mm ""năm"" yyyy")

Tình huống 3: Ghép thời gian Time vào chuỗi

Giả sử ô A1 là giá trị thời gian Time thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"hh:mm")

Trường hợp muốn hiển thị dạng AM/PM thì công thức là:

="Chuỗi văn bản tùy ý " & TEXT(A1,"hh:mm AM/PM")

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp
{Đt+Zalo} - 038 696 1334

Xem chi tiết

Chia sẻ công thức Excel tách tên Tỉnh thành từ chuỗi địa chỉ

 Trong nhiều lĩnh vực như quản lý nhân sự, nhân khẩu, thông tin xuất xứ,...thì cần lấy tên tỉnh từ chuỗi địa chỉ để quản trị thông tin, trích lọc dữ liệu và tổng hợp báo cáo theo tỉnh/thành

Bài viết sau đây sẽ chia sẻ công thức tách tên tỉnh/thành ngắn gọn và dễ sử dụng như sau:

Giả sử ô A2 chứa chuỗi địa chỉ tỉnh thành (địa chỉ luôn ở bên tay phải của chuỗi), sử dụng dấu phẩy (,) ngăn cách trong chuỗi

Lập công thức tại ô B2 để lấy ra tên Tỉnh/Thành:

Công thức:

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

Cách sử dụng công thức:

- Thay ô A2 tương ứng

- Nếu chuỗi địa chỉ không dùng dấu phẩy (,) mà dùng dấu ; hoặc dấu - thì thay bằng dấu tương tứng. Ví dụ sử dụng dấu - thì công thức là: =TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",99)),99))

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết