In the following example you will learn how to split the phone number into area code, central office code and four digit station number.
Telephone number in USA is a 9 digit number. The first 3 numbers represent area code followed by HYPHEN; next three numbers represent central office code followed by HYPHEN and the last 4 digits a four digit station number.
Convert TEXT Values into Columns
Splitting the text TELEPHONE NUMBER:
Add 3 columns adjacent to Phone Number (Column F) and Start Date (Column G).
Click column G. The entire column will be selected. Right click and from the menu Clicks insert; a new column is created. Click F4 (Pressing F4 repeats the previous task of creating the column) two times. Totally 3 columns are created.
Copy the Phone number (F column) column and paste it into G column.
Click column G and select the entire column. Click DATA tab. Click Text to Columns in DATA TOOLS group.
Convert Text to Column Wizard appears (step 1 of 3). Select delimited. Click next. In step 2 of 3, deselect tab check box and select other check box. Type hyphen in other check box. Click next. In step 3 of 3, click finish. You can see the data populated in those 3 columns G, H, and I.
How to transpose the values using Paste Special-Transpose?
Copy the values from B3-B8.
Click the cursor on B1.
Right click. Click Paste Special and select TRANSPOSE.
Click OK.
You can see the cells copied from B1 to G1.
Paste Special-Transpose
How to paste the value with formula using Paste Special - Values?
Add another column Incentive on c3.
Type the values from c4-c8 as shown in the figure.
Add another column total and add salary and incentive. When you place your cursor on the total values, you can see the formula. Note: we have copied the formula of D8 cell and pasted it on D9 cell (B8+C8)
Create another column Paste Special without formula.
Copy values from D3-D8. Place the cursor on F3.
Right Click. Choose Paste Special. Click Values. You will see all values copied without the formula.
How to general serial numbers automatically?
Add another column Serial Number on A3.
Type 1 in A4 and 2 in A5. Select A4 & A5.
Place the cursor on right bottom corner of A5. You will see + symbol. Drag it until A9.You can see values 1,2,3,4,5,6,7,8,9
TEXT Functions in Excel 2013