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.
Recent Comments