Category Archives: Extracting words

Excel formula to change foreign accented characters to regular non-accented characters

Need to change foreign accented characters to regular non-accented characters? I just encountered this problem today. I have a list of hundreds of thousands of city names from all over the world, and I needed to generate a list of these names without the foreign accented characters. Here’s how to get this done in just two seconds. […]

Posted in Extracting words | Leave a comment

How to Extract Text Before Second or Third Instance of a Symbol

For example, I have the following list in cell A1: “Coffee, cream, sugar, tea” I am trying to copy all the text before the second or third instance of the comma into an adjacent cell. To extract the text before the second instance of the comma, use the ff. formula: =LEFT(A1,FIND(“^^”,SUBSTITUTE(A1,”,”,”^^”,2))-1) where the 2 represents […]

Posted in Extracting words | Leave a comment

How to delete or remove the first two words in a cell

For example, you have the following  text in a cell “A1” and you’d like to remove the first two words: “English courses USA” To remove the first two words “English courses” from A1, use the following formula: =MID(A1,1+FIND(“~”,SUBSTITUTE(A1,” “,”~”,2)),255) The result: USA Not working for you? Remember to replace A1 with the cell that contains […]

Posted in Extracting words | 1 Response

How to extract words before and after a comma

Today, I encountered a situation where I had to separate the full names of our customers into the first and last names. The names are comma separated, so I was looking for an Excel formula that can help me extract the last name before the comma and the first name after the comma. For example, […]

Posted in Extracting words | 34 Responses

How to remove the first character of a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to remove the first character “: “I want to remove the first character of this cell To remove the first character ” from A1, use one of the following formula: =MID(A1,2,LEN(A1)) or =REPLACE(A1,1,1,””) or =RIGHT(A1,LEN(A1)-1) The result: I want to […]

Posted in Extracting words | Leave a comment

How to extract Nth word from a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to extract the 4th word “extract”: I want to extract the nth word in this cell To extract the 4th word “extract” from A1, use the following formula: =MID(MID(MID(SUBSTITUTE(A6,” “,”^”,3),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,3)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,3),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,3)),256))-2) The result: extract This last one […]

Posted in Extracting words | Leave a comment

How to extract last three words in a cell in Excel

For example, you have the following the text in a cell “A1″: I want to extract the last three word in this cell To extract the last three words from A1, assuming all words have 20 characters or less, use the following formula: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,60)),180)) The result: in this cell Not working for you? […]

Posted in Extracting words | Leave a comment

How to extract last two words in a cell in Excel

For example, you have the following the text in a cell “A1”: I want to extract the last two word in this cell To extract the last two words in cell “A1″, use the following formula: =MID(A1,FIND(“@”,SUBSTITUTE(A1,” “,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-1))+1,255) The result: this cell Not working for you? Remember to replace A1 with the cell that […]

Posted in Extracting words | Leave a comment

How to remove the last word in a cell in Excel

For example, you have the following the text in a cell “A1”: I want to remove the last word in a cell in this cell To remove the last word in cell “A1”, use one of the following formula: =LEFT(TRIM(A1),FIND(“~”,SUBSTITUTE(A1,” “,”~”,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))))-1) or =IF(ISNUMBER(SEARCH(” “,TRIM(A1))),TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,99)),99)),””)),A1) The result: I want to remove the last […]

Posted in Extracting words | 8 Responses

How to extract the last word in a cell in Excel

For example, you have the following the text in a cell “A1”: Can you help me extract the last word in this cell in Excel? To extract the last word “Excel”, use one of the following formula: =RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))) or =MID(SUBSTITUTE(A1,” “,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))),FIND(“^”,SUBSTITUTE(A1,” “,”^”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))+1,256) The result: Excel

Posted in Extracting words | 11 Responses