Как сделать строки с большой буквы в excel?

Мне иногда не хватает в Excel команды, аналогичной «Регистр» Word'а, которая в один щелчок могла бы превращать текст в читаемый. Например, сделать из полностью строчного текста предложения.

Иными словами, менять регистр и превращать заглавные, то есть прописные буквы в строчные и наоборот.

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

Функции

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

1. Функция СТРОЧН(). У нее только один аргумент — текст, который она перебирает и каждую букву которого превращает в строчную.
2. Функция ПРОПИСН(). Аналогичная предыдущей, только превращает все наоборот в заглавные (прописные) буквы.
3. Функция ПРОПНАЧ(). Эта функция превращает в заглавную первую букву каждого слова.

В принципе, этого хватает. На рисунке можно посмотреть результат работы этих функций:

Сложные режимы

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

Как видите, три режима вполне реализуются через текстовые функции. Но остальные две, а именно — «Как в предложениях» и «иЗМЕНИТЬ РЕГИСТР» стандартными средствами не достичь.

У вас есть следующие варианты для того, чтобы получить желаемое:

1. Метод простой и грубый . Как говорил Принц-Полукровка в «Гарри Поттере»: «просто засуньте им в глотку безоар». А именно, копируете нужный текст, переходите в Word, там вставляете, применяете команду «Регистр», потом получившийся текст тащите обратно в Excel. Преимущества — скорость и незамысловатость. Недостаток — с большими таблицами придется повозиться, менять форматирование и т.д.
2. Использовать макрос или пользовательскую функцию. Лично я пытался реализовать через формулы массива или через комбинации функций, но получалось так громоздко и неэффективно по сравнению с найденным на сайте «Мир 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

Как ею пользоваться, по шагам:

1. Открываете Excel, нажимаете Alt+F11, откроется окно Visual Basic.
2. Команда Insert/Module.В открывшееся поле вставляете текст.
3. Переходите в Excel, теперь в списке функций, в полном алфавитном перечне у вас появилась функция ConvertRegistr.
4. У этой функции два аргумента — ячейка и режим преобразования регистра. В тексте функции есть комментарии, какой режим обозначен какой цифрой. По сути она заменяет предыдущие три функции, а работает полным аналогом уже упоминавшейся команды «Регистр». Правда, есть нюанс — 4й пункт — «Как в предложениях» — работает только для одного предложения. Если кому нужно, пишите на мэйл, скину рабочий вариант, который переработает нормальный литературный текст.
5. Если вы хотите, чтобы функция всегда была под рукой, сохраните файл как надстройку Excel в папке Addins. У меня она находится по адресу: C:UsersuserAppDataRoamingMicrosoftAddIns.
6. Активируйте надстройку через Офис/Параметры Excel/Надстройки/Перейти в надстройки. Там появится окно, где надо будет поставить галочку напротив названия файла. Также возможно прописать макрос пребразования регистра в диапазонах, это вы сможете посмотреть здесь.

Удачи в использовании!
Эксель Практик
«Глаза боятся, а руки делают»

P.S.

Источник: http://excelpractic.ru/zaglavnye-i-strochnye-bukvy-v-excel.html

Заглавные (прописные) и строчные буквы в Excel

Разберемся как изменять регистр букв в Excel, т.е. переводить заглавные (прописные) буквы в строчные и наоборот, а также другие различные нестандартные расстановки.

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

Наверняка многие из вас сталкивались с задачей изменения текста с той целью, чтобы его было удобнее воспринимать и читать, например, перевести заглавные буквы в строчные (проще говоря, большие в маленькие) или наоборот, строчные буквы в заглавные (маленькие в большие), представить текст в стандартном виде (как в предложениях), поменять регистр на противоположный и т.д.
Поэтому, несмотря на то, что Microsoft Excel в основном ориентирован на работу с числовыми данными, все-таки не стоит забывать и про возможность работы с текстовыми данными.

Вместо того, чтобы перенабирать все записи вручную или если просто лень этим заниматься, то предлагаю рассмотреть более удобные способы изменения регистра букв в Excel.
Хотя Excel в полной мере и не обладает возможностями Microsoft Word, тем не менее варианты перевода символов в верхний и нижний регистры есть, давайте подробнее остановимся на 3 основных способах:

• Применение Microsoft Word.Конвертирование данных посредством программы Microsoft Word;
• Стандартные функции Excel.Поменять регистр текста можно воспользовавшись функциями СТРОЧН (изменяет все буквы на строчные), ПРОПИСН (изменяет все символы на заглавные) и ПРОПНАЧ (изменяет первые буквы слов на заглавные, все оставшиеся — на строчные);
• Применение пользовательских функций (UDF).Если варианты со стандартным функционалом не подходят, и нужна другая расстановка прописных и строчных символов, то можно реализовать пользовательскую функцию.

Применение Microsoft Word

Так как в Microsoft Word возможность изменения регистра текста уже реализована, то можно не изобретать велосипед и для перевода заглавных букв в строчные (и наоборот) воспользоваться программой Word.

Алгоритм простой — выделяем и копируем данные из Excel, переходим в Word и вставляем их в документ.

Далее применяем опцию Регистр (располагается на вкладке Главная в блоке Шрифт), а затем возвращаем отредактированные данные обратно в Excel.

Стандартные функции Excel

Excel предлагает нам 3 стандартные функции для преобразования данных в строчные (все строчные) и прописные буквы (ВСЕ ПРОПИСНЫЕ), а также смешанную расстановку (Начинать С Прописных).
Детально изучим каждый из вариантов конвертации текстовых данных.

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

Сделать перевод всех символов в строчные можно с помощью функции СТРОЧН (английская версия LOWER) в Excel:

СТРОЧН(текст)
Делает все буквы в строке текста строчными.

• Текст (обязательный аргумент) — текст, буквы которого конвертируются в нижний регистр (т.е. в маленькие).

Применим функцию СТРОЧН, где в качестве аргумента выбираем произвольный текст, в котором мы хотим сконвертировать все символы в строчные:

Как сделать все буквы заглавными (прописными) в Excel?

Для перевода текстовых данных в большие буквы можно обратиться к функции ПРОПИСН (английская версия UPPER):

ПРОПИСН(текст)
Делает все буквы в строке текста прописными.

• Текст (обязательный аргумент) — текст, буквы которого конвертируются в верхний регистр (т.е. в большие).

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

Как сделать первую букву заглавной в Excel?

Для изменения начального символа каждого слова в тексте из строчного в заглавный можно воспользоваться функцией ПРОПНАЧ (английская версия PROPER):

ПРОПНАЧ(текст)
Начинает текстовую строку с заглавной буквы; делает прописной первую букву в каждом слове текста, преобразуя все другие в строчные.

• Текст (обязательный аргумент) — текст, в котором начальные буквы слов конвертируются в верхний регистр (т.е. в большие), все оставшиеся — в нижний (т.е. в маленькие).

Все то же самое, что и в предыдущих примерах, применяем к данным функцию ПРОПНАЧ, в котором хотим конвертировать начальные буквы слов в большие, а все оставшиеся сделать маленькими:

Из-за этого могут возникнуть ситуации, когда некоторые слова преобразуются правильно (пример алма-ата -> Алма-Ата), а некоторые некорректно (пример кое-что -> Кое-Что).

Применение пользовательских функций (UDF)

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

Давайте повторим функционал смены регистра из текстового редактора Word, в которой к 3 основным (ВСЕ ПРОПИСНЫЕ, все строчные и Начинать С Прописных) добавляются еще 2 варианта — Как в предложениях и иЗМЕНИТЬ рЕГИСТР.

Так как в общем и целом никаких ограничений на итоговый вид текстовых данных нет, то также добавим реализацию нестандартных расстановок, к примеру, ЧеРеДоВаНиЕ рЕгИсТрОв и ПрОИЗвоЛЬноЕ нАПиСАниЕ.

Переходим в редактор Visual Basic (горячая клавиша Alt + F11), добавляем новый модуль и записываем код новой функции (напротив основных действий кода даются пояснения):

Function ChangeRegister(Text As String, TextType As Integer) As String
'Тип TextType:
'1 — ВСЕ ПРОПИСНЫЕ
'2 — все строчные
'3 — Начинать С Прописных
'4 — Как в предложениях
'5 — иЗМЕНИТЬ рЕГИСТР
'6 — ЧеРеДоВаНиЕ рЕгИсТрОв
'7 — ПрОИЗвоЛЬноЕ нАПиСАниЕ
If TextType = 1 Or TextType = 2 Or TextType = 3 Then 'Если тип данных 1, 2 или 3…
ChangeRegister = StrConv(Text, TextType) '… то конвертируем встроенной функцией
ElseIf TextType = 4 Then 'Если тип данных 4
ChangeRegister = StrConv(Text, 2) 'То конвертируем в тип 2 (делаем все символы строчные)
Mid$(ChangeRegister, 1, 1) = UCase(Mid$(ChangeRegister, 1, 1)) 'Затем меняем начальный символ на заглавный
ElseIf TextType = 5 Then 'Если тип данных 5
For i = 1 To Len(Text) 'Цикл по каждому символу
Mid$(Text, i, 1) = IIf(Mid$(Text, i, 1) = UCase(Mid$(Text, i, 1)), LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Меняем регистр символа на противоположный
Next i
ChangeRegister = Text
ElseIf TextType = 6 Then 'Если тип данных 6
For i = 1 To Len(Text)
Mid$(Text, i, 1) = IIf(i Mod 2 = 0, LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Чередуем регистр
Next i
ChangeRegister = Text
Else 'В остальных случаях
For i = 1 To Len(Text)
Mid$(Text, i, 1) = IIf(Round(Rnd()) = 0, LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Произвольный регистр
Next i
ChangeRegister = Text
End If
End Function

Function ChangeRegister(Text As String, TextType As Integer) As String    '3 — Начинать С Прописных    '6 — ЧеРеДоВаНиЕ рЕгИсТрОв    '7 — ПрОИЗвоЛЬноЕ нАПиСАниЕ    If TextType = 1 Or TextType = 2 Or TextType = 3 Then 'Если тип данных 1, 2 или 3…        ChangeRegister = StrConv(Text, TextType) '… то конвертируем встроенной функцией    ElseIf TextType = 4 Then 'Если тип данных 4        ChangeRegister = StrConv(Text, 2) 'То конвертируем в тип 2 (делаем все символы строчные)        Mid$(ChangeRegister, 1, 1) = UCase(Mid$(ChangeRegister, 1, 1)) 'Затем меняем начальный символ на заглавный    ElseIf TextType = 5 Then 'Если тип данных 5        For i = 1 To Len(Text) 'Цикл по каждому символу            Mid$(Text, i, 1) = IIf(Mid$(Text, i, 1) = UCase(Mid$(Text, i, 1)), LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Меняем регистр символа на противоположный    ElseIf TextType = 6 Then 'Если тип данных 6            Mid$(Text, i, 1) = IIf(i Mod 2 = 0, LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Чередуем регистр    Else 'В остальных случаях            Mid$(Text, i, 1) = IIf(Round(Rnd()) = 0, LCase(Mid$(Text, i, 1)), UCase(Mid$(Text, i, 1))) 'Произвольный регистр

У данной функции всего 2 аргумента: ячейка с текстом и тип преобразования данных.
Переходим обратно в Excel и в строке формул набираем функцию пользователя ChangeRegister (или находим ее в категории Определенные пользователем посредством мастера функций):

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

Ознакомиться с разобранными материалами можно скачав пример.

Спасибо за внимание!
Если у вас есть мысли по теме или остались вопросы — пишите в х.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Источник: https://tutorexcel.ru/tekst/zaglavnye-propisnye-i-strochnye-bukvy-v-excel/

Как сменить регистр текста в Экселе

Здравствуйте. Сегодня работаем с регистром текста, и я сразу очерчу терминологию. Буквы  верхнего и нижнего регистра — что это? Верхний регистр — это заглавные буквы, нижний — это строчные. Как на рисунке:

Чтобы изменить регистр текста, в Excel есть 3 функции, сейчас мы их разберем на примерах.

Как сделать в Excel все буквы заглавными

Напомню, верхний регистр — это когда буквы заглавные. Чтобы перевести текст в такой формат, воспользуемся функцией =ПРОПИСН(текст). На практике получается так:

Как в Экселе сделать все буквы строчными

По аналогии, нижний регистр — это когда символы строчные. Для изменения строки в строчный формат, используется функция =СТРОЧН(текст):

Как сделать первые буквы заглавными

Вы можете сделать, чтобы каждое слово начиналось с заглавной буквы, а остальные символы были строчными. Для этого используется функция =ПРОПНАЧ(текст). Вот, как это выглядит в работе:

Эта функция концом слова считает любые знаки пунктуации, непечатаемые символы. Например, формула =ПРОПНАЧ(“алма-ата”) вернёт правильный результат – «Алма-Ата». А вот =ПРОПНАЧ(“кое-что”) вернет строку «Кое-Что». Далеко не всегда нам нужно именно это.

Из приведенной выше нотации следует, что функция ПРОПНАЧ идеально работает лишь с именами и некоторыми географическими названиями. А следующий бонус призван эту ситуацию исправить.

Как сделать первый символ заглавным

Сейчас задача стоит сделать первый символ в ячейке — верхнего регистра, а остальные — нижнего. Для выполнения, скомбинируем формулу из двух функций: =ПРОПИСН(ПСТР(текст;1;1)) & СТРОЧН(ПСТР(текст;2;ДЛСТР(B2)-1)).

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

А результат такой:

Вот и всё об изменении регистра текста. Готов ответить на ваши вопросы, пишите свои комментарии!

В следующем посте я расскажу, как извлечь часть текста в Excel.

Источник: https://officelegko.com/2016/01/03/kak-izmenit-registr-teksta-funktsii-propisn-strochn-propnach/

Excel — функции для изменения строчных в заглавных букв

При настройке компании для Яндекс директа я часто пользуюсь Microsoft Excel и если в Вашем арсенале есть знания некоторых приемом один из которых как сделать первую букву заглавную в объявлении или сделать все строчные буквы или все заглавные вы можете сэкономить очень большое кол-во времени при работе с директом, а время то Деньги!

Сегодня я Вам хочу рассказать как в Microsoft Excel сделать след приемы:

1. как сделать первую заглавную букву Большой
2. Как сделать все буквы ПРОПИСНЫМИ
3. как делать все буквы строчными

Excel — первая буква Заглавная

Данный вопрос имеет два варианта решения

1. Только первая буква в тексте будет заглавная
2. Большая буква каждого нового слова

Как видите ситуацию бывают разные поэтому нам надо рассмотреть все варианты решения данных вопросов поэтому будет разбирать по максимум!

Только «Первая» буква заглавная

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

Для этого нам поможет формула:

=ЗАМЕНИТЬ(D4;1;1;ПРОПИСН(ЛЕВСИМВ(D4;1)))

описание формулы:

• заменить — это функция синтаксис который выглядит так: ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)Стар_текст — Текст, в котором требуется сделать заменуНачальная_позиция — Координата символа в Стар_текст с которого начнется заменаЧисло_знаков — Число символов от Начальная_позиция которые нужно заменитьНов_текст — Текст который заменяем
• D4 — старый текст
• 1 — начальная позиция (первая буква)
• 1 — число знаков отчета (первая буква)
• ПРОПИСН — функция которая делает букву прописной

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

excel — сделать первую букву прописной

Excel — каждое слово с большой буквы

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

=ПРОПНАЧ(D4)

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

• ПРОПНАЧ — функция первая буква в строке текста и все первые буквы, следующие за знаками, отличными от букв, преобразуются в прописные (верхний регистр). Все прочие буквы в тексте преобразуются в строчные (нижний регистр).
• D4 — координата текста

excel — преобразование каждого слова с большой буквы

Excel — все буквы ЗАГЛАВНЫЕ

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

=ПРОПИСН(D4)

• ПРОПИСН — функция которая преобразует каждый символ в верхний регистр
• D4 — координата с текстом

Я особо не знаю где может пригодиться данная трансформация текста в, но главное не пишите такими слова на форумах)) потому как текста с большой буквы модераторы банят)  ну а если серьезно, то переходим к последней функции

Excel — все слова с большой буквы

Excel — весь с маленькой буквы

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

=СТРОЧН(D4)

• СТРОЧН — функция которая преобразуемый все символы в нижний регистр
• D4 — координаты текста

excel сделать весь текст с маленькой буквы

Источник: https://www.nibbl.ru/office/excel-funkcii-dlya-izmeneniya-strochnyx-v-zaglavnyx-bukv.html

Заглавные и строчные буквы в Excel

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

Текстовые функции

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

Как преобразовать строчные буквы в прописные?

Как сделать все буквы прописными

При помощи стандартной функции Excel «ПРОПИСН» можно все буквы в строке перевести в верхний регистр (сделать заглавными).

Как Сделать Все Первые Буквы Прописными

Стандартная функция Excel «ПРОПНАЧ» изменяет первые буквы в каждом слове со строчных на заглавные.

Как сделать первые буквы прописными

Комбинация встроенных в Excel функций «ПРОПИСН», «СЦЕПИТЬ», «ЛЕВСИМВ», «ПРАВСИМВ» и «ДЛСТР» позволяет привести только первые буквы к верхнему регистру.

Итоговая формула имеет вид «=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(B2));ПРАВСИМВ(B2;ДЛСТР(B2)-1))», где B2 – это адрес ячейки с текстовым значением.

Как превратить заглавные буквы в строчные?

как сделать все буквы строчными

«СТРОЧН» из набора встроенных текстовых функций конвертирует все буквы в строчные.

кАК СДЕЛАТЬ ПЕРВЫЕ БУКВЫ СТРОЧНЫМИ

• В некоторых случаях необходимо сделать строчной только первую букву.
• «=СЦЕПИТЬ(СТРОЧН(ЛЕВСИМВ(B2));ПРАВСИМВ(B2;ДЛСТР(B2)-1))», где B2 – это адрес ячейки с исходным значением.
• Использование сочетаний функций требует затрат времени, делает формулы громоздкими, снижает их восприятие и эффективность использования.

Пользовательские функции

Как изменить регистр букв на противоположный?

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

Результат, который сложно получить стандартными функциями Excel, достаточно легко может быть достигнут при помощи функций пользовательских. Ниже приведен листинг пользовательской функции, написанной на Visual Basic for Application.

Эту функцию можно использовать, скопировав программный код в личную книгу макросов. Также, можно бесплатно скачать надстройку и установить её по инструкции. После установки надстройки функция будет добавлена в раздел «Пользовательские функции».

Готовое решение для быстрых преобразований текста

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

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

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

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

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

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

Источник: http://macros-vba.ru/nadstrojki/excel/291-zaglavnye-i-strochnye-bukvy-v-excel

Как решить задачу в Excel «Первая буква заглавная»

Многиe задаются вопросом: как в Excel сдeлать пeрвую букву заглавной? Такая проблeма, дeйствитeльно, встрeчаeтся очeнь часто. Напримeр, пользоватeли рабочeй книги нeаккуратно вносят данныe, занося фамилию с малeнькой буквы, допуская лишниe пробeлы, описки, нeточности, что приводит к тому, что eдинообразия в итогe сложно добиться.

Данная статья посвящeна тому, какиe срeдства в табличном рeдакторe Excel от компании «Майкрософт» прeдусмотрeны разработчиком, чтобы управлять заглавными и прописными буквами по своeму усмотрeнию.

Возможности Excel

В программe Excel сущeствуют слeдующиe готовыe функции по работe со строками:

• =ПРОПИСН(тeкст или ячeйка с тeкстом) – пeрeводит вeсь ввeдeнный тeкст (или тeкст из выбранной ячeйки) к заглавному виду;
• =СТРОЧН(тeкст или ячeйка с тeкстом) – пeрeводит вeсь ввeдeнный тeкст (или тeкст из выбранной ячeйки) к строчному виду;
• =ПРОПНАЧ(тeкст или ячeйка с тeкстом) – эта функция сдeлаeт прописной пeрвую букву каждого слова (раздeлeнного пробeлом) тeкста, а остальныe буквы сдeлаeт малeнькими.

Выполнить задачу в Excel «Пeрвая буква заглавная», как мы видим, нe можeт ни одна из прeдложeнных функций. Для этого мы воспользуeмся комбинациeй готовых функций, но рассмотрим это чуть нижe. Сначала проанализируeм, как сдeлать в Excel заглавныe и прописныe буквы на основe вышeуказанных функций.

Работа с функциями ПРОПИСН, СТРОЧН и ПРОПНАЧ

• Чтобы сразу провeрить рeзультат работы всeх трeх функций, ввeдeм в ячeйку А2 рабочeго листа Excel простую строку «яблокИ антоновка свeжиe» (спeциально допустив лишнюю прописную букву для провeрки рeзультата работы). В сосeдний столбeц ввeдитe слeдующиe формулы и сравнитe рeзультат:
• Тeкст
• Рeзультат
• Функция
• яблокИ антоновка свeжиe
• ЯБЛОКИ АНТОНОВКА СВЕЖИЕ
• =ПРОПИСН(A2)
• яблоки антоновка свeжиe
• =СТРОЧН(A2)
• Яблоки Антоновка Свeжиe
• =ПРОПНАЧ(A2)
• Как видим, всe функции работают согласно описанию.

Согласитeсь, было бы удобно, чтобы в Excel пeрвая буква заглавная становилась таковой тожe простым выбором одной функции, но, к сожалeнию, это нeвозможно. Придeтся создать самим нужную формулу… Выдeлим для этого отдeльный раздeл.

Excel: пeрвая буква заглавная

Чтобы рeшить поставлeнную задачу, надо логичeским образом разобрать ee на отдeльныe этапы. В данном случаe можно выдeлить слeдующиe:

Выдeлить пeрвую букву тeкста. При этом нeобходимо учeсть, что зачастую пользоватeли вносят лишниe пробeлы пeрeд началом ввода тeкста. Удалить лишниe пробeлы нам поможeт функция СЖПРОБЕЛЫ(тeкст), а выдeлить пeрвую букву – функция ЛЕВСИМВ(тeкст;[количeство знаков]) со вторым аргумeнтом, равным 1 (он выбираeтся по умолчанию, поэтому можно выпустить).

Выдeлeнную пeрвую букву с помощью рассмотрeнной в прeдыдущeм раздeлe функции ПРОПИСН пeрeвeсти в заглавный вид.

Раз нам нeобходимо выполнить задачу в Excel «Пeрвая буква заглавная», то это подразумeваeт, что остальной тeкст будeт прописным. Как бы то ни было, сeйчас нам нужно присоeдинить к пeрвой буквe оставшийся тeкст. Сдeлать это нам поможeт функция ПРАВСИМВ(тeкст; [количeство знаков]), выдeляющая с конца тeкста нужноe количeство знаков. А какоe, собствeнно говоря, нужноe? Нужно столько, сколько составляeт длина строки минус пeрвый символ, то eсть -1. Выполнив данныe дeйствия, нeобходимо рeзультат привeсти к прописному виду.

Осталось соeдинить рeзультаты выполнeния второго и трeтьeго пункта с помощью ампeрсанда (&).

1. Всю эту послeдоватeльность зафиксируeм в таблицe, для наглядности внeсeм пeрeд тeкстом «яблокИ антоновка свeжиe» нeсколько лишних пробeлов.
2. Тeкст
3. Рeзультат
4. Функция
5. яблокИ антоновка свeжиe
6. яблокИ антоновка свeжиe
7. =СЖПРОБЕЛЫ(A2)
8. я
9. =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2))
10. Я
11. =ПРОПИСН(ЛЕВСИМВ(СЖПРОБЕЛЫ(A2)))
12. 22
13. =ДЛСТР(СЖПРОБЕЛЫ(A2))-1
14. блокИ антоновка свeжиe
15. =ПРАВСИМВ(СЖПРОБЕЛЫ(A2); ДЛСТР(СЖПРОБЕЛЫ(A2))-1)
16. блоки антоновка свeжиe
17. =СТРОЧН(ПРАВСИМВ(СЖПРОБЕЛЫ(A2); ДЛСТР(СЖПРОБЕЛЫ(A2))-1))
18. Яблоки антоновка свeжиe
19. =ПРОПИСН(ЛЕВСИМВ(СЖПРОБЕЛЫ(A2))) &
20. СТРОЧН(ПРАВСИМВ(СЖПРОБЕЛЫ(A2); ДЛСТР(СЖПРОБЕЛЫ(A2))-1))
21. Как видим, рeшить задачу в Excel «Пeрвая буква заглавная» оказалось нe так и сложно, главноe, прeдставить всю логичeскую послeдоватeльность этапов.

Источник: https://xroom.su/kak-reshit-zadachy-v-excel-pervaia-bykva-zaglavnaia/

Подсчет ТЕКСТовых значений с учетом РЕгиСТра в MS EXCEL

Буквы могут находиться в ВЕРХНЕМ и нижнем регистре (ПРОПИСНЫЕ и строчные). Текстовые строки, соответственно, могут состоять целиком из строчных или ПРОПИСНЫХ букв, а также состоять из букв находящихся в разном РЕгиСТре (например, ЗагоЛовок). Научимся подсчитывать значения с учетом их регистра.

Подсчет текстовых значений с учетом регистра разобьем на несколько частных случаев:

• подсчет значений из букв в ВЕРХНЕМ регистре;
• подсчет значений из букв в нижнем регистре;
• подсчет значений с первой Заглавной буквой;
• подсчет значений из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой);
• подсчет значений по критерию.

Функции, учитывающие регистр: НАЙТИ(), СОВПАД(), СТРОЧН(), ПРОПИСН(), ПРОПНАЧ(), ПОДСТАВИТЬ(). В принципе, еще может помочь функция КОДСИМВ(), возвращающая код символа, т.к. код ПРОПИСНЫХ символов всегда меньше кода строчных (КОДСИМВ(«Б»)=193, а КОДСИМВ(«б»)=225) и функция СИМВОЛ().

Пусть в исходном списке (диапазон А2:А16) содержатся слова, состоящие из букв в различных регистрах. Исходный список не должен содержать чисел, пустых ячеек и ошибок. (См. файл примера).

Подсчет значений из букв в ВЕРХНЕМ регистре

Для подсчета значений из букв в ВЕРХНЕМ регистре используем формулу =СУММПРОИЗВ(—(СОВПАД(A2:A16;ПРОПИСН(A2:A16))))

Функция СОВПАД() возвращает результат ИСТИНА, если 2 значения совпадают, причем с учетом регистра. В нашем случае каждое значение, начиная с А2 до А16, сравнивается со своей прописной версией.

Как и следовало ожидать, только 3 значения (ЯБЛОКИ) находятся в ВЕРХНЕМ регистре. Применяя операцию двойного отрицания переводим этот массив в форму {0:0:0:0:0:0:1:1:1:0:0:0:0:0:0}. Далее функция СУММПРОИЗВ() возвратит сумму элементов массива, т.е. 3.

Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(ПРОПИСН(A2:A16);A2:A16);1;0)) После окончания редактирования формулы в ячеке, нужно вместо ENTER нажать CTRL+SHIFT+ENTER.

В принципе, можно было обойтись и без формулы массива, но тогда необходимо было бы создать вспомогательный столбец рядом с исходным, записав в каждую ячейку формулу =ЕСЛИ(СОВПАД(ПРОПИСН(A2);A2);1;0), и просуммировать результат.

Подсчет значений из букв в нижнем регистре

• Для подсчета значений из букв в нижнем регистре используем формулу
• =СУММПРОИЗВ(—(СОВПАД($A$2:$A$16;СТРОЧН($A$2:$A$16))))
• Как видно, она практически аналогична формуле предыдущего случая: только функция ПРОПИСН() заменена на СТРОЧН().
• Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений с первой Заглавной буквой

1. Для подсчета значений с первой Заглавной буквой используем формулу
2. =СУММПРОИЗВ(—(СОВПАД($A$2:$A$16;ПРОПНАЧ($A$2:$A$16))))
3. Другим вариантом подсчета является формула массива =СУММ(ЕСЛИ(СОВПАД(СТРОЧН($A$2:$A$16);$A$2:$A$16);1;0))

Подсчет значений, состоящих из букв в РаЗныХ регистрах (включая значения с первой заглавной буквой)

• В этом случае подсчет становится немногим более сложным.
• =СУММПРОИЗВ(—(НЕ(СОВПАД($A$2:$A$16;ПРОПИСН($A$2:$A$16))));—(НЕ(СОВПАД($A$2:$A$16;СТРОЧН($A$2:$A$16)))))
• Выделив два аргумента функции СУММПРОИЗВ() и нажав клавишу F9 получим два массива: {1:1:1:1:1:1:0:0:0:1:1:1:1:1:1} – все значения, кроме ПРОПИСНЫХ
• {0:0:1:1:1:1:1:1:1:1:1:1:1:1:1} – все значения, кроме строчных

Источник: https://excel2.ru/articles/podschet-tekstovyh-znacheniy-s-uchetom-registra-v-ms-excel

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

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

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

Речь здесь уже идет о преобразовании данных бухгалтерского учета в данные управленческого учета.

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

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

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

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

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

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

• 
• Если в таблице находиться десять операций, то проще проставить признак вручную в соседнем столбце, однако если записей несколько тысяч, то это уже проблематично.
• Вся трудность, в том чтобы извлечь код из текстовой строки.
• Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.
• В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.
• =ЛЕВСИМВ(текст; [количество_знаков])
• =ПРАВСИМВ(текст; [количество_знаков])
• Где:

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

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

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

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

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

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

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

Где:

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

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

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

1. Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.
2. В нашем случае формула будет иметь вид:
3. =ПСТР(С7;29;2)
4. Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

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

• Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
•  =ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])
• Где:

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

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

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

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

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

1. Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:
2. =ПСТР(C7;ПОИСК(«?Ф00000»;C7);2)
3. Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.
4. В результате, мы получим в отдельном столбце код филиала, который сможем использовать как признак для фильтрации, сортировки или создания сводной таблицы.
5. 

Источник: http://excel-training.ru/kak-vyitashhit-chislo-ili-chast-teksta-iz/

Как сделать первую букву заглавной в Excel

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

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

И я в этом вам помогу.

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

Итак, давайте приступим. За основу я возьму ячейку А1.

Что значит: первая буква заглавная, а остальные строчные? Это значит, что нам нужно сообщить Excel, чтобы он взял первую в ячейке, сделал ее прописной, а затем добавил к результату текст из ячейки в строчном варианте, но при этом отнял первый символ.

Для нахождения первого символа в ячейке будем использовать функцию ЛЕВСИМВ, которая возьмет только первую ЛЕВУЮ букву в указанной нами ячейки. И к этому символу мы применим функцию ПРОПИСН, что сделает ее Заглавной. Вот первая часть формулы: ПРОПИСН(ЛЕВСИМВ(A1))

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

Хорошо, два куска формулы у нас есть. Теперь нужно их объединить. Воспользоваться специальный символ & (SHIFT + 7). В результате формула выглядит вот так:
=ПРОПИСН(ЛЕВСИМВ(A1))&ПРАВСИМВ(СТРОЧН(A1);ДЛСТР(A1)-1)

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

Смотрите мой результат ниже.

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

На этом все. Надеюсь вам помогут полученные знания. Не забудьте прокомментировать.

Источник: https://v-ofice.ru/excel/pervaya-bukva-zaglavnaya-excel/