In this article, I will tell you how to find number values formatted as text in excel cells, and how to convert the text formatted number to number format.
1. How To Find Number Values Formatted As Text In Excel.
- To find number values formatted as text in Excel, follow these steps.
- Select the cells that may contain number values formatted as text.
- On the Home tab, click on the Find & Select button ( in the Editing group ) and select “Find” in the drop-down menu list.
- Then it will open the Find and Replace dialog box.
- In the Find and Replace dialog box, click on the “Options” button to expand the dialog box.
- Click the “Format…” drop-down list, then click the “Format…” item in the drop-down list.
- It will open the Find Format dialog box.
- Click the Number tab in the Find Format dialog box.
- Select “Text” in the Category list pane.
- Click the OK button to save the settings, then it will return to the Find and Replace dialog box.
- Click on “Find All” to display a list of cells that contain text-formatted numbers.
2. How To Convert The Text Formatted Number To Number Format.
- Now you can press the Ctrl key and click to select the text formatted cells in the Find and Replace dialog box result list like above picture.
- Then Press “Esc” to close the Find and Replace dialog box.
- And you can find that your selected text formatted cells are still selected.
- Right click any one of the selected cells, then click the Format Cells… menu item in the popup menu list.
- It will open the Format Cells dialog window.
- Select the item Number in the Number tab Category pane.
- You can see the number value sample on the dialog window right side.
- You can also adjust the Decimal places on the window right side.
- If you want to use 1000 separator, you can check the checkbox before the Use 1000 Separator.
- Click the OK button, then all the selected text format number cells will be formatted as number format.
