SELECT * - FROM - ORDER BY RowNo, PartNo SELECT DISTINCT t2. SELECT RowNo, PartNo, RTRIM(LTRIM(MyWord)) AS MyWord WHERE CHARINDEX( ' ', LTRIM(Remainder))=0 UNION ALL SELECT RowNo, PartNo + 1 AS PartNo, LTRIM(Remainder) AS MyWord, NULL AS Remainder WHERE CHARINDEX( ' ', LTRIM(Remainder))>0 UNION ALL SELECT RowNo, PartNo + 1 AS PartNo, LEFT(LTRIM(Remainder), CHARINDEX( ' ', LTRIM(Remainder))-1) AS MyWord, RIGHT(LTRIM(Remainder), LEN(LTRIM(Remainder)) - CHARINDEX( ' ', LTRIM(Remainder))) AS Remainder SELECT ROW_NUMBER() OVER( ORDER BY MyText) AS RowNo, 1 AS PartNo, LEFT(LTRIM(MyText), CHARINDEX( ' ', LTRIM(MyText))-1) AS MyWord, RIGHT(LTRIM(MyText), LEN(LTRIM(MyText)) - CHARINDEX( ' ', LTRIM(MyText))) AS RemainderįROM WHERE CHARINDEX( ' ', LTRIM(MyText))>0 Place your cursor in the Find what text box and press the space bar twice to add two spaces.
Ensure you are in the Replace tab in the Find and Replace dialog box. ( ' This is another very strange text.'),ĭECLARE TABLE(RowNo INT, PartNo INT, MyWord VARCHAR( 255)) A Word for Caution: You may be tempted to increase the space between words by adding multiple spaces.I strongly advise against this practice because adding unnecessary spaces (rather than making individual spaces bigger, as shown in this tutorial) can disturb other formatting in your document. Delete Extra Spaces Using Word’s Find and Replace Tool.
Don't forget to add a TRIM function to remove the extra spaces.Copy Code DECLARE TABLE(MyText VARCHAR( 500)) If not, it could be that someone has inadvertently pressed Ctrl+Shift+J instead of Ctrl+J. You can use the SUBSTITUTE function to substitute non-breaking spaces, CHAR(160) with normal spaces, CHAR(32). If you display nonprinting characters (press Ctrl+ or click the Show/Hide button in the Paragraph group on the Home tab), do you see a dot between each pair of letters If so, then there is an actual space there. Remove the spaces between two words : - The content of the file is : The quick brown fox jumps over the lazy dog After removing the spaces the content is. If your text contains non-breaking spaces, CHAR(160), the TRIM function doesn't work.ġ1. Next, use SUBSTITUTE and CHAR to return the text string without this character.ġ0. First, find out the code number of the character.ĩ. Simply use the SUBSTITUTE function to remove other ASCII characters.Ĩ. The CLEAN function doesn't remove all ASCII characters. You can also use the SUBSTITUTE function to remove a line break, CHAR(10), and replace it with something else. To enter a line break, press ALT + ENTER.ħ. Hit CTRL+A to select all the text in the document. Simply combine CLEAN and TRIM to remove non-printable characters and spaces.Ħ. If you get a Word document with double spaces, you can quickly strip out the extra spaces to meet modern standards by following these steps. The CLEAN function below removes a non-printable character, CHAR(7).ĥ. The CLEAN function removes the first 32 non-printable ASCII characters (codes 0 through 31).Ĥ. Text imported from other applications may contain ASCII characters. Note: the SUBSTITUTE function substitutes spaces (second argument) with empty strings (third argument). You can use the SUBSTITUTE function to remove all spaces. Press the Space bar twice in the Find What field and once in Replace With Click on the ' Replace all '. Press Ctrl + H to get the ' Find and Replace ' dialog box. \s matches a space, tab, new line, carriage return, form feed or vertical tab. Using regular expression, to replace 2 or more white spaces with single space, is also a good solution. To get the length of a string, use the LEN function.Įxplanation: the LEN function counts 2 spaces, 2 characters, 4 spaces, 3 characters and 2 spaces.ģ. Using Find & Replace to remove extra spaces between words Select one or several columns with the data to delete spaces between words. Remove extra white spaces between words with regular expression. Note: the TRIM function does not remove single spaces between words.Ģ.