Excel - 搜索和替换各种字符串 [英] Excel - search and replace various strings

问题描述

我有一个通过 SQL 查询从 Access 数据库导出的表.在excel中，有3列:ID，姓名和电话号码......有问题的是电话号码列.用户以各种方式输入数字，因此看起来很不整洁，我无法将该字段用于其他目的.

I have a table exported from Access database via SQL query. In excel, there are 3 columns: ID, name and a phone number... The problematic is a phone number column. Users have put in numbers in all kinds of ways so it looks very untidy and i cant use that field for other purposes.

假设我想查看数字的默认方式是:385991234567.所以它有国家/地区代码，然后是区号，然后是数字……但全部组合在一起.

Lets say that the default way that i want to see the numbers is: 385991234567. So it has the country code, than the area code, then the number... but all together combined.

在数据库中我有如下输入:

In the database i have inputs like:

0991234567 - 所以没有国家代码

0991234567 - so without the country code

991234567 - 没有国家代码和区号的前导零

991234567 - without the country code and the leading zero of the area code

[phone] - 在国家/地区代码前加 +

[phone] - with + in front of the country code

99/1234-567 - 用各种字符分隔数字组(我已经设法通过 sql 查询中的替换来清理它).

99/1234-567 - with various character separating the groups of numbers (i've managed to clean that up through the replace in the sql query).

我想要的是实现我在开头提到的格式.所以添加国家代码，如果需要，删除前导零.额外的问题是一些数字是 7 位数字，其他数字是 6 位数字(没有国家或地区代码).我曾经尝试通过搜索 099 来进行替换，但如果它出现在数字的中间，它也会被更改.

The thing i want is to achieve the format i mentioned in the beginning. so add the country code, if needed remove the leading zero. The additional problem is that some of the numbers are 7 digits, others are 6 digits (without the country or area codes). Ive tried to do the replace by searching the 099 for instance, but if it occurs in the middle of the number, it gets changed too.

我不知道该怎么办.或者甚至这是这个问题的正确部分(因为我不知道在 excel 中，通过 VBA 宏，通过 sql 查询或其他方式是否更容易).我不能手工做，因为它是一个大约的领域.100.000 个电话号码.

I dont know what to do. Or even is this the right section for this question (because i dont know if it is easier to do it in excel, via VBA macro, via sql query or something else). I cant do it by hand because its a field of approx. 100.000 phone numbers.

推荐答案

好的，这里是... 首先，我使用了一些 SQL 查询来选择特定的电话号码并清除电话号码中的垃圾字符...

OK, so here it is... for starters, i used some SQL Queries to select the specific phone numbers and clean up the junk characters from phone numbers...

select sk, name1, phone into telefoni from 
(
SELECT customer_id as sk, name as NAME1, phone1 as phone from customers where len    (phone1)>7 and (phone1 like "09*" or phone1 like "3859*" or phone1 like "*3859*" or phone1 like "95*" or phone1 like "98*" or phone1 like "99*" or phone1 like "91*" or phone1 like "92*" or phone1 like "97*")

还有这个...

SELECT distinct sk, name1, replace(replace(replace(replace(replace(phone, "/", ""), "-", ""), " ", ""), ".", ""), "*", "") as tel into telefoni2 from telefoni1 order by name1;

之后，我将数据导出到 excel 并应用下一个宏...

After that, i exported the data to excel and applied the next macro...

Sub test()
For Each cell In Selection
    If cell.Value Like "091*" Then cell.Value = Replace(cell.Value, "091", "38591", 1, 1, vbTextCompare)
    Next cell
End Sub

就是这样......就像一个魅力.代码可能很乱，或者可以写得更好，但我是新手，所以我想还可以:D

And thats about it... worked like a charm. The code may be messy or could be written better, but i am a novice, so i guess its ok :D

这篇关于Excel - 搜索和替换各种字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！