Cleaning Up Your Excel 2010 Data
Illustration by John Hersey

Dealing with special characters and using wildcards


From:

Cleaning Up Your Excel 2010 Data

with Dennis Taylor

Start your free trial now, and begin learning software, business and creative skills—anytime, anywhere—with video instruction from recognized industry experts.

Start Your Free Trial Now

Video: Dealing with special characters and using wildcards

In addition to standard kinds of replacement techniques available with Find and Replace, you may need to have to deal with special characters, like the asterisks that we see in Column D. And there is also another function, one called Substitute, that in many ways solves problems that Find and Replace can't handle. Let's deal, first of all, with the issue in Column D. Who knows how those asterisks got in there? We simply want to get rid of them, or possibly substitute some other character for them. Let's click Column D. And using Find & Select, the rightmost button on the Home tab in the Ribbon, let's go to Replace, and we would like to replace that asterisk-- that's all we want to replace.
please wait ...
Watch the Online Video Course Cleaning Up Your Excel 2010 Data
1h 26m Appropriate for all Nov 01, 2011

Viewers: in countries Watching now:

In this course, Dennis Taylor explores the functions, commands, and techniques in Excel that restructure data, remove unwanted characters, convert date data into the desired format, and prepare data for efficient analysis. This course helps get data from a business management system file, other database software, a text file, or a poorly designed Excel worksheet into optimal shape for working with in Excel.

Topics include:
  • 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
Subjects:
Business IT
Software:
Excel
Author:
Dennis Taylor

Dealing with special characters and using wildcards

In addition to standard kinds of replacement techniques available with Find and Replace, you may need to have to deal with special characters, like the asterisks that we see in Column D. And there is also another function, one called Substitute, that in many ways solves problems that Find and Replace can't handle. Let's deal, first of all, with the issue in Column D. Who knows how those asterisks got in there? We simply want to get rid of them, or possibly substitute some other character for them. Let's click Column D. And using Find & Select, the rightmost button on the Home tab in the Ribbon, let's go to Replace, and we would like to replace that asterisk-- that's all we want to replace.

We don't want to match the entire cell; we simply want to replace the asterisk with, how about nothing? So this is empty. Replace All. 742 replacements, but look at Column D. It's all gone. So how do we deal with that? What we've got to do for now is click OK, close, and undo. I'll press Ctrl+Z here. The asterisk is a special wildcard character. Perhaps you've encountered it in the other uses of Excel, perhaps in the filter. Let's go back to Find & Select.

We need to somehow refer to that asterisk in a special way. We'll do a replace. What we need to do here, and it's an unlikely character to use, is the tilde character. This is that squiggly little character that appears above the letter N in certain Spanish words. On the keyboard, usually this is found below the Escape key, to the left of the number 1 key, and above the Tab key on the upper left-hand corner. So if we embed the asterisk within tildes, two of them there, we want to replace all the asterisks in here with nothing, so we'll leave the Replace with panel empty.

Let's do a Replace All. There we go, and you see that's happened on the background. Turns out there are only four of them, but we did get rid of them. So that's an unusual construction. You might need that at different times when you're trying to clean up data. Now sometimes a coding-structure change is needed. We saw one example in a previous movie about how to make the third character become an X, using a function called Replace. Now we've got a different situation, maybe working off the same data. We want to replace the 2 that's in here, but only the first 2, and it might occur in the third position, as it does in a lot of these, or it might occur elsewhere.

It might not occur there at all. Let's replace the first occurrence of 2 with a certain character. Again, we're not just saying that to make it up. Based on the concept, we really need to get rid of that first 2. The function is called Substitute. We're looking at cell B2, comma. The text we're looking for is 2, comma. And what we want to replace it with is, for example, the letter Q. Now if it's a letter, we need to embed that within double quotes.

And then you'll see, after this in the prompt, instance number, comma, 1. If we leave off that argument, we will replace all the 2s with Q. So, you see what happened. The third entry there, that's the first 2 that we encountered, becomes a Q, but not the fourth entry, even though it's a 2, but the function, remember, says, just the first instance, because we've put in a 1 there. Let's copy this down the column and check out a few more. A lot of these that begin with 2, well, you see immediately what's happened: the Q is out there instead.

Others like this one, the 2 doesn't appear until way over here, and it gets replaced with the Q. When there are multiple 2s, only the first one gets replaced with Q. So, pretty arcane stuff in a certain sense, but the tool that's definitely needed when you're in that situation and you've got to clean up data. You're trying to make wholesale changes to codes. Substitute function works fine for this. And previously in this movie, we saw how to use the Find & Select feature for unusual characters, like the asterisk.

We used a tilde to embed the asterisk within it to replace those kinds of entries.

Find answers to the most frequently asked questions about Cleaning Up Your Excel 2010 Data .


Expand all | Collapse all
please wait ...
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.
 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

* Estimated file size

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Cleaning Up Your Excel 2010 Data.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.