Сделать аббревиатуру в эксель

Обновлено: 22.01.2025

Надстройка предназначена для удаления лишних символов из словосочетаний и перестановки заданных аббревиатур и слов(ПБОЮЛ, ОАО, ООО и т.д.) из указанного места строки в конец. Для примера в описании взяты названия организаций. Конечно же, каждый может придумать свое применение, т.к. не обязательно это должны быть названимя организаций - это могут быть любые слова и символы.

Особенность надстройки - вы можете сами создавать, менять списки для замены/перестановки.

Надстройка включает в себя:

Текст( A1 ) - текст для преобразования, содержащий наименования организаций. Может быть текстом или ссылкой на ячейку.
Организ_Поз(1) - необязательный аргумент. Если не указан, то наименование организации ищется во всем тексте, указанном параметром Текст и при успешном нахождении будет перемещено в конец. Если указан - ищется только в том слове параметра Текст, номер позиции которого указан. Номер позиции можно узнать, если разбить Текст на отдельные слова, используя в качестве разделителя пробел. Т.е. наименование "ООО Рога и Копыта" будет разбито на 4 слова: ООО ; Рога ; и ; Копыта . Если указать Организ_Поз равным 2, то ничего перенесено не будет, т.к. второе слово получается - "Рога" и оно не присутствует в списке(Организации). Если указать 1 или не указывать ничего, то ООО будет перенесено в конец - "Рога и Копыта ООО" .
Удалять_символы(1) - необязательный аргумент. Если не указан либо указана 1, то все лишние символы удаляется. Если в качестве аргумента указан 0, то лишние символы не будут удалены. По умолчанию принимает значение - 1(т.е. символы удаляются). Перечень символов содержится в списке Символы, который доступен с панели Надстройки -Управления списками
Удалять_Организации(0) - необязательный аргумент. Если не указан либо равен нулю, то наименование организации не удаляется. Если в качестве аргумента указана цифра, отличная от нуля, то наименования организаций будут удалены. По умолчанию принимает значение - 0. Перечень организаций содержится в списке Организации, который доступен с панели Надстройки -Управления списками
Учитывать_регистр - необязательный аргумент. Если не указан либо равен нулю, либо указано ЛОЖЬ (FALSE) то поиск организаций внутри текста производится без учета регистра букв. Если указан ИСТИНА (TRUE) или 1, то поиск будет производится с учетом регистра. Для чего это нужно: чтобы в расчет не брались те данные, которые написаны не в верхнем регистре(ооо, зао, ип). Иногда бывает нужно выбрать только определенные наименования из всего текста и таким образом можно исключить те данные, которые просматривать не надо.
Организ_ДО - необязательный аргумент. Если не указан либо равен нулю, то наименования будут перемещаться в конец строки. Если указан как ИСТИНА(TRUE) или 1 - то организации будут помещаться перед остальным текстом.

Диапазон исходных значений - диапазон со значениями для преобразования.
Ячейка для вставки значений - указывается одна(первая) ячейка диапазона в любом месте. Именно с указанной ячейки начнется вставка преобразованных значений. Значения вставляются в том же порядке, в каком они расположены в Диапазоне исходных значений. Если ячейка не указана - то заменяются значения исходного диапазона.
Удалить символы - Если установлен, то все лишние символы удаляется. Если отключен, то лишние символы не будут удалены. По умолчанию установлен. Перечень символов содержится в списке Символы, который доступен с панели Надстройки -Управления списками
Удалить организации - Если не установлен, то наименование организации удаляется. Если установлен, то наименования организаций будут удалены. По умолчанию флажок снят. Перечень организаций содержится в списке Организации, который доступен с панели Надстройки -Управления списками
Позиция организации - Если указан - наименование организации просматривается только в том слове ячейки, номер которого указан. К примеру наименование ООО Рога и Копыта будет разбито на 4 слова: ООО;Рога; и; Копыта. Если указать 2, то ничего перенесено не будет, т.к. второе слово - Рога и оно не присутствует в списке(Организации). Если не указан, то ООО будет перенесено в конец - Рога и Копыта ООО.
Перемещать вперед - Если не установлен, то наименования будут перемещаться в конец строки. Если установлен, то организации будут помещаться перед остальным текстом.
Учитывать регистр - Если не установлен, то поиск организаций внутри текста производится без учета регистра букв. Если установлен, то поиск будет производится с учетом регистра. Для чего это нужно: чтобы в расчет не брались те данные, которые написаны не в верхнем регистре(ооо, зао, ип). Иногда бывает нужно выбрать только определенные наименования из всего текста и таким образом можно исключить те данные, которые просматривать не надо.

Организации - Список должен содержать только аббревиатуры юридической формы организаций(ООО, ЗАО, ОАО, ИП и т.п.) в верхнем регистре. Если в список будет добавлено значение в нижнем регистре, то оно будет преобразовано в верхний регистр автоматически - ПБоюЛ будет преобразовано в ПБОЮЛ. Это необходимо для точного поиска организаций.
Символы - содержит список любых символов - точка; запятая; точка с запятой и т.д. При использовании функции все символы в исходной строке, которые присутствуют в списке будут удалены.

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

Очистить списки - списки Организаций и Символов будут удалены. Операция необратима, поэтому перед выполнением убедитесь, что действительно хотите полностью удалить все списки.
Удалить выбранное - предварительно в списках выбираются позиции(путем проставления флажков напротив значений), которые необходимо удалить. После нажатия Удалить выбранное и подтверждения удаления - выбранные позиции будут удалены из списков.
Добавить данные - появляется форма добавления данных:

В какой список добавить - выбирается список для добавления данных. По умолчанию - Список символов.
Метод добавления:
- Ручной ввод - вводите в текстовое поле нужный символ/наименование. При добавлении в список Организации, введенное значение будет преобразовано в верхний регистр.
- Выбрать из диапазона - указывается диапазон, который содержит необходимые значения. При добавлении в список Организации, введенное значение будет преобразовано в верхний регистр.
Особенность данной формы: закрыть можно только кнопкой Закрыть.

FunctionsMoveAndReplace.zip (87,2 KiB, 3 688 скачиваний)

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

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

Преобразование полных имен состояний в аббревиатуры с помощью функции Vlookup

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

Сначала скопируйте и вставьте названия левых состояний и их аббревиатуры на свой рабочий лист, где вы хотите их использовать.

Предположим, я вставляю их в ячейку A1: B51, а мой диапазон данных в D1: E11, см. Снимок экрана:

Введите или скопируйте следующую формулу в ячейку E2:

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

Внимание: В приведенной выше формуле D2 - название штата, относительное сокращение которого вы хотите вернуть, A2: B51 диапазон данных, который вы хотите найти, число 2 указывает номер столбца, в котором возвращается совпадающее значение.

Преобразование сокращений в полные имена состояний с помощью функций ИНДЕКС И ПОИСКПОЗ

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

Скопируйте или введите приведенную ниже формулу в пустую ячейку, в которой вы хотите получить результат:

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

Внимание: В этой формуле D2 - это значение критерия, относительная информация которого вы хотите вернуть, A2: A51 это список, содержащий значение, которое вы хотите вернуть, B2: B51 столбец, который вы ищете.

Преобразуйте полные названия штатов в аббревиатуры или наоборот с помощью замечательной функции

Если у вас есть Kutools for Excel, С его ПОСМОТРЕТЬ справа налево утилиту, вы можете быстро и легко вернуть сокращения или полные имена состояний из исходного имени состояния и таблицы сокращений.

Советы: Чтобы применить это ПОСМОТРЕТЬ справа налево функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Kutools > Супер поиск > ПОСМОТРЕТЬ справа налево, см. снимок экрана:

2. В ПОСМОТРЕТЬ справа налево диалоговом окне выполните следующие действия:
- Выберите ячейки значений поиска и выходные ячейки из Значения поиска и диапазон вывода раздел;
- Затем укажите соответствующие элементы из Диапазон данных .
3, Затем нажмите OK , соответствующие записи были возвращены на основе значений поиска, см. снимок экрана:

Советы: Чтобы получить сокращения, основанные на полных названиях штатов, вам просто нужно изменить Ключевой столбец и Столбец возврата в ПОСМОТРЕТЬ справа налево диалоговое окно по мере необходимости.

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

В Office 365 и Excel 2019

Освободи Себя ТЕКСТ ПРИСОЕДИНИТЬСЯ function - это новая функция, доступная в Office 365 и Excel 2019, которую можно использовать для извлечения всех начальных букв из каждого слова.

Общая формула:

TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(text,ROW(INDIRECT("1:"&LEN(text))),1)),
ROW(INDIRECT("63:90")),0)),MID(text,ROW(INDIRECT("1:"&LEN(text))),1),""))

аргументы

Text: the text string you want to abbreviate.

Как работает эта формула

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

Нажмите Shift + Ctrl + Enter key, и все инициалы каждого слова будут извлечены.

объяснение

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

2. MID, РЯД, КОСВЕННЫЕ и LEN функции используются для преобразования текстовой строки в массив или буквы. Предположим

Нажмите Shift + Ctrl + Enter

Возвращает массив букв

Затем этот массив передается в функцию CODE, которая выводит массив числовых кодов ascii, по одному коду для каждой буквы.

3. СТРОКА (КОСВЕННАЯ ("63: 90") соответствует кодам ascii для всех заглавных букв AZ.

4. Функции ЕЧИСЛО, ЕСЛИ и ПОИСКПОЗ используются для фильтрации результатов для окончательного объединения текста.

Примечания:

1. Если в текстовой строке нет заглавных букв, эта формула возвращает пустое значение.

2. Эта формула извлечет все заглавные буквы.

В Office 2016 или предыдущих версиях

Если вы используете Excel 2016 или предыдущие версии, вы можете использовать TRIM функции.

Общая формула:

TRIM(LEFT(Text,1)&MID(Text,FIND(" ",Text&" ")+1,1)&MID(Text,FIND("*",SUBSTITUTE(Text&" "," ","*",2))+1,1))

аргументы

Text: the text string you want to extract the first letters of each word.

Как работает эта формула

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

Нажмите Enter key, извлекаются все первые буквы каждого слова в ячейке A1.

объяснение

1. Функция TRIM удаляет все лишние пробелы в текстовой строке.

2. ВЛЕВО (A1,1) извлекает первую букву текстовой строки.

3. MID (A1; НАЙТИ ("", A1 & "") +1,1) извлекает первую букву второго слова, разделенного пробелом.

4. MID (A1, НАЙТИ ("*", ПОДСТАВИТЬ (A1 & "", "", "*", 2)) + 1,1)) извлекает первую букву третьего слова, разделенную пробелом.

Примечания:

1. Эта формула работает только для трех или менее слов в ячейке.

2. Вы можете заменить «» в формуле на другие разделители.

3. Эта формула извлекает первые буквы без учета регистра. Если вы хотите, чтобы формула всегда возвращалась в верхнем регистре, добавьте в формулу функцию UPPER.

Файл примера

Относительные формулы
Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Функция ТЕКСТ преобразует значение в текст с заданным форматом в Excel.

Синтаксис

=TEXT ( value , format_text )

аргументы

Стоимость: Числовое значение, которое нужно преобразовать в текст.

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

Возвращаемое значение

Примечания к функциям

Функция ТЕКСТ преобразует числовое значение в текстовый формат, но результат не позволит вам ссылаться на другие вычисления. Лучше всего разделить исходное значение и функцию ТЕКСТ в разных ячейках и ссылаться на исходное значение в других вычислениях вместо текста результата.

Примеры

Примеры, показанные на скриншоте ниже, помогут вам преобразовать дату в различные форматы даты в Excel.

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

Здесь представлены символы настраиваемого форматирования даты, которые Excel распознает как показанную ниже таблицу.

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

Связанные функции

Функция ПОДСТАВИТЬ в Excel
Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.

Функция Excel TEXTJOIN
Функция Excel TEXTJOIN объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.

Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.

Функция ВЕРХНИЙ в Excel
Функция Excel ВЕРХНИЙ преобразует все буквы заданного текста в верхний регистр.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Читайте также: