Author Archives: karen

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

Alternative to VLOOKUP: INDEX & MATCH

VLOOKUP and HLOOKUP take up a lot of memory and processing power, especially if you have a large data set. A great alternative to VLOOKUPs and HLOOKUPs are the INDEX & MATCH functions. Below is an example from Mr. Excel. The Problem One day, you have a situation where you have the employee name, but […]

Posted in Functions | 38 Responses

How to add or insert a line break or carriage return in a cell in Excel

Want to add insert line breaks and carriage returns from a cell in your Excel file? Let’s say, we have the following example: John Smith But now we need these two words to appear on two separate lines instead of a single line, just like this: John Smith There are two options to get the […]

Posted in Functions | Leave a comment

How to remove line breaks and carriage returns in cells in Excel

Want to remove line breaks and carriage returns from a cell in your Excel file without having to do it manually? The good news is, you can use the “find and replace” function on Excel to get this job done. Let’s say, we have the following example: In this case, the author has used the […]

Posted in Functions | 6 Responses

How to remove all hyperlinks in an Excel file

Looking to remove all the hyperlinks in an Excel file? Hit [ALT]+[F11] to open the Visual Basic Editor Go to “Insert” > “Module” and in the pop-up window copy: Sub RemoveHyperlinks() ‘Remove all hyperlinks from the active sheet ActiveSheet.Hyperlinks.Delete End Sub Then click “File” > Close and return to Microsoft Excel You can now run […]

Posted in Macros | 2 Responses

How to remove line breaks from Excel

Want to remove a line break in Excel? Follow the steps below! In Excel, choose Edit>Replace Click the “Find what” box Hold the Alt key, and (on the number keypad), type “0010”. This represents a line break in Excel. You won’t see anything in the “Find What” box In the “Replace with” box, type the characters that […]

Posted in Functions, General tips | Leave a comment

How to remove the last character in a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to remove the last character “s”: “I want to remove the last character of this cells” To remove the last character “s” from A1, use the following formula: =LEFT(A1,LEN(A1)-1) The result: I want to remove the last character of this […]

Posted in Functions | 6 Responses

How to capitalize the first letter in a cell in Excel

If you’d like to capitalize all letters in a cell, it’s easy – just use the following formula (assuming cell “A1” has the text you want to capitalize). =PROPER(A1) But what if you just want to capitalize the first word in the cell “A1”? For example, you have the following  text in a cell “A1” […]

Posted in Functions | 46 Responses

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