How to change the value of the "Phone" column in Excel from "244-44-44 is Katya's number" to "244-44-44"?

Question

excel

Greetings!
We introduced customer records in Excel once. There was no single standard, as well as control over the entered data. Employees entered anything in the phone number column, from any characters, numbers, and text.
It turned out like this:
244-33-33 multi-channel secretary Katya, 256-65-56
[phone], - the indicated phones are CHP 2 bodies
Now these files need to be uploaded to the Bitrix CRM system. However, when you download, everything is displayed in the system with text. There are more than 1000 contacts, so it takes a very very long time to do it manually.
Tried in excel:
Format cells - all formats, substituted formulas - the result is no change
Please tell me how you can "turn" in Excel the values ​​​​in the column from
"244-33-33 multi-channel secretary Katya, 256-65-56"
to
244-33-33
256-65-56

Answer 1

Replacing
the "Old but not obsolete" trick. Select the range of cells to be cleaned and use the Replace tool from the Home tab – Find and Select (Home – Find & Select – Replace) or press the keyboard shortcut Ctrl + H.
Initially, this window was conceived for the wholesale replacement of one text with another according to the principle "find Masha - replace with Petya", but we, in this case, can also use it to remove excess text. For example, in the first line enter "g." (without quotes!), and in the second do not enter anything and click the button Replace All (Replace All). Excel will remove all "y" characters. before city names:
clean-text1.png
Just don't forget to select the desired range of cells first, otherwise the replacement will occur on the entire sheet!
Removing spaces
If you need to remove all spaces from the text at all (for example, they stand as thousandth separators inside large numbers), then you can use the same replacement: press Ctrl + H, enter a space in the first line, enter nothing in the second line and click the Replace All (Replace All).
However, a situation often arises when it is not necessary to remove all the spaces in a row, but only the extra ones - otherwise all the words will stick together with each other. In the arsenal of Excel there is a special function for this - TRIM (TRIM) from the Text category. It removes all spaces from the text, except for single spaces between words, i.e. we get exactly what we need as an output:
clean-text2.png
Removing non-printing characters
In some cases, however, the TRIM function may not help. Sometimes what looks like a space is not actually a space, but an invisible special character (non-breaking space, line break, tab, etc.). Such characters have an internal character code different from the space code (32), so the TRIM function cannot "stripped" them.
There are two solutions:
Carefully select these special characters in the text with the mouse, copy them (Ctrl + C) and paste (Ctrl + V) into the first line in the replacement window (Ctrl + H). Then click the Replace All button to remove it.
Use the CLEAN function. This function works similarly to the TRIM function, but removes non-printable characters from the text instead of spaces. Unfortunately, it is also not able to cope with all special characters, but most of them can be removed with its help.
SUBSTITUTE Function
Replacing some characters with others can be implemented using formulas. To do this, in the Text category in Excel there is a SUBSTITUTE function. It has three mandatory arguments:
Text in which we are replacing
Old text - the one we are replacing
New text - the one we are replacing with
With its help, you can easily get rid of errors (replacing "a" with "o"), extra spaces (replacing them with an empty string ""), remove extra separators from numbers (do not forget to multiply the result by 1 later so that the text becomes a number) :
clean-text3.png
Remove apostrophes at the beginning of cells
The apostrophe (') at the beginning of a cell in a Microsoft Excel worksheet is a special character officially known as a text prefix. It is needed in order to make it clear to Excel that all subsequent contents of the cell should be treated as text, and not as a number. In fact, it serves as a convenient alternative to pre-setting the text format for a cell (Home - Number - Text) and for entering long sequences of numbers (bank account numbers, credit cards, inventory numbers, etc.) it is simply irreplaceable. But sometimes it ends up in cells against our will (after unloading from corporate databases, for example) and begins to interfere with calculations. To remove it, you will have to use a small macro. Open the Visual Basic editor with Alt+F11, insert a new module (menu Insert - Module) and enter its text there:
?
1
2
3
4
5
6
7
8
9
Sub Apostrophe_Remove()
For Each cell In Selection
If Not cell.HasFormula Then
v = cell.Value
cell.Clear cell.Formula
= v
End If
Next
End Sub
run our macro (Alt + F8 or the Developer tab - the Macros button), then the apostrophes in front of the contents of the selected cells will disappear.
English letters instead of Russian
This is, as a rule, a purely human factor. When entering text data into a cell, instead of Russian letters, similar English letters are accidentally entered ("tse" instead of Russian "es", "y" instead of Russian "y", etc.) And everything is decent from the outside, because the style of these characters is sometimes exactly the same , but Excel perceives them, of course, as different values ​​and produces errors in formulas, duplicates in filters, etc.
You can, of course, manually replace Latin characters with their corresponding Cyrillic alphabet, but it will be much faster to do this using a macro. Open the Visual Basic editor with Alt+F11, insert a new module (menu Insert - Module) and enter its text there:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Replace_Latin_to_Russian()
Rus = "
acekopxyACEHKMOPTX"
For Each cell In Selection
For i = 1 To Len(cell)
c1 = Mid(cell, i, 1)
If c1 Like "[" & Eng & "]" Then
c2 = Mid(Rus, InStr(1, Eng, c1), 1)
cell.Value = Replace(cell, c1, c2)
End If
Next i
Next cell
End Sub
Now, if we select a range on the sheet and run our macro (Alt + F8 or Developer tab - Macros button), then all English letters found in the selected cells will be replaced with equivalent Russian ones. Just be careful not to accidentally replace the Latin you need :)
www.planetaexcel.ru/techniques/7/191

Answer 2

I don’t know how to work with regular expressions in Excel,
but you can convert an Excel file to CSV format or to a database that supports regular expressions
and programmatically extract everything that looks like a phone number . The second step is to unify the numbers to one type.
Regular expression ((\+?[\s]?\d+[\-\s]{,2})?(\(\d+\)\s*?)?(\d[\-\s\/] ?){4,}\d)
tested on the following phone numbers:
0504720963
095-713-21-45
095 449 73 66/0
244-33-33
(8555) 566 06 06
+ 38 [phone]-99990
[phone] 444
[phone]
[phone] 333
[phone]-444