An assignment was received for removing phone numbers from a column of mail containing (You understand, sometimes they create something like 89999999999@mail.ru) with the subsequent transfer of phone data into a separate column. The problem is that everywhere they are written in kos and kos, and among other things it is required to bring them into the format + 7-999-999-99-99. What kind of operators do this?

  • Functions: Left (), Right (), Mid (), Instr (), Len (). Operators =, <>, Like. - Akina
  • That is, we create a condition and in the Like operator we write the variants of the values ​​we are looking for? - Arseny

2 answers 2

As an option - pull the substring up to @ , check that there are only numbers and write them in the correct format:

 Sub test() Dim rows As Integer, i As Integer, email As String, phone As String r = ActiveSheet.UsedRange.rows.Count ' число строк ' For i = 1 To r email = ActiveSheet.Cells(i, 1).Value phone = Left(email, InStr(email, "@") - 1) ' берем номер из эл. почты ' If IsNumeric(phone) Then ' чтобы были только цифры ' If Len(phone) = 10 Then ' если кол-во цифр 10 - вероятно, номер без 8 или 7 ' ActiveSheet.Cells(i, 2).Value = "+7-" & Left(phone, 3) & "-" & Mid(phone, 4, 3) & "-" & Mid(phone, 7, 2) & "-" & Mid(phone, 9, 2) ElseIf Len(phone) = 11 Then ActiveSheet.Cells(i, 2).Value = "+7-" & Mid(phone, 2, 3) & "-" & Mid(phone, 5, 3) & "-" & Mid(phone, 8, 2) & "-" & Mid(phone, 10, 2) End If End If Next i End Sub

Output to test data:

enter image description here

  • phone = Left(email, InStr(email, "@") - 1) In my opinion, phone = Split(email, "@")(0) will be more colored ... and at the same time will not fail if there is no character in the cell @ . - Akina
  • THANK YOU THANKS - Arseny
  • @Arseny, if the answer was right for you, please tick the box next to it. - Denis
  • @Denis Polzhitelny answer, but the data types on the variables had to be replaced by Long due to going beyond the limits of integer - Arseny
  • @ Arseny you probably have a lot of lines, in such cases, yes - Long is needed. - Denis

Option. Working with arrays can significantly speed up data processing.

If you set the cell format of the second column +0 \ (000) 000-00-00 , then this will allow you not to convert the number into text and not divide it into fragments.

 Sub test2() Dim a() Dim lRw As Long, lTel As Long Dim i As Long With ActiveSheet ' лист, где нужно обработать данные' lRw = .Cells(.rows.Count, "A").End(xlUp).Row ' последняя видимая заполненная строка' If lRw < 2 Then Exit Sub ' нет данных (строка 1 - шапка)' a = .Range("A1:A" & lRw).Value ' данные в массив' ReDim Preserve a(1 To lRw, 1 To 2) ' добавляем поле для т/ф' For i = 2 To lRw If a(i, 1) <> Empty Then ' значение есть' lTel = Val(Right(Split(a(i, 1), "@")(0), 10)) ' извлекаем часть строки' If lTel > 1000000000 Then a(i, 2) = "7" & lTel ' номер есть' End If Next i Application.ScreenUpdating = False .Range("A1:B" & lRw).Value = a ' выгрузка на лист Application.ScreenUpdating = True End With End Sub
  • A good option, but not quite suitable for the task, because The array is unrealistically huge, and there may be different .... hm. At the moment, I am finalizing 1 first script, appending variations, and it showed itself quite well. - Arseny
  • I finalized the script and made a link to the second one, which in theory should edit the values ​​that are in the column and which 1 script does not touch. NO. It does not work and you get an error Application- Defined or object-defined error. Please tell me what is wrong? (I can't add the code) - Arseny
  • ">> the array is unrealistically huge, and there can be different things === the more you need to handle memory. And the fact that" different ": to exclude / circumvent / fixes, you need to see examples of these" differences "to make up an algorithm for working with By such data. There is no universal code for all occasions. - vikttur