IToverview.com - Easy To Learn Tutorials!

IToverview.com

Tutorials


How to convert a TEXT VALUE TO COLUMNS: Example

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
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
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



Previous  TEXT Functions in Excel 2013     |     Import data from a text file into excel 2013   Next