How to convert cell value to specific format in EXCEL?

M

mr jeery2017-10-09 15:58:06

Database

mr jeery, 2017-10-09 15:58:06

There is a huge column with a list of phones in the format 921139931, you need to change the format to +7 (921) 139-93-31
Please tell me how to do this? Through the mask, only the appearance changes.
I'm not strong in VBA, I'm afraid that I won't have time to figure it out.

Reply

Answer the question

In order to leave comments, you need to log in

1 answer(s)

X

x67, 2017-10-09
@jeerjmin

the formula takes the numbers from cell A1 and converts it to the desired form:

="+7("&ПСТР(A1;1;3)&")"&ПСТР(A1;4;3)&"-"&ПСТР(A1;7;2)&"-"&ПСТР(A1;9;2)
или
=СЦЕПИТЬ("+7(";ПСТР(A1;1;3);")";ПСТР(A1;4;3);"-";ПСТР(A1;7;2);"-";ПСТР(A1;9;2))