Drag the handle down until you fill the cell that appears in the row that contains the last of the values with trailing spaces.
HOW TO REMOVE SPACES BETWEEN WORDS IN EXCEL PLUS
Your cursor turns into the autofill handle, which looks like a black plus sign. Click on the cell that contains the formula and place your cursor over the lower right corner of the cell boundary. The last part of the formula "!" provides the replacement text.Īpply your formula to the rest of the values in your column of cells without retyping it over and over again. Specify the replacement text, either as a cell reference or as a piece of text surrounded by double quotation marks. This portion of the formula "LEN(A1)-LEN(TRIM(A1))" identifies how many characters must be replaced. The portion of the formula "(LEN(TRIM(A1))+1)" finds the starting position.Ĭalculate the "number_of_characters" by measuring the LENgth in characters of the text in cell A1 and subtracting the TRIMmed LENgth from it to determine the number of trailing spaces. This function deletes all spaces except for single spaces between words. You can use the SUBSTITUTE function to remove all spaces. In Excel, to remove the leading, trailing, and extra spaces between words in a particular string, use the TRIM function. Explanation: the LEN function counts 2 spaces, 2 characters, 4 spaces, 3 characters and 2 spaces.
To get the length of a string, use the LEN function. Adding 1 to the TRIMmed LENgth locates the first space, which falls one character after the length of the TRIMmed text. Note: the TRIM function does not remove single spaces between words. "Replacement_text" specifies the substitute text.ĭetermine the "starting_position" - the character position at which the trailing spaces begin - by using the TRIM function to measure the length of the text in cell A1 without the trailing spaces. "Number_of_characters" indicates how many characters must be replaced. "Starting_position" identifies the numeric position of the first character you want to replace. In this case, your text appears in cell A1. "Original_text" consists of text content or a reference to the cell location where the text exists. REPLACE(original_text,starting_position,number_of_characters,replacement_text) Build this formula by understanding its parts and the requirements of the REPLACE function, which actually turns your spaces into an exclamation point.
0 kommentar(er)
