Excel звездочка в строке

Хитрости »

1 Май 2011              135755 просмотров

Как заменить/удалить/найти звездочку?

Наверное кто-то уже сталкивался с подобной ситуацией — на листе в ячейках записаны слова/предложения, в которых имеется знак — * — звездочка. Иногда его нужно либо найти, либо заменить на другой символ, либо просто удалить. Например, строку вида «496*120*45» надо преобразовать в такой: «496x120x45«. И вот тут как раз возникает проблема: символ звездочки(как и знак вопроса) является служебным символом — он заменяет собой группу любых символов(а знак вопроса — один любой символ). И при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Сейчас пока разговор про стандартную замену — (Excel 2003: Правка —Заменить; Excel 2007-2010: вкладка Главная —Найти и выделить —Заменить) — Ctrl+H. Неужели ничего нельзя сделать? Можно. Разработчики предусмотрели такой вариант. Просто перед служебным символом необходимо поставить другой служебный знак, который сообщает Excel, что следующий за ним символ не выполняет свои служебные функции, а выступает в роли обычного символа. Знак этот называется Тильда. Вот такой — ~. На обычной клавиатуре этот знак обычно расположен на кнопке вместе с буквой Ё.

Таким образом в поле Найти поискового окошка должен получиться такой текст — «~*«(в поле Заменить на указывается символ/ы, которым требуется заменить звездочку. Если звездочку надо удалить — поле оставляем пустым). И не стоит забывать про дополнительные параметры замены(они раскрываются при нажатии кнопки «Параметры» поискового окна). А именно — необходимо поставить/снять галочку у параметра Ячейка целиком. В рассматриваемом случае эту галочку необходимо снять.

Если же надо найти саму Тильду, то тут все также — просто указываете две тильды: ~~

По тому же принципу этот символ используется и в формулах, которые работают с служебными символами (ВПР, СЧЁТЕСЛИ, СУММЕЛИ и т.п.)

Но. Всегда есть но. Некоторые формулы любые символы воспринимают как текст сразу и для них нет такого понятия, как служебный символ. К ним относится функция НАЙТИ. В данной функции для поиска данного символа не надо ставить перед ним еще один, потому как она не воспринимает символы подстановки как таковые. Она абсолютно любой символ принимает как текст и ищет строго то, что указано:
=НАЙТИ(«~»; A1)
=НАЙТИ(«*»; A1)
=НАЙТИ(«?»; A1)
=FIND(«~», A1)
=FIND(«*», A1)
=FIND(«?», A1)

Есть аналогичная ей функция — ПОИСК. В этой функции обязательно применять тот же принцип, что и во всех вышеперечисленных — т.е. явно указывать Excel, что мы ищем спец.символ:
=ПОИСК(«~~»; A1)
=ПОИСК(«~*»; A1)
=ПОИСК(«~?»; A1)
=SEARCH(«~~», A1)
=SEARCH(«~*», A1)
=SEARCH(«~?», A1)

И к слову сказать, символы звездочки и вопр.знака так же используются формулами ВПР(VLOOKUP), ГПР(HLOOKUP) и ПОИСКПОЗ(MATCH). При этом самый неприятный момент здесь заключается в том, что звездочка, вопросительный знак или сама тильда могут быть внутри текста в искомом массиве данных:

и как видно в этом случае данные с тильдой не могут быть найдены. Чтобы найти их через ВПР(и прочие схожие функции) необходимо заменить тильду на две. Я бы советовал делать это так(на примере формул из таблицы выше):
=ВПР(ПОДСТАВИТЬ(F2;»~»;»~~»);$A$2:$C$7;3;0)
=VLOOKUP(SUBSTITUTE(F2,»~»,»~~»),$A$2:$C$7,3,0)

И хоть данные со звездочкой и вопр.знаком в большинстве случаев мешать не будут, можно попасть на ситуацию, когда тексту со звездочкой будет сопоставлен неверный результат:

почему ВПР так поступает лучше прочитать здесь: Как найти значение в другой таблице или сила ВПР. А в этой статье я лишь приведу формулу, как избежать таких проблем. Так же как и с тильдой используем функцию ПОДСТАВИТЬ(SUBSTITUTE), но теперь подставляем тильду к звездочке:
=ВПР(ПОДСТАВИТЬ(D2;»*»;»~*»);$A$2:$A$4;1;0)
=VLOOKUP(SUBSTITUTE(D2,»*»,»~*»),$A$2:$A$4,1,0)

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

With wildcards in Excel, you can narrow down your search in spreadsheets. Here’s how to use these characters in filters, searches, and formulas.

Wildcard characters are special characters in Microsoft Excel that let you extend or narrow down your search query. You can use these wildcards to find or filter data, and you can also use them in formulas.

In Excel, there are three wildcards: Asterisk (*), question mark (?), and tilde (~). Each of these wildcards has a different function, and though only three, there’s a lot you can accomplish with them.

Wildcards in Excel

• Asterisk *: Represent any number of characters where the asterisk is placed.
  • A query such as *en will include both Women and Men, and anything else that ends in en.
  • Ra* will include Raid, Rabbit, Race, and anything else starting with Ra.
  • *mi* will include anything that has a mi in it. Such as Family, Minotaur, and Salami.
• Question Mark ?: Represents any single character where the question mark is: S?nd. This includes Sand and Send. Moreover, ???? will include any 4 letter text.
• Tilde ~: Indicates an upcoming wildcard character as text. For instance, if you want to search for a text that contains a literal asterisk and not a wildcard asterisk, you can use a tilde. Searching for Student~* will bring up only Student*, while searching for Student* will include every cell that starts with Student.

How to Filter Data With Wildcards in Excel

With wildcards, you can make a specific filter for your data in Excel. In this example spreadsheet, we have a list of the first ten elements from the periodic table. We’re going to make two different filters: First to get a list of elements that end with ium, and second to get a list of elements that only have four letters in their names.

1. Click the header cell (A1) and then drag the selection over to the last element (A11).
2. On your keyboard, press Ctrl + Shift + L. This will make a filter for that column.
3. Click the arrow next to the header cell to open the filter menu.
4. In the search box, enter *ium.
5. Press Enter.
6. Excel will now bring up all the elements in the list that end with ium.

Now let’s run a filter to see which elements in this list have only four letters in their names. Since you’ve already created a filter, you’ll just need to adjust the content of the search box.

1. Click the arrow next to the header cell. This will open the filter menu.
2. In the search box, enter ????.
3. Press Enter.
4. Excel will now show only the elements that have four letters in their names.

To remove the filter and make all the cells visible:

1. Click the arrow next to header cell.
2. In the filter menu, under the search box, mark Select All.
3. Press Enter.

If you’re curious about the filter tool and want to learn more about it, you should read our article on how to filter in Excel to display the data you want.

How to Search Data With Wildcards in Excel

The Find and Replace tool in Excel supports wildcards and lets you use them to narrow down or expand your search. In the same spreadsheet, let’s try to search for elements that begin and end with N.

1. Press Ctrl + F on your keyboard to bring up the Find and Replace window.
2. In the text box in front of Find what enter N*N.
3. Click Find All.
4. The window will extend to display a list of cells that contain elements beginning and ending with N.

Now let’s run a more specific search. Which element in the list begins and ends with N, but has only four letters?

1. Press Ctrl + F on your keyboard.
2. In the text box, type N??N.
3. Click Find All.
4. The Find and Replace window will extend to show you the result.

How to Use Wildcards in Excel Formulas

While not all functions in Excel support wildcards, here are the six ones that do:

• AVERAGEIF
• COUNTIF
• SUMIF
• VLOOKUP
• SEARCH
• MATCH

In this example, we have a list of cars and their engine configurations. We’re going to use the COUNTIF function to count three kinds of cars and then display the results in three separate cells. First, we can count how many cars are powered by 12-cylinder engines, then we can count how many cars have four-letter names.

Now that you have a grip on wildcards, it’s easy to guess that the first and second objectives can be accomplished solely with the asterisk.

1. Select cell E6 (in our example).
2. In the formula bar, enter the formula below:

   =COUNTIF(B2:B11, "*12")

   This formula will take the cells B2 to B11 and test them for the condition that the engine has 12 cylinders or not. Since this is regardless of the engine’s configuration, we need to include all the engines that end with 12, thus the asterisk.

3. Press Enter.
4. Excel will return the number of 12-cylinder cars in the list.

Counting the number of GT cars is done similarly. The only difference is an additional asterisk at the end of the name, which alerts us to any cars that do not end with GT but still contain the letter.

1. Select cell E7.
2. In the formula bar, enter the formula below:

   =COUNTIF(A2:A11, "*GT*")

   The formula will test cells A2 to A11 for the *GT* condition. This condition states that any cell that has the phrase GT in it, will be counted.

3. Press Enter.
4. Excel will return the number of GT cars.

The final objective is slightly different. To count the cars that have four-letter model names, we’re going to need the question mark wildcard.

1. Select cell E8.
2. In the formula bar, enter the formula below:

   =COUNTIF(A2:A11, "* ????")

   This formula will test the car names, the asterisk states that the car brand can be anything, whereas the four question marks make sure that the car model is in four letters.

3. Press Enter.
4. Excel will return the number of cars that have four-letter models. In this case, it’s going to be the Golf and the DB11.

Get the Data You Want With Wildcards

Wildcards are simple in nature, yet they have the potential to help you achieve sophisticated things in Excel. Coupled with formulas, they can make finding and counting cells much easier. If you’re interested in searching your spreadsheet more efficiently, there are a couple of Excel functions that you should start learning and using.