This step describes how to contain the text of Excel cells into cells that contain numbers.
Overview:
When the import in another program (such as dBASE or Lotus 1-2-3) files created or imported from the mainframe to download the file, Excel 2003 some of these figures may be recognized as text.
This will cause some functions (such as SUM and AVERAGE) ignores the value of these cells. In addition to digital conversion, these text strings may contain actual text characters. So how to convert text to numbers?
Work table to convert text to numbers, you can use any of this article described methods (methods 1 to 7).
Note that each method assumes that you have a cell in any cell number format changed to "regular." To do this, follow these steps:
1. In the "Format" menu, click "cell."
2. In the "number" tab, click the "classification" under the "General", then click "OK."
Method 1: Using the wrong button
If the digital display as text in the top left corner of the cell has an error indicator, please follow these steps.
Note that this method assumes that Excel in the background error checking feature is turned on. If you do not turn on error checking feature, you must open it to use this method.
1. In the "Tools" menu, click "Options."
2. Click "Error Checking" tab.
3. Click to select the "Enable background error checking" check box.
4. Click to select the "Digital Storage in text format" check box.
5. Click to select the other rules you want to open.
6. Click "OK."
7. Click the cell that contains the error indicator.
8. Click the cell next to the wrong button, then click "convert to digital."
Method 2: re-type the value in cell
To re-type the value of a cell, follow these steps:
1. In the "Format" menu, click the "cell", then click the "number" tab, change the number format of cells.
2. To re-type figure.
Method 3: directly edit the cell
To edit directly in cell, follow these steps:
1. In the "Tools" menu, click "Options."
2. In the "Edit" tab, verify that the "Edit directly in cell" check box.
Click "OK."
3. Double-click the cell you want to format, then press Enter.
Method 4: Use the "Paste Special" command
To use the Paste Special command, follow these steps:
1. In any empty cell, type the value 1.
2. Choose your type 1 in which the cell, and then click the "Edit" menu "Copy."
3. Select the value to which the cell is converted to digital.
4. In the "Edit" menu, click "Paste."
5. In the "Actions", click "ride."
In the "Paste", click "value", then click "OK."
6. Remove your blank cell, type in the value of 1.
Note that some of the accounting program will display a negative value a negative sign (-) on the value of the right. To convert the text string value, you must return the text string of all characters (most characters, except the right negative sign), then multiply the result by -1. For example, if the value of cell A2 in the 156 -, the following formula can be converted to the value of -156 text:
= LEFT (A2, LEN (A2) -1) *- 1
Method 5: Remove Hidden characters and spaces
If the data is arranged in a single column or row, this method is best. It uses the TRIM and CLEAN function to remove extra spaces with file import and non-printing characters. The following example assumes that the data in column A, the first in row 1 ($ A $ 1). To remove the hidden characters and spaces, follow these steps:
1. A column on the right into one.
To do this, click the "B", then "Insert" menu, click "column."
2. Inserted in the first column cell (B1), type the following:
$ B $ 1: = VALUE (TRIM (CLEAN (A1)))
3. In the B column, select the A column of data containing the cell to the right of all cells.
4. In the "Edit" menu, point to "fill", then click "down."
A new column contains the value of the text in column.
5. Selected the same area, click the "Edit" menu "Copy."
6. Click the cell A1, then "Edit" menu click "Paste."
In the "Paste", click "value", then click "OK" to paste the converted values back to the top of the A column.
7. Remove the B column.
To do that, click the column, then click the "Edit" menu "delete."
A column of text is now in digital format.
Note that this method may not remove all non-printing white space characters. For example, the blank character Chr $ (160) does not remove.
Method 6: Using Microsoft Visual Basic for Applications (VBA) procedure
Create a VBA macro to the selected cell or selected range of cells re-enter numbers. Concrete steps are as follows:
1. Select the cells you want to convert.
2. If you do not change the number format of cells, in the "Format" menu, click the "cell", then click the "digital" tab to change the number format of cells.
3. Press Alt + F11 key combination to start the Visual Basic Editor.
4. In the "Insert" menu, click "module."
5. The following macro code to type a new module:
Sub Enter_Values ()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub
6. Press Alt + F11 to switch to Excel.
7. If you have not selected the cells you want to convert, it will select it.
8. In the "Tools" menu, point to "Macro", then click the "macro."
In the "Macro name" list, click "Enter_Values". Click "Run."
Method 7: Use the "Text to Columns" command
If the data is arranged in a single column, this method is best. The following example assumes that the data in column A, the first in row 1 ($ A $ 1). To use this example, follow these steps:
1. Select contains the text of a cell.
2. In the "Data" menu, click the "breakdown."
3. In the "original data type", click "separator", then click "Next."
4. In the "Delimiters", click to select the "Tab key" check box, then click "Next."
5. In the "Column data format", click "General."
6. Click the "Advanced", set the corresponding "10 delimiter" and the "thousands separator." Click "OK."
7. Click "Finish."
Now that is converted to digital text.