Căng nhỉ
Mình cũng làm liều vào giải thích, đao gươm, rượu thịt xin phép không bàn đến.
=TRIM(MID(SUBSTITUTE($C$3,"|",REPT(" ",100)),(COLUMN(A1)-1)*100+1,100))
Chuỗi ban đầu "
CCCD|
CMND|
HOTEN|
NAMSINH|
GIOITINH|
DIACHI|
NGAYCAP"
Vì ban đầu không biết rõ vị trí của ký tự phân tách "|" (delimiter). Dữ liệu giữa các phân cách có thể dài ngắn và không cố định. Nên ta không dùng hàm MID ngay được.
Do vậy công thức trên sẽ làm 1 xíu thủ thuật như sau:
Bước 1 : SUBSTITUTE($C$3,"|",REPT(" ",100)
+ Thay thế "|" thành 100 khoảng trắng "Space".
(1) "CCCD|CMND|......"
sẽ biến thành
(1') "
CCCD .........(100 khoảng trắng)..........
CMND .........(100 khoảng trắng)..........
HOTEN .... "
+ Vậy tại sao phải làm như vậy? Khi xem xét kỹ chuỗi (1') ta thấy
ký tự đầu tiên đến ký tự 100 sẽ chứa CCCD và 96 khoảng trắng. Luôn nhỏ hơn 100 khoảng trắng nên không bao giờ lẹm vào dữ liệu thứ 2(CMND);
Tổng quát hóa là các dữ liệu cần tách sẽ Ở VỊ TRÍ
CCCD nằm trong đoạn 1 - 100
CMND nằm trong đoạn 101 - 200
HOTEN nằm trong đoạn 201 - 300
NAMSINH nằm trong đoạn 301 - 400
Hàm REPT(" ",100) sẽ tạo ra 100 khoảng trắng, thay vì gõ tạch tạch tạch 100 lần
SUBSTITUTE($C$3,"|",REPT(" ",100) sẽ tìm trong chuỗi chứa tại ô C3, cứ thấy "|" thì thay bằng 100 dấu khoảng trắng đã tạo bởi hàm REPT
Bước 2 : MID(Chuỗi ở bước 1 sau khi thay "|" bằng "100 khoảng trắng",(COLUMN(A1)-1)*100+1,100))
Như ở trên ta biết các thông tin cần lấy nó nằm trong vùng
1 - 100; 101 - 200; .... vậy làm sao lấy nó ra
Ứng dụng dùng hàm MID, chỉ ra việc lấy cái thông tin tại vị trí 1; 101; 201 với độ dài 100 vì với độ dài 100 sẽ không bao giờ lẹm vào thông tin phía sau.
thay vì ta phải dùng
MID("chuỗi", 1, 100) để tìm ra CCCD
MID("chuỗi", 101, 100) để tìm ra CMND
thứ tự 1, hay 101 ta lợi dụng thứ tự của cột
Trong trang tính, COLUMN(A1) sẽ trả về thứ tự cột A trong bảng tính là "1"; COLUMN(B1) sẽ trả về thứ tự cột B trong bảng tính là "2"
Lợi dụng việc này ta dùng
(COLUMN(A1)-1)*100+1 = 1
(COLUMN(B1)-1)*100+1 = 101
.. khi ta auto fill (kéo công thức của ô hiện hữu sang phải) chỗ A1 sẽ biến thành B1, ta mặc nhiên có 1, 101, 201, 301...
Sau khi kết thúc bước 2 ta thu được thông tin chứa trong từng cột: "
một ít dấu khoảng trắng ... CCCD ... một mớ dấu khoảng trắng"
mệt ...
Bước 3 : TRIM( chuỗi thu được từ bước 2)
hàm TRIM thì rất đơn giản, sẽ xóa toàn bộ khoảng trắng 2 đầu của chuỗi (TEXT) được chỉ định, 2 khoảng trắng trở lên trong chuỗi thì giảm về chỉ còn 1;
Vậy từ chuỗi bước 2 "
một ít dấu khoảng trắng ... CCCD ... một mớ dấu khoảng trắng" sau khi cắt gọt bằng TRIM thu được "CCCD" không còn khoảng trắng 2 đầu.
Diễn giải thì nhiều nhưng bạn làm từng bước sẽ thấy nó cũng đơn giản thôi. Quan trọng là người ban đầu nghĩ ra cái việc này. Cá nhân mình thấy việc đó quá siêu
