This tip is useful when you want to replace or find text using Wildcards, e.g. if you have a column where you would like to replace everything after the space character with text.
What are Wildcards?
Wildcards are characters that are used to represent one or more characters.
The following wildcard characters can be used in Excel:
- * (asterisk) – Can take the place of one or more characters
- ? (question mark) – Takes the place of one character
- ~ (tilde) – Indicates that the next character shouldn’t be treated as a wildcard
The examples below show how these wildcard characters can be used.
Using the * character to replace
Consider the following data in column A which contains an Id and Name separated by a space.
If you wanted to keep only the names, you could use the Text to Columns option from the Data Tab, choose Delimited, select Space and keep the column with names.
An alternate method is:
- Highlight column A
- Select Find and Replace, this can be easily done using the shortcut Ctrl and H
- In the Find What box, type * (asterisk and a space)
- Click Replace All
The * wildcard character replaced everything up until a space was found.
Using the ? and ~ character to find/filter
Consider the following data in column A that contains names.
If you wanted to filter on Names that started with the letter L and that were 4 letters in total, you would filter/search on L??? which would result in the following rows being shown.
Notice that Linda is not part of the filtered list since it contains 5 letters.
If you wanted to filter only on rows that started with L?, you would filter/search on L~?* which would result in one row being shown since ~ specifies that the next character shouldn’t be treated as a wildcard.
Searching on L?* would show the following two rows.
This is a quick and easy method to replace or find data.
The post Using Wildcards to make finding or replacing data in Excel quicker and easier appeared first on Sage Intelligence.
Source: Excel on Steroids