Очистить номер телефона в Excel

Question

Я хочу очистить данные моего телефонного номера. Формула

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,"(",""),")",""),"-","")," ",""),".","")+0

делает часть работы. Но я также хочу включить оператор IF() поэтому, если длина меньше 11 и в начале нет 0, добавьте начальный 0. Номера могут быть стационарными или мобильными, но я думаю, что основной проблемой будет отсутствие 0 и пробелов. Смотрите образец данных ниже:

7523698745
7896541236
1235552207
07841 256321
01235552204
7845 213698
07845632158
01235552204

Благодарю.

Answer 1

Из вашего вопроса выясняется, что вы пытаетесь набрать номер телефона с разными специальными символами и вытянуть целые числа из номера телефона. Например, число (123) 456-7890 должно возвращать 01234567890 .

Попробуйте описанную ниже пользовательскую функцию (объяснение UDF)

Function PARSEPHONENO(number As String) 'Number is the selected cell

Application.Volatile 'The formula will recalculate itself
Dim answer As String 'The value returned from the function
Dim i As Integer 'Used to loop through each character of the selected 'number' String

     For i = 1 To Len(number) 'For each character of the cell from 1 to the lenght of the string
           If IsNumeric(Mid(number, i, 1)) Then answer = answer & Mid(number, i, 1) 'If the value if numberic then add it to the answer
     Next i 'Next Character

     If Len(answer) < 11 And Left(answer, 1) <> "0" Then answer = "0" & answer 'If the answer is less than 11 digits, add a zero to the beginning of the cell

PARSEPHONENO = answer 'Return the answer
End Function

Примечание. Формула вернет строковое значение числовых цифр в начальный 0, который будет отображаться в ячейке.

Надеюсь это поможет!

Answer 2

С вашим текущим выходом в столбце A, в B1 введите:

=IF(AND(LEN(A1)<11,LEFT(A1,1)<>"0"),"0" & A1,A1)

и скопировать вниз:

Answer 3

Ваша текущая формула заставляет результат быть числом, добавляя 0 . Поскольку в результате получается число, Excel удалит все начальные нули, поэтому проверять его на единицу не имеет смысла. Это показано в столбцах B:E таблицы ниже.

Столбец A - это ваши данные выборки, B - результат вашей формулы, D - результат вашей формулы без +0 в конце. Столбцы C, E, G и I показывают первый символ ячейки слева от них, используя =LEFT(cell,1) .

Есть несколько приемов, которые вы можете использовать, чтобы Excel отображал начальные нули. Столбец F такой же, как и столбец B, за исключением пользовательского формата "0"# . Это отображает начальные нули, даже если их на самом деле нет, как показано в столбце G.

Колонка H использует эту формулу

=IF(LEN(B2)=10,0&B2,B2)

который дает текстовый результат, но имеет реальные ведущие нули.

То, как вы решите эту проблему, зависит от того, нужно ли хранить данные в виде чисел или они могут быть текстовыми.