Интересное и полезное о Microsoft Excel с минимумом теории
Заглавные и строчные буквы в Excel
Мне иногда не хватает в Excel команды, аналогичной "Регистр" Word'а, которая в один щелчок могла бы превращать текст в читаемый. Например, сделать из полностью строчного текста предложения. Иными словами, менять регистр и превращать заглавные, то есть прописные буквы в строчные и наоборот. Заглавные и строчные буквы в Excel приходится преобразовывать совсем по-другому, а именно с помощью функций.
Функции
В первую очередь обратим внимание на функции, которых в простых случаях хватит:
Функция СТРОЧН(). У нее только один аргумент - текст, который она перебирает и каждую букву которого превращает в строчную.
Функция ПРОПИСН(). Аналогичная предыдущей, только превращает все наоборот в заглавные (прописные) буквы.
Функция ПРОПНАЧ(). Эта функция превращает в заглавную первую букву каждого слова.
В принципе, этого хватает. На рисунке можно посмотреть результат работы этих функций:
Сложные режимы
Конечно, бывают случаи, когда простого использования этих функций недостаточно. Например, уже упомянутая команда "Регистр" содержит 5 режимов:
Как видите, три режима вполне реализуются через текстовые функции. Но остальные две, а именно - "Как в предложениях" и "иЗМЕНИТЬ РЕГИСТР" стандартными средствами не достичь.
У вас есть следующие варианты для того, чтобы получить желаемое:
Метод простой и грубый . Как говорил Принц-Полукровка в "Гарри Поттере": "просто засуньте им в глотку безоар". А именно, копируете нужный текст, переходите в Word, там вставляете, применяете команду "Регистр", потом получившийся текст тащите обратно в Excel. Преимущества - скорость и незамысловатость. Недостаток - с большими таблицами придется повозиться, менять форматирование и т.д.
Использовать макрос или пользовательскую функцию. Лично я пытался реализовать через формулы массива или через комбинации функций, но получалось так громоздко и неэффективно по сравнению с найденным на сайте "Мир MS Excel" способом, что я решил его передать без каких-либо поправок.
Пользовательская функция
Вот сам текст функции:
Function ConvertRegistr(sString As String, Tip As Byte) As String
'Tip = 1 - ВСЕ ПРОПИСНЫЕ
'Tip = 2 - все строчные
'Tip = 3 - Начинать С Прописных
'Tip = 4 - Как в предложениях
'Tip = 5 - иЗМЕНИТЬ рЕГИСТР
Dim i&
If Tip = 4 Then
ConvertRegistr = StrConv(sString, 2)
Mid$(ConvertRegistr, 1, 1) = UCase(Mid$(ConvertRegistr, 1, 1))
ElseIf Tip > 4 Then
For i = 1 To Len(sString)
Mid$(sString, i, 1) = IIf(Mid$(sString, i, 1) = UCase(Mid$(sString, i, 1)), _
LCase(Mid$(sString, i, 1)), UCase(Mid$(sString, i, 1)))
Next
ConvertRegistr = sString
Else
ConvertRegistr = StrConv(sString, Tip)
End If
End Function
Команда Insert/Module.В открывшееся поле вставляете текст.
Переходите в Excel, теперь в списке функций, в полном алфавитном перечне у вас появилась функция ConvertRegistr.
У этой функции два аргумента - ячейка и режим преобразования регистра. В тексте функции есть комментарии, какой режим обозначен какой цифрой. По сути она заменяет предыдущие три функции, а работает полным аналогом уже упоминавшейся команды "Регистр". Правда, есть нюанс - 4й пункт - "Как в предложениях" - работает только для одного предложения. Если кому нужно, пишите на мэйл, скину рабочий вариант, который переработает нормальный литературный текст.
Если вы хотите, чтобы функция всегда была под рукой, сохраните файл как надстройку Excel в папке Addins. У меня она находится по адресу: C:\Users\user\AppData\Roaming\Microsoft\AddIns.
Активируйте надстройку через Офис/Параметры Excel/Надстройки/Перейти в надстройки. Там появится окно, где надо будет поставить галочку напротив названия файла. Также возможно прописать макрос пребразования регистра в диапазонах, это вы сможете посмотреть здесь.
Удачи в использовании! Эксель Практик «Глаза боятся, а руки делают»
P.S. Понравилась статья? Подпишитесь на рассылку в правой части страницы (Бесплатный курс "Топ-10 инструментов Excel") и будьте в курсе новых событий.
Заглавные и строчные буквы в Excel: 12 комментариев
команды «регистр» в Экселе не существует … к преогромному сожалению.
roman
Статья полезная, но почему-то не указано некоторое количество важной информации. Как VBA открыть написано, а где находится список функций нет. А как сохранить скрипт в виде add in? Тоже, видимо, должно было всем понятно.
roman
Опять же, как добавить кнопку на панель тоже не написано. Видимо это куда проще, чем запустить VBA через Excel.
adminАвтор записи
Роман, спасибо, исправлюсь. На момент написания статьи сделал быстро, некоторые моменты можно было сделать проще.
Ная
Здравствуйте!
Статья понравилась, очень нужная вещь. Столкнулась с одной трудностью:
когда использую вышеописанную пользовательскую функцию со значением 4 (Как в предложениях), то заглавной становится только первая буква, даже если в ячейке стоит точка и после неё едёт новое предложение, т.е. новое предложение начинается со строчной буквы
Excel 2013
adminАвтор записи
Вообще-то да. Возьму на заметку, постараюсь залить расширенный вариант.
артем
=ПРОПИСН(ПСТР(C2;1;1))&ПСТР(C2;2;ДЛСТР(C2)-1)
Юлия
Здравствуйте. А может Вы мне сможете помочь? Есть такой текст «Выдача кредитов; прием платежей; обменный пункт; отдел по работе с населением», а его надо преобразовать вот в такой «Выдача кредитов; Прием платежей; Обменный пункт; Отдел по работе с населением». Т .е. каждая буква после точки с запятой должна быть большой. Не каждого слова, не начало предложение а именно после «;». Как добиться подобного результата? Что написать, и куда это потом вставлять и как применять? Пожалуйста подскажите, если знаете.
adminАвтор записи
Изучите функции СЦЕПИТЬ, НАЙТИ, ПСТР, в принципе с теми, что есть в статье этого хватит. Или пришлите файл на почту.
команды «регистр» в Экселе не существует … к преогромному сожалению.
Статья полезная, но почему-то не указано некоторое количество важной информации. Как VBA открыть написано, а где находится список функций нет. А как сохранить скрипт в виде add in? Тоже, видимо, должно было всем понятно.
Опять же, как добавить кнопку на панель тоже не написано. Видимо это куда проще, чем запустить VBA через Excel.
Роман, спасибо, исправлюсь. На момент написания статьи сделал быстро, некоторые моменты можно было сделать проще.
Здравствуйте!
Статья понравилась, очень нужная вещь. Столкнулась с одной трудностью:
когда использую вышеописанную пользовательскую функцию со значением 4 (Как в предложениях), то заглавной становится только первая буква, даже если в ячейке стоит точка и после неё едёт новое предложение, т.е. новое предложение начинается со строчной буквы
Excel 2013
Вообще-то да. Возьму на заметку, постараюсь залить расширенный вариант.
=ПРОПИСН(ПСТР(C2;1;1))&ПСТР(C2;2;ДЛСТР(C2)-1)
Здравствуйте. А может Вы мне сможете помочь? Есть такой текст «Выдача кредитов; прием платежей; обменный пункт; отдел по работе с населением», а его надо преобразовать вот в такой «Выдача кредитов; Прием платежей; Обменный пункт; Отдел по работе с населением». Т .е. каждая буква после точки с запятой должна быть большой. Не каждого слова, не начало предложение а именно после «;». Как добиться подобного результата? Что написать, и куда это потом вставлять и как применять? Пожалуйста подскажите, если знаете.
Изучите функции СЦЕПИТЬ, НАЙТИ, ПСТР, в принципе с теми, что есть в статье этого хватит. Или пришлите файл на почту.
=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A2;1));ПРАВСИМВ(A2;ДЛСТР(A2)-1))
Функция, которая делает заглавными буквы из соседней ячейки.
Cпасибо огромное
очень помогли
Первые три функции очень помогли, хочу сказать Вам огромное РАХМАТ!!!
Функция СТРОЧН().
Функция ПРОПИСН().
Функция ПРОПНАЧ().