Cách xóa mã quốc gia khỏi số điện thoại trong Google Sheets

Hôm nay tôi có lần đầu tiên - tôi được yêu cầu chuẩn bị một số dữ liệu để tải lên và số điện thoại là một trong các trường. Thật không may, các số cần được chia thành mã vùng, tiền tố, hậu tố và phần mở rộng. ừ. Thêm vào đó, một số số điện thoại đều là số, trong khi những số khác có dấu chấm, dấu gạch ngang và/hoặc dấu ngoặc đơn trong đó. Vì vậy, cuối cùng tôi đã sử dụng 2 phương pháp để chuẩn bị cho việc tải dữ liệu lên.  

Trong bài đăng này, trước tiên chúng ta sẽ xem xét cách sử dụng các hàm LEFT và RIGHT - các hàm hữu ích nếu bạn chỉ cần chọn một số mục nhập. Sau đó chúng ta sẽ xem lại cách sử dụng chức năng SPLIT, cùng với ứng dụng này của nó.  

Chức năng LEFT và RIGHT

Đây là cách nhanh chóng để chia các số điện thoại không có dấu chấm câu hoặc văn bản được nhập cùng với chúng. Đầu tiên - các chức năng

• TRÁI - chỉ sao chép số lượng ký tự được chỉ định bắt đầu ở phía bên trái của mục nhập
• PHẢI - chỉ sao chép số ký tự được chỉ định bắt đầu ở bên phải của mục nhập

Cả hai đều có định dạng (dữ liệu, #) trong đó

• dữ liệu là dữ liệu nên được sử dụng (thường xuyên nhất là tham chiếu ô)
• # là số ký tự cần sao chép

Vì vậy, một vài ví dụ

=LEFT(A5, 3) sẽ cho kết quả là 239

=LEFT (C7, 4) sẽ dẫn đến 'Jenn'

=RIGHT(B1, 3) sẽ cho kết quả là 'ith'

=RIGHT(A2, 4) sẽ cho kết quả là 9324

Dưới đây là một số số điện thoại mẫu.   

Đối với các mục nhập chỉ bằng số của tôi, tôi phải sử dụng các công thức khác nhau cho từng phần của số

• Mã vùng. =TRÁI(A2, 3)
• Tiền tố (kết hợp để lấy ở giữa). =RIGHT(TRÁI(A2, 7), 3)
• hậu tố. =RIGHT(A2, 4) cách khác với phần mở rộng =RIGHT(LEFT(A2, 10), 4)

Điền vào các công thức thích hợp (hoặc sao chép các ô và dán chúng vào các hàng khác) sẽ tạo ra các công thức khác

Chức năng TÁCH

Tôi đã thực sự chia sẻ về chức năng SPLIT trước đây.  

Điều thú vị là tôi đã học được một cách mới để sử dụng nó khi làm việc này. Vì tôi muốn có thể xóa nhiều ký tự khỏi các mục nhập khác để đưa chúng xuống chỉ còn số, nên tôi đã sử dụng phân tách. Nhưng làm thế nào để làm điều đó khi mỗi mục có thể khác nhau? .  

Hàm SPLIT có dạng

=SPLIT(dữ liệu, vị trí chia)

Trước đây, tôi đã làm điều gì đó như =SPLIT(A5, " ") để phân tách tại các khoảng trắng. Nhu cầu mới của chúng tôi liên quan nhiều hơn một chút vì có thể có nhiều ký tự có thể phá vỡ dữ liệu - đặc biệt là dấu gạch ngang, dấu chấm và dấu ngoặc đơn. Từ Extend Office, tôi biết được rằng bạn có thể đặt nhiều ký tự cho vị trí phân tách

Vì vậy, công thức của tôi đã kết thúc là
=SPLIT(A5, "abcdefghijklmnopqrstuvwxyz. ()-"
Điều này sẽ đặt phần tách ở bất kỳ chữ cái, dấu chấm, dấu ngoặc đơn, dấu gạch ngang hoặc dấu cách. Sau đó, tôi đã phát hiện ra rằng tôi thậm chí đã có một người sử dụng dấu phẩy, vì vậy đã thêm nó vào danh sách kết hợp.  

Như bạn có thể thấy, nó chia số điện thoại cho tôi một cách hợp lý -

Điền xuống , sau đó áp dụng nó cho các hàng khác -

Với một chút bí quyết và nỗ lực, bạn có thể định dạng số điện thoại trong Google Trang tính theo bất kỳ cách nào bạn muốn chúng xuất hiện

Rất tiếc, Google Trang tính không có cài đặt sẵn định dạng cho số điện thoại

Tuy nhiên, bạn có thể dễ dàng hoàn thành việc này bằng cách sử dụng một số bí quyết định dạng số và một chút phép thuật công thức

Quá trình áp dụng định dạng rất dễ dàng;

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách định dạng số điện thoại trong Google Trang tính và cách tạo định dạng số điện thoại tùy chỉnh của riêng bạn

Bài viết này bao gồm

Cách định dạng số điện thoại trong Google Sheets

Trong hướng dẫn này, chúng tôi sẽ thêm định dạng số điện thoại nội địa tiêu chuẩn của Hoa Kỳ vào các số điện thoại chưa định dạng mẫu

Thuật ngữ "không định dạng" đề cập đến việc làm việc với các số điện thoại chỉ là các chữ số. không có định dạng hiện có như dấu gạch ngang hoặc dấu chấm

Các bước sau đây cho biết cách định dạng số điện thoại trong Google Trang tính với dữ liệu chưa được định dạng. Ví dụ này sử dụng định dạng nội địa Hoa Kỳ. (123) 456-7890

1. Đánh dấu cột bạn muốn định dạng. Trong ví dụ này, chúng tôi đã đánh dấu cột B
2. Mở menu thả xuống “Định dạng”, chọn “Số”, chọn “Định dạng khác” và chọn “Định dạng số tùy chỉnh. ”
3. Nhập công thức định dạng vào trường “Định dạng số tùy chỉnh” và chọn “Áp dụng. ” Trong ví dụ này, chúng ta đang sử dụng công thức “(“###”)”” “###”-“####
4. Bây giờ cột sẽ định dạng số điện thoại cho phù hợp

Đôi khi bạn có thể phải làm việc với các số điện thoại đã được định dạng nhưng đang sử dụng sai định dạng

Xem phần sau trong hướng dẫn để tìm hiểu cách xóa định dạng khỏi số điện thoại nếu bạn nhận được chúng ở định dạng không chính xác

Cách viết công thức định dạng số điện thoại

Có rất nhiều cách khác nhau để định dạng số điện thoại theo các ngữ cảnh khác nhau trên toàn cầu. Hướng dẫn này sử dụng các định dạng phổ biến cho các số điện thoại ở Hoa Kỳ làm ví dụ

Bạn có thể tìm thấy định dạng mà bạn đang tìm kiếm với các ví dụ sau. Nếu không, họ có thể cung cấp đường cơ sở mà bạn có thể sửa đổi thành định dạng mà bạn đang tìm kiếm

Công thức định dạng ví dụ

• Phong cách. công thức = ví dụ
• Kiểu Mỹ Nội địa. “(“###”)”” “###”-“#### = (123) 456-7890
• US Style Nội địa với dấu gạch ngang. ###”-“###”-“#### = 123-456-7890
• phong cách Mỹ quốc tế. “+1-”###”-“###”-“#### = +1-123-456-7890
• Phong cách tiêu đề thư Hoa Kỳ. ###”. ”###”. ”#### = 123. 456. 7890
• Gọi từ Đức. “001-”###”-“###”-“#### = 001-123-456-7890
• Gọi từ Pháp. “191 “”###” “###” “#### = 191 123 456 7890

Công thức Kỹ thuật cho số điện thoại trong Google Sheets

Thoạt nhìn, hệ thống định dạng số tùy chỉnh của Google Trang tính có thể khá choáng ngợp

May mắn thay, các số điện thoại có xu hướng hiển thị trực tiếp nên bạn chỉ cần sử dụng hệ thống để thêm các ký tự được xác định trước tại các điểm cụ thể trong công thức

Xử lý số

Hệ thống công thức sử dụng “#” để chỉ định chữ số theo thứ tự trong công thức định dạng. “#” đầu tiên tham chiếu đến chữ số đầu tiên, “#” thứ hai tham chiếu đến chữ số thứ hai, v.v.

Nếu bạn đang định dạng số điện thoại gồm 10 chữ số, bạn sẽ sử dụng “#” 10 lần trong công thức của mình

Xử lý ký tự

Tùy thuộc vào định dạng, bạn sẽ thêm dấu ngoặc đơn, dấu gạch ngang, dấu chấm, dấu cách, tiền tố số và thông tin văn bản khác

Google Trang tính sử dụng dấu ngoặc kép để chỉ định thông tin văn bản giữa các số

Ví dụ: nếu bạn muốn thêm dấu gạch ngang giữa hai chữ số, bạn sẽ sử dụng công thức như #”-”#

Nếu bạn áp dụng công thức này cho một ô có giá trị “21” thì nó sẽ hiển thị như “2-1. ”

Ví dụ ký tự khóa.  

• # = đại diện cho chữ số tiếp theo theo thứ tự xuất hiện
• “” = văn bản hiển thị
• “ “ = thêm dấu cách
• “(“ và “)” = hiển thị dấu ngoặc đơn
• “-” = thêm dấu gạch ngang
• “. ” = thêm dấu chấm
• “001” = thêm các số được xác định trước
• “+1-” = thêm mã quốc tế +1 theo sau là dấu gạch ngang
  

Cách xóa định dạng hiện có khỏi số điện thoại

Tùy thuộc vào cách nhận thông tin số điện thoại trong bảng tính của bạn, bạn có thể gặp trường hợp định dạng không nhất quán. Một số số có thể có định dạng, trong khi những số khác chỉ là chữ số

Thay vì cố gắng đưa ra một công thức tính đến tất cả các loại định dạng số điện thoại, việc xóa tất cả định dạng hiện có và bắt đầu lại sẽ dễ dàng hơn

Các bước sau đây giải thích cách xóa định dạng bằng công thức =VALUE(REGEXREPLACE(*ô đã chọn*,”[^[. chữ số. ]]”, “”)) loại bỏ tất cả các giá trị không có chữ số khỏi một ô

Ví dụ này cho thấy cách xóa định dạng khỏi ba số được định dạng trước này

1. Tạo một cột trợ giúp bên cạnh các số được định dạng sẵn bằng cách nhấp chuột phải vào cột và chọn “Chèn 1 ngay. ”
2. Đặt tên cột là Header column
3. Nhập công thức “=VALUE(REGEXREPLACE(CELL,”[^[. chữ số. ]]”, “”)) trong cột trợ giúp. Ví dụ sử dụng công thức “=VALUE(REGEXREPLACE(D2,”[^[. chữ số. ]]”, “”))” trong ô E2 để tham chiếu ô D2
4. Kéo công thức xuống cột. Điều này sẽ điền phần còn lại của cột với các số điện thoại chưa được định dạng
5. Đánh dấu các số điện thoại từ cột trợ giúp và chọn “Sao chép” từ menu chuột phải
6. Chọn các giá trị số điện thoại cột ban đầu, nhấp chuột phải, chọn “Dán đặc biệt” và “Chỉ dán giá trị. ”
7. Google Trang tính sẽ điền vào cột gốc các số điện thoại chưa được định dạng

Vì vậy, đây là cách bạn có thể sử dụng định dạng tùy chỉnh đơn giản để định dạng số điện thoại trong Google trang tính

Tôi hy vọng bạn tìm thấy hướng dẫn này hữu ích

Các hướng dẫn khác về Google Trang tính mà bạn có thể thích

• Format Painter trong Google Sheets (Sao chép định dạng dễ dàng)
• Định dạng có điều kiện dựa trên một giá trị ô khác trong Google Trang tính
• Cách chuyển đổi văn bản thành số trong Google Sheets
• Làm cách nào để chuyển đổi thời gian quân sự trong Google Sheets?
• Cách kết hợp công thức và văn bản trong Google Sheets

Sumit

Chuyên gia bảng tính tại Điểm năng suất . + bài đăng

Sumit là Chuyên gia về Google Trang tính và Microsoft Excel. Anh ấy cung cấp đào tạo về bảng tính cho các tập đoàn và đã được Microsoft trao giải thưởng Excel MVP danh giá vì những đóng góp của anh ấy trong việc chia sẻ kiến ​​thức Excel của mình và giúp đỡ mọi người