Join Dennis Taylor for an in-depth discussion in this video Getting rid of leading single quotes, part of Cleaning Up Your Excel 2010 Data.
Downloaded or copied data might contain leading single quotes.…And although generally not a problem, you do want to get rid of them.…There are a couple techniques for doing this.…We don't see them so obviously either. In Column B there are some, and as I…click on Cell B3, you can see in the formula bar there's a single quote.…If you happen to double-click in the cell, you'll also see it there too.…We don't have these on all the cells, but we don't know how many, and we are…certainly not going to go to every single cell to check it out.…We need to get rid of them though, for the sake of consistency, and potentially…some other reasons as well.…
One way to approach this is simply to use a function called Substitute.…I'll make the column a little bit wider here, so we can see this better.…We simply want to substitute for that single quote if it exists, nothing.…The substitute function allows us to do this, regardless of where it's found,…but in this case it will only be in the first position.…
- Moving or inserting rows and columns of data with a simple drag
- Using Text to Columns
- Harnessing the Find and Replace command to replace data at the character level
- Dealing with special characters and wildcards during search
- Converting dates with text functions
- Converting text data to values/numbers
- Checking and correcting spelling mistakes
- Splitting data into multiple columns via the Text to Columns feature
- Combining data from different columns via concatenation
Skill Level Appropriate for all
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.