Converting Numbers in Excel Spreadsheet


Sometimes I need to convert Telephone numbers to a different format, and I use the following formula in my Excel Spreadsheet to get the result that I need.

Example:
I take an eight digit number such as 12345678 and I need to express it with an area code of 07 in front, and show the number in two 4 digit blocks, such as this end result: 07 1234 5678

In cell A1 I enter the original data 12345678
In cell B1 I enter the formula =LEFT(A1,4)
In cell C1 I enter the formula =RIGHT(A1,4)
In cell D1 I enter the formula =+CONCATENATE(“07 “,B1,” “,C1)

The result in D1 now shows 07 1234 5678

The procedure is that =LEFT(A1,4) gives the left 4 digits, and =RIGHT(A1,4) gives the right 4 digits.

Then, the use of the Concatenate feature  adds 07 to the front and puts a space between the two sets of 4 digits.


31.1 - 606,221
Scroll down for Comments
0 0 votes
Article Rating
Subscribe
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments