Excel из ячейки удалить часть текста

Как вытащить число или часть текста из текстовой строки в Excel

Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.

Как правило, экономисту поручают проведение всевозможных видов анализа на основании бухгалтерских данных, группировку их специальным образом, получение дополнительных срезов, отличающихся от имеющихся бухгалтерских аналитик и т.д.

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета. Мы не будем говорить о необходимости сближения бухгалтерского и управленческого учета, или, по крайней мере, получения нужных срезов и аналитик в имеющихся учетных программах в автоматическом режиме. К сожалению, зачастую экономисту приходиться «перелопачивать» огромные объемы информации вручную.

И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.

Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:

Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.

В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.

Источник: excel-training.ru

Удалить из текста список слов

Периодически возникает необходимость удалить слова из текста в ячейках. У вас есть список слов, и нужно удалить их все из списка фраз, если они в нем присутствуют. Зачастую, но не всегда такое приходит в голову после того, как удалось обнаружить слова и/или извлечь слова из списка и понять, что они — лишние.

  • Например, вы – контент-менеджер интернет-магазина. Ваш каталог представляет собой Excel-файл. В нем товары представляют собой массив ячеек, содержащий названия продукта и фирмы-производителя. У вас есть список фирм-производителей, а ваша задача – удалить их и оставить только названия продуктов.
  • Или у вас каталог автомобилей в виде одной строки – марка и модель. Вы хотите разбить его на марки и модели, и все что у вас есть – список из

В обоих случаях можно было бы воспользоваться последовательной заменой в выделенном списке ячеек исходных значений на пустоту. Но это, очевидно, займет довольно много времени, особенно, если каталог большой и каждая замена не происходит моментально.

Для таких случаев может пригодиться макрос удаления списка слов из ячеек. Макрос находится в группе «УДАЛИТЬ» в меню «Удалить слова«

Местоположение макроса «удалить из ячеек любые слова из указанного списка»

Как и все макросы, работающие со словами, он определяет целые слова и удаляет строго только их, пропуская простые вхождения. Это позволяет избежать непреднамеренных удалений частей слов и «повреждения» данных. Однако, если в вашем списке не только слова, но и словосочетания, он вам не подойдет. Смотрите как удалить список фраз из ячеек.

Как удалить из текста список слов с помощью !SEMTools:

Все очень просто. Выделяем список фраз, вызываем макрос из меню, он предлагает выделить список слов, выделяем его и жмем «ОК». Вуаля!
Важно:
Слова удаляются в исходном диапазоне и навсегда. Рекомендуется копировать столбец, оставляя исходные данные, как показано на видеоинструкции ниже, если не уверены в результате:

Удалить список слов из ячеек в Excel — видеоинструкция

Удалить слова во всех словоформах

Если стоит задача удалить слова во всех их словоформах, а перебирать их вручную не очень хочется, есть пара вариантов:
1. Можно воспользоваться макросом удаления слов по условию «начинается с». Такой вариант менее предпочтителен, если вы не уверены, что в массиве нет других слов, начинающихся с указанной последовательности. И точно непригоден для удаления коротких слов.
2. Можно воспользоваться модулем морфологии и быстро просклонять слова в Excel автоматически, не прикладывая лишних усилий. Но и тут есть примечение. Модуль Морфология в !SEMTools на текущий момент работает только с существительными и прилагательными.

Источник: semtools.guru

Microsoft Excel

трюки • приёмы • решения

Как извлечь слова из строки таблицы Excel

Формулы в этой статье полезны для извлечения слов из текста, содержащегося в ячейке. Например, вы можете создать формулу для извлечения первого слова в предложении.

Извлечение первого слова из строки

Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(” “;A1)-1) .

Эта формула возвращает весь текст до первого пробела в ячейке A1. Однако у нее есть небольшой недостаток: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, потому что состоит из одного слова. Несколько более сложная формула решает проблему с помощью новой функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(” “;A1)-1);A1) .

Если вам нужно, чтобы формула была совместима с более ранними версиями Excel, вы не можете использовать ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(” “;A1));A1;ЛЕВСИМВ(A1;НАЙТИ(” “;A1)-1))

Извлечение последнего слова строки

Извлечение последнего слова строки — более сложная задача, поскольку функция НАЙТИ работает только слева направо. Таким образом, проблема состоит в поиске последнего символа пробела. Следующая формула, однако, решает эту проблему. Она возвращает последнее слово строки (весь текст, следующий за последним символом пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(“*”;ПОДСТАВИТЬ(A1;” “;”*”;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;””;””)))))

Но у этой формулы есть такой же недостаток, как и у первой формулы из предыдущего раздела: она вернет ошибку, если строка не содержит по крайней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(“*”;ПОДСТАВИТЬ(A1;” “;”*”;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””)))));A1)

Следующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(” “;A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(“*”;ПОДСТАВИТЬ(A1;””;”*”;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))))))

Извлечение всего, кроме первого слова строки

Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(” “:A1;1)) .
Если ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.

Формула возвращает ошибку, если ячейка содержит только одно слово. Следующая версия формулы использует функцию ЕСЛИОШИБКА, чтобы можно было избежать ошибки; формула возвращает пустую строку, если ячейка не содержит более одного слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(” “;A1;1));””)

А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(” “;A1));””;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(” “;A1;1)))

Источник: excelexpert.ru

Как обрезать символы в Excel

Обзор способов, позволяющих быстро обрезать значения ячеек и удалить лишний текст. Обрезка текста формулами слева и справа, быстрая обрезка строк надстройкой VBA, удаление лишнего текста до определенного символа, разбиение текста по столбцам.

Как обрезать символы при помощи формул?

Как оставить заданное количество символов в начале строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ЛЕВСИМВ», которая возвращает заданное количество знаков с начала строки.

Как удалить заданное количество знаков в начале строки, остальные оставить?

Ввести в ячейку комбинацию стандартных функций Excel «ПРАВСИМВ» и «ДЛСТР», которая определяет длину строки, то есть количество символов в значении указанной ячейки.

Как оставить заданное количество символов в конце строки, остальные удалить?

Ввести в ячейку стандартную текстовую функцию Excel «ПРАВСИМВ», которая возвращает заданное количество знаков с конца строки.

Как удалить заданное количество знаков в конце строки, остальные оставить?

Ввести в ячейку комбинацию стандартных функций Excel «ЛЕВСИМВ» и «ДЛСТР»

Как видно из приведенных примеров, оставить текстовые строки заданной длины достаточно просто при помощи формулы в одно действие. Чтобы обрезать определенное количество знаков с начала или с конца строки, необходимо вносить в ячейки достаточно громоздкие формулы, что достаточно неудобно.

Как обрезать символы без ввода формул?

Надстройка для быстрой обрезки текста

Все описанные выше манипуляции с текстовыми значениями можно производить и без использования формул, что гораздо быстрее. Для этого нужно лишь вызвать диалоговое окно надстройки и задать в нем диапазон ячеек, подходящую функцию и количество знаков, которые необходимо удалить либо оставить.

1. обрезать заданное количество символов слева;

2. обрезать заданное количество символов справа;

3. обрезать значения ячеек до первого попавшегося заданного символа (сочетания символов);

4. обрезать значения ячеек до последнего попавшегося символа (сочетания символов).

В надстройку зашиты функции, аналогичные тем, которые описаны выше, что сокращает время на:

– выделение диапазона ячеек.

В программе предусмотрена возможность вместо выделения диапазона ячеек выделять целиком строки/столбцы. Программа автоматически сокращает выделенный диапазон до границы используемого диапазона, при этом на листе не должно быть отфильтрованных строк.

Разбиение текста по столбцам

Для обрезания значений ячеек можно использовать «Текст по столбцам». Это стандартное средство Excel не удаляет и не обрезает символы, а распределяет содержимое ячеек по разным столбцам при помощи мастера текстов. Кнопка вызова диалогового окна находится на вкладке «Данные» в группе кнопок «Работа с данными». Этот инструмент можно использовать в двух режимах работы, разделять исходные данные при помощи разделителей либо при помощи настраиваемых полей, когда можно вручную указать границы раздела значений.

Шаг1. Выбор формата исходных данных.

Шаг2. Установка нужной ширины полей.

Количество полей при этом не ограничено.

Шаг 3. Просмотр и получение результатов.

В результате таких манипуляций исходные значения нарезаются в соответствии с установленными полями и разносятся по различным столбцам.

Видео по быстрой обрезке символов в Excel

Источник: macros-vba.ru

Примеры использования функции ЛЕВСИМВ в Excel работа с текстом

Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.

Исходная таблица данных:

Для выделения первого символа, которым является искомое название переменной, используем формулу:

Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:

Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.

Выделим код ошибки с помощью следующей формулы:

  • ЛЕВСИМВ(B5;НАЙТИ(“3”;B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
  • 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).

В результате получим:

Выделим часть текста ошибки:

В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(“:”;B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).

В результате получим:

Округление чисел текстовыми функциями в текстовой строке

Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.

Форма для округления числовых значений имеет следующий вид:

В ячейке B3 введена следующая сложная формула:

=5,ЛЕВСИМВ(A2+1,НАЙТИ(“.”,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(“.”,A2)-1)),”Число не введено”)’ >

  1. Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
  2. Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(“,”;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
  3. Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(“,”;B2)-1), то есть целой части числа +1 по правилам округления.
  4. Если Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.

Функция ЛЕВСИМВ в Excel и особенности ее использования

Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:

  • текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
  • [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
  1. Если необязательный аргумента [число_знаков] явно не указан, по умолчанию будет возвращен первый символ строки. Например, функция =ЛЕВСИМВ(«текст») вернет значение «т».
  2. Числовое значение, переданное в качестве необязательного аргумента, должно быть взято из диапазона неотрицательных чисел, то есть должно быть равным нулю или больше нуля. В противном случае будет возвращен код ошибки #ЗНАЧ!.
  3. Если в качестве аргумента [число_знаков] будет переданы текстовые данные, функция ЛЕВСИМВ вернет код ошибки #ЗНАЧ!.
  4. Если число, переданное в качестве необязательного аргумента, превышает количество знаков в текстовой строке, рассматриваемая функция вернет весь текст целиком.
  5. Excel выполняет автоматическое преобразование типов данных там, где это возможно. Например, рассмотрим некоторые возможные варианты записи функции ЛЕВСИМВ и результаты ее выполнения:
  • ЛЕВСИМВ(«текст»;200%) вернет значение «те» (преобразование процентов в числовое значение);
  • при аргументах: («текст»;ИСТИНА) вернет «т» (логическое ИСТИНА эквивалентно числовому значению 1);
  • при аргументах: («текст»;ЛОЖЬ) вернет пустое значение «», поскольку логическое ЛОЖЬ эквивалентно числовому 0 (нуль);
  • при аргументах: («текст»;15/7) вернет значение «те».

Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.

Источник: exceltable.com

Сводные таблицы Excel

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel, далек от совершенства. Если он был введен другими пользователями или выгружен из какой-нибудь корпоративной БД или ERP-системы не совсем корректно, легко может содержать следующее.

  • лишние пробелы перед, после или между словами (для красоты!);
  • ненужные символы («г.» перед названием города);
  • невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция);
  • апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки).

Давайте рассмотрим способы избавления от такого «мусора».

Замена символов

Старый, но не устаревший трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная → Найти и выделить (Home → Find & Select → Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы в данном случае можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов.

Старый, но не устаревший трюк

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно.

СЖПРОБЕЛЫ удаляет из текста все пробелы, кроме одиночных пробелов между словами

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел, – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

  • Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
  • Использовать функцию ПЕЧСИМВ (CLEAN). Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справиться не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE). У нее три обязательных аргумента:

  • Текст, в котором производим замену.
  • Старый текст – тот, который заменяем.
  • Новый текст – тот, на который заменяем.

С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом).

Функция ПОДСТАВИТЬ может легко избавиться от ошибок

Удаление апострофов в начале ячеек

Апостроф – это специальный символ

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. Апостроф виден только в строке формул, на листе его не видно.

По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная → Число → Текстовый), и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.

Но иногда апостроф оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда его текст.

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

Теперь если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик → кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Источник: pivot-table.ru