{"id":24069,"date":"2021-05-07T14:42:05","date_gmt":"2021-05-07T07:42:05","guid":{"rendered":"https:\/\/ben.com.vn\/tin-tuc\/?p=24069"},"modified":"2021-05-07T14:42:05","modified_gmt":"2021-05-07T07:42:05","slug":"ham-tach-ten-trong-excel","status":"publish","type":"post","link":"https:\/\/ben.com.vn\/tin-tuc\/ham-tach-ten-trong-excel\/","title":{"rendered":"C\u00e1c h\u00e0m t\u00e1ch t\u00ean trong Excel v\u00e0 h\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng chi ti\u1ebft"},"content":{"rendered":"

Gi\u1ea3 s\u1eed v\u00ec l\u00fd do n\u00e0o \u0111\u00f3 m\u00e0 b\u1ea1n c\u1ea7n t\u00e1ch ri\u00eang bi\u1ec7t h\u1ecd v\u00e0 t\u00ean c\u1ee7a nh\u1eefng ng\u01b0\u1eddi \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea trong b\u1ea3ng t\u00ednh Excel. Trong Excel c\u00f3 nhi\u1ec1u c\u00e1ch v\u00e0 h\u00e0m t\u00e1ch t\u00ean<\/strong> m\u00e0 b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng \u0111\u1ec3 th\u1ef1c hi\u1ec7n t\u00e1c v\u1ee5 n\u00e0y. B\u1ea1n \u0111\u1ecdc c\u00f9ng tham kh\u1ea3o ti\u1ebfp b\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y c\u1ee7a Ben Computer \u0111\u1ec3 t\u00ecm hi\u1ec3u chi ti\u1ebft nh\u00e9.<\/p>\n

T\u00e1ch t\u00ean l\u00e0 m\u1ed9t trong nh\u1eefng thao t\u00e1c ph\u1ed5 bi\u1ebfn m\u00e0 ng\u01b0\u1eddi d\u00f9ng Excel th\u01b0\u1eddng hay ph\u1ea3i x\u1eed l\u00fd. Thay v\u00ec thao t\u00e1c b\u1eb1ng tay v\u1eeba m\u1ea5t th\u1eddi gian v\u00e0 d\u1ec5 b\u1ecb nh\u1ea7m l\u1eabn, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c h\u00e0m t\u00e1ch t\u00ean trong Excel<\/strong> \u0111\u1ec3 thao t\u00e1c nhanh ch\u00f3ng v\u00e0 \u0111\u01a1n gi\u1ea3n.<\/p>\n

C\u00e1ch t\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns<\/strong><\/h2>\n

Trong tr\u01b0\u1eddng h\u1ee3p n\u1ebfu c\u1ed9t b\u1ea3ng t\u00ednh ch\u1ee9a c\u00f9ng c\u1ea3 h\u1ecd t\u00ean v\u00e0 b\u1ea1n mu\u1ed1n t\u00e1ch t\u00ean, h\u1ecd th\u00e0nh c\u00e1c c\u1ed9t ri\u00eang bi\u1ec7t. Th\u1ef1c hi\u1ec7n theo c\u00e1c b\u01b0\u1edbc d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

B\u01b0\u1edbc 1<\/em>:<\/strong> Ch\u1ecdn c\u1ed9t ch\u1ee9a h\u1ecd t\u00ean m\u00e0 b\u1ea1n mu\u1ed1n t\u00e1ch t\u00ean trong b\u1ea3ng t\u00ednh.<\/p>\n

B\u01b0\u1edbc 2:<\/strong> Ti\u1ebfp theo truy c\u1eadp\u00a0tab Data<\/strong>\u00a0=>\u00a0Data Tools group<\/strong>\u00a0r\u1ed3i click ch\u1ecdn\u00a0Text to Columns.<\/strong><\/p>\n

\"T\u00e1ch
T\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns – \u1ea2nh 1<\/figcaption><\/figure>\n

B\u01b0\u1edbc 3:<\/em>\u00a0<\/strong>Tr\u00ean m\u00e0n h\u00ecnh s\u1ebd hi\u1ec3n th\u1ecb c\u1eeda s\u1ed5 Convert Text to Columns Wizard, t\u1ea1i \u0111\u00e2y t\u00ecm v\u00e0 ch\u1ecdn t\u00f9y ch\u1ecdn\u00a0Delimited<\/strong>\u00a0r\u1ed3i click ch\u1ecdn\u00a0Next.<\/strong><\/p>\n

\"T\u00e1ch
T\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns – \u1ea2nh 2<\/figcaption><\/figure>\n

B\u01b0\u1edbc 4<\/em>:<\/strong> Ti\u1ebfp theo, ch\u1ecdn m\u1ed9t ho\u1eb7c nhi\u1ec1u d\u1ea5u ph\u00e2n c\u00e1ch r\u1ed3i click ch\u1ecdn\u00a0Next.<\/strong><\/p>\n

\"T\u00e1ch
T\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns – \u1ea2nh 3<\/figcaption><\/figure>\n

B\u01b0\u1edbc 5:<\/em>\u00a0<\/strong>Cu\u1ed1i c\u00f9ng ch\u1ecdn \u0111\u1ecbnh d\u1ea1ng v\u00e0 \u0111\u00edch d\u1eef li\u1ec7u r\u1ed3i click ch\u1ecdn\u00a0Finish<\/strong>\u00a0\u0111\u1ec3 ho\u00e0n t\u1ea5t qu\u00e1 tr\u00ecnh.<\/p>\n

\"T\u00e1ch
T\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns – \u1ea2nh 4<\/figcaption><\/figure>\n

Sau khi qu\u00e1 tr\u00ecnh ho\u00e0n t\u1ea5t, t\u00ean trong b\u1ea3ng s\u1ebd \u0111\u01b0\u1ee3c t\u00e1ch th\u00e0nh c\u00e1c c\u1ed9t ri\u00eang bi\u1ec7t nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

\"T\u00e1ch
T\u00e1ch t\u00ean trong Excel b\u1eb1ng l\u1ec7nh Text to Columns – \u1ea2nh 5<\/figcaption><\/figure>\n

H\u00e0m t\u00e1ch t\u00ean trong Excel v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/strong><\/h2>\n

Ngo\u00e0i c\u00e1ch tr\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m t\u00e1ch t\u00ean trong Excel<\/strong> \u0111\u1ec3 t\u00e1ch t\u00ean.<\/p>\n

1. H\u00e0m t\u00e1ch t\u00ean trong Excel<\/strong><\/h3>\n

H\u00e0m t\u00e1ch t\u00ean trong Excel c\u00f3 c\u00fa ph\u00e1p, c\u00f4ng th\u1ee9c nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

LEFT(cell, SEARCH(\u201d \u201c, cell) \u2013 1)<\/strong><\/p><\/blockquote>\n

Trong \u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m SEARCH ho\u1eb7c FIND \u0111\u1ec3 l\u1ea5y v\u1ecb tr\u00ed c\u1ee7a k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng (\u2018\u201d) trong m\u1ed9t \u00f4, t\u1eeb \u0111\u00f3 tr\u1eeb \u0111i 1 \u0111\u1ec3 lo\u1ea1i kho\u1ea3ng tr\u1eafng. Gi\u00e1 tr\u1ecb n\u00e0y \u0111\u01b0\u1ee3c cung c\u1ea5p cho h\u00e0m Left d\u01b0\u1edbi d\u1ea1ng k\u00fd t\u1ef1 s\u1ed1 \u0111\u01b0\u1ee3c tr\u00edch xu\u1ea5t, b\u1eaft \u0111\u1ea7u \u1edf b\u00ean tr\u00e1i c\u1ee7a chu\u1ed7i.<\/p>\n

2. H\u00e0m t\u00e1ch h\u1ecd trong Excel<\/strong><\/h3>\n

C\u00fa ph\u00e1p h\u00e0m t\u00e1ch h\u1ecd trong Excel c\u00f3 d\u1ea1ng nh\u01b0 sau:<\/p>\n

RIGHT(cell, LEN(cell) \u2013 SEARCH(\u201d \u201c, cell))<\/strong><\/p><\/blockquote>\n

Trong c\u00f4ng th\u1ee9c tr\u00ean, ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m Search \u0111\u1ec3 t\u00ecm v\u1ecb tr\u00ed c\u1ee7a d\u1ea5u c\u00e1ch v\u00e0 tr\u1eeb s\u1ed1 \u0111\u00f3 kh\u1ecfi t\u1ed5ng \u0111\u1ed9 d\u00e0i c\u1ee7a chu\u1ed7i (do h\u00e0m Len tr\u1ea3 v\u1ec1) v\u00e0 nh\u1eadn h\u00e0m Right \u0111\u1ec3 tr\u00edch xu\u1ea5t c\u00e1c k\u00fd t\u1ef1 \u0111\u00f3 t\u1eeb ph\u00eda b\u00ean ph\u1ea3i c\u1ee7a chu\u1ed7i.<\/p>\n

C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m t\u00e1ch t\u00ean v\u00e0 h\u1ecd trong Excel<\/strong><\/h3>\n

Cho b\u1ea3ng d\u1eef li\u1ec7u nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y v\u00e0 y\u00eau c\u1ea7u l\u00e0 s\u1eed d\u1ee5ng c\u00e1c h\u00e0m t\u00e1ch t\u00ean \u0111\u1ec3 t\u00e1ch h\u1ecd t\u00ean trong \u00f4 A2 th\u00e0nh c\u00e1c c\u1ed9t ri\u00eang l\u1ebb.<\/p>\n

C\u00f4ng th\u1ee9c h\u00e0m t\u00e1ch t\u00ean Excel c\u00f3 d\u1ea1ng nh\u01b0 sau:<\/p>\n

=LEFT(A2,SEARCH(\u201d \u201c,A2)-1)<\/strong><\/p><\/blockquote>\n

C\u00f4ng th\u1ee9c h\u00e0m t\u00e1ch h\u1ecd Excel c\u00f3 d\u1ea1ng nh\u01b0 sau:<\/p>\n

=RIGHT(A2,LEN(A2)-SEARCH(\u201d \u201c,A2,1))<\/strong><\/p><\/blockquote>\n

Nh\u1eadp c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean v\u00e0o \u00f4 B2 v\u00e0 C2 t\u01b0\u01a1ng \u1ee9ng r\u1ed3i k\u00e9o xu\u1ed1ng c\u00e1c c\u1ed9t. K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 c\u00f3 d\u1ea1ng nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

\"C\u00e1ch
C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m t\u00e1ch t\u00ean v\u00e0 h\u1ecd trong Excel<\/figcaption><\/figure>\n

C\u00e1ch t\u00e1ch h\u1ecd t\u00ean kh\u00e1c trong Excel<\/strong><\/h2>\n

M\u1ed9t c\u00e1ch kh\u00e1c \u0111\u1ec3 t\u00e1ch h\u1ecd t\u00ean trong Excel l\u00e0 th\u00f4ng qua h\u1ed9p tho\u1ea1i t\u00ecm ki\u1ebfm v\u00e0 thay th\u1ebf. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y, b\u1ea1n th\u1ef1c hi\u1ec7n theo c\u00e1c b\u01b0\u1edbc d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

B\u01b0\u1edbc 1<\/em>:<\/strong> Sao ch\u00e9o to\u00e0n b\u1ed9 n\u1ed9i dung t\u1eeb c\u1ed9t H\u1ecc V\u00c0 T\u00caN sang c\u1ed9t T\u00caN b\u1eb1ng c\u00e1ch nh\u1ea5n Ctrl + A \u0111\u1ec3 ch\u1ecdn v\u00e0 nh\u1ea5n Ctrl + C \u0111\u1ec3 sao ch\u00e9p.<\/p>\n

B\u01b0\u1edbc 2<\/em>:<\/strong> Ti\u1ebfp theo khoanh v\u00f9ng c\u00e1c t\u00ean c\u1ea7n t\u00e1ch h\u1ecd v\u00e0 t\u00ean \u1edf c\u1ed9t T\u00caN.<\/p>\n

B\u01b0\u1edbc 3:<\/strong> M\u1edf h\u1ed9p tho\u1ea1i t\u00ecm ki\u1ebfm v\u00e0 thay th\u1ebf b\u1eb1ng c\u00e1ch nh\u1ea5n t\u1ed5 h\u1ee3p ph\u00edm\u00a0Ctrl + H.<\/strong><\/p>\n

B\u01b0\u1edbc 4:<\/em><\/strong>\u00a0Tr\u00ean c\u1eeda s\u1ed5 hi\u1ec3n th\u1ecb tr\u00ean m\u00e0n h\u00ecnh, nh\u1eadp \u201c* \u201d (d\u1ea5u hoa th\u1ecb + kho\u1ea3ng tr\u1eafng) v\u00e0o khung Find What r\u1ed3i click ch\u1ecdn\u00a0Replace All<\/strong>\u00a0l\u00e0 xong.<\/p>\n

Tr\u00ean \u0111\u00e2y Ben Computer v\u1eeba gi\u1edbi thi\u1ec7u cho b\u1ea1n m\u1ed9t s\u1ed1 c\u00e1ch v\u00e0 h\u00e0m t\u00e1ch t\u00ean trong Excel<\/strong>. Hy v\u1ecdng v\u1edbi nh\u1eefng th\u00f4ng tin tr\u00ean \u0111\u00e2y s\u1ebd gi\u00fap b\u1ea1n l\u00e0m ch\u1ee7 \u0111\u01b0\u1ee3c h\u00e0m Excel n\u00e0y. V\u1ec1 c\u01a1 b\u1ea3n c\u00e1c h\u00e0m t\u00e1ch t\u00ean v\u00e0 t\u00e1ch h\u1ecd trong Excel l\u00e0 c\u00e1c h\u00e0m k\u1ebft h\u1ee3p, m\u1ed7i h\u00e0m th\u1ef1c hi\u1ec7n c\u00e1c ch\u1ee9c n\u0103ng kh\u00e1c nhau.<\/p>\n

Xem th\u00eam c\u00e1c b\u00e0i vi\u1ebft li\u00ean quan:<\/p>\n