5. Remove spaces and non-printable characters from text
Sometimes text values contain embedded space characters at the beginning, end, or in multiple places.
These characters can often produce unexpected results new zealand mobile phone number list when sorting, filtering, or searching. For example, in the external data source, users may make typographical errors by inadvertently adding extra space characters or imported text data from external sources that may contain non-printing characters that are embedded in the text. Because these characters are not easily observed, it can be difficult to understand the unexpected results. To remove these unwanted characters, you can use a combination of the SPACES and SUBSTITUTE functions .
A) SPACES function
Removes spaces from text except for the normal space left between words. Use SPACES on text from other applications that may contain irregular spacing.
Syntax
SPACES (text)
The syntax of the SPACES function has the following arguments:
Required Text. This is the text from which you want to remove spaces.
B) Nuncio REPLACE
Replace original_text with new_text within a text string. Use SUBSTITUTE to replace specific text in a text string.
Syntax
REPLACE (text, original_text, new_text, [occurrence_number])
The SUBSTITUTE function syntax has the following arguments:
Required Text . This is the text or reference to a cell containing the text in which you want to replace characters.
Original_text Required . This is the text you want to replace.
New_text Required . This is the text you want to replace the original_text with.
Num_of_occurrence Optional. Specifies the instance of original_text that you want to replace with new_text. If you specify the num_of_occurrence argument, only that instance of original_text is replaced. Otherwise, all instances of original_text in text are replaced with new_text.
6. Correct date values
Because there are several different date formats, and because these formats can be confused with numbered item codes or other strings containing forward slashes or dashes, it is often necessary to convert and reformat date values.
Convert dates stored as text to dates
Sometimes dates can be formatted as text and stored as text in cells. For example, you may have typed a date into a cell that was formatted as text, or the data may have been imported or pasted from an external data source as text.
Dates formatted as text are aligned in a cell to the left (rather than the right). When error checking is enabled, text dates with two-digit years may also be flagged with an error indicator: .
Since Excel's error checking can detect text-formatted dates with two-digit years, you can use the automatic correction options to convert them to date-formatted dates. You can use the DATEVALUE function to convert most types of text dates to dates.
DATEVALUE function:
To convert a text date in a cell to a serial number, use the DATEVALUE function . Then, copy the formula, select the cells containing the text dates, and use Paste Special to format them as dates.
Follow these steps:
Select a blank cell and make sure the number format is General .
In the blank cell:
Write = DATEVALUE (
Click the cell that contains the text-formatted date you want to convert.
Enter )
Press enter and the DATEVALUE function returns the serial number of the date represented by the text date.
What is an Excel serial number?
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008, which is serial number 39,448 because it is 39,448 days after January 1, 1900. To copy the conversion formula to a range of contiguous cells, select the cell that contains the entered formula, and then drag the fill handle over a range of empty cells that matches the size of the range of cells that contains the text dates.
After dragging the fill handle, you should have a range of cells with serial numbers that corresponds to the range of cells containing the text dates.
Select the cell or range of cells that contains the serial numbers, and then right-click and select Copy .
Select the cell or range of cells that contains the text dates, right-click and select Paste Special .
In the Paste Special dialog box , under Paste , select Values , and then click OK .
On the Home tab , click the pop-up window selector next to Number .
In the Category box , click Date , and in the Type list , click the date format that you want.
To remove serial numbers after all dates have been successfully converted, select the cells that contain them, and then press the DELETE key .
