Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как зашифровать буквы в словах в Excel?

Цифры буквами в Excel

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

Цифры прописью в Excel

Для редактирования чисел в Excel вам понадобится дополнительная надстройка, но не паникуйте, если вы ничего не понимаете. Просто прочитайте и следуйте инструкциям, и все будет в порядке.

Интересно. Посмотрите, как это можно сделать в Word.

Число прописью в Экселе. Установка

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

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

Хватит о буквах, давайте приступим к работе и подробно рассмотрим, как написать число прописными буквами.

Делайте это в следующем порядке:

1. Скачайте и распакуйте файл, нажав здесь.

Поместите файл NUMSTR.XLA в папку Library, которая находится в каталоге C:ProgramFiles (x86)MicrosoftOfficeOffice14Library (расположение может немного отличаться в зависимости от версии программного обеспечения).

3. Запустите Excel и нажмите кнопку «Надстройки» на вкладке «Разработчик» (если ее нет, читайте ниже).

Цифры буквами в Excel

Внимание. В вашей системе может не быть вкладки «Разработчик», поэтому перейдите в меню Файл → Параметры → Параметры ленты и установите флажок «Разработчик».

Цифры прописью в Excel

4. Необходимо установить флажок NumStrAdd-in в окне Add-In.

Excel число прописью

Теперь все готово к работе.

Число прописью в Excel. Как пользоваться

Расширение установлено, а теперь несколько слов о том, как его использовать:

5. Просто напишите цифру в любой ячейке.

6. Нажмите на кнопку «Вставить функцию».

Число прописью в Экселе

В открывшемся окне «Мастер функций» выберите «Пользовательский» из списка «Категория».

8. Затем выберите нужную функцию:

NumStr — позволяет отображать числа в виде букв в Excel;

Конвертирует число в рубли;

USDStr — Конвертирует количество слов в доллары;

Сумма выводится словами в евро — E URStr.

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

Число буквами Excel

Интересно. Как закрепить строки в Excel читайте здесь.

Как оставить в ячейке только цифры или только текст?

Вот что происходит: у вас есть текст в ячейке. Допустим, «было доставлено 763 куска мыла». Вам нужно всего 763, поэтому вы можете произвести некоторые расчеты. Если есть только одна клетка, нет проблем, но если есть две тысячи клеток? И все они разные?

  • Было доставлено 763 куска мыла.
  • Всего 34 трубки
  • — 54 поставленных
  • И т.д.

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

Читайте так же:
Как найти значение для возврата нескольких соответствующих значений в Excel?

СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:

  1. Формула вводится в ячейку с помощью комбинации клавиш Ctrl+Shift+Enter, так как это табличная формула. Чтобы узнать больше об этих формулах, см. статью: Что такое формула массива
  2. В этой форме формула работает с текстом, длина которого не превышает 99 символов. Чтобы расширить его, замените STRING( $1:$99 ) на STRING( $1:$200 ) во всей формуле. То есть вместо 99 нужно указать количество символов с отступом. Однако не увлекайтесь, иначе формула может работать слишком долго
  3. Формула не сможет правильно обработать текст «Мы доставили 763 куска мыла, но заказали 780» и т.п., где числа разбросаны по всему тексту.

Теперь рассмотрим формулу на примере: Было доставлено 763 бруска мыла.

  • в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
  • блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))
    вычисляет позицию первой цифры в ячейке — 29
  • блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))
    вычисляет позицию последней цифры в ячейке — 31
  • в результате получается: =ПСТР( A1 ;29;3129+1)
    функция ПСТР извлекает из текста, указанного первым аргументом( A1 ) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(3129+1)
  • И в итоге:
    =ПСТР( A1 ;29;3129+1)
    => =ПСТР( A1 ;29;2+1)
    => =ПСТР( A1 ;29;3)
    => 763

СПОСОБ 2: используем макросы
Самый главный недостаток метода при помощи формулы, приведенной выше — из текста » Было доставлено кусков мыла 763шт., а заказывали 780 » формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780 .
Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот — цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции, которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.

Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer) ‘sWord = ссылка на ячейку или непосредственно текст ‘Metod = 0 – числа ‘Metod = 1 – текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len(sWord) sSymbol = Mid(sWord, i, 1) If Metod = 1 Then If Not LCase(sSymbol) Like "*[0-9]*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i — 1, 1) Like "*[0-9]*" And Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(sWord, i — 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_Number_from_Text = sInsertWord End Function

Читайте так же:
Как затенять или раскрашивать пустые или непустые ячейки в Excel?

Данный код необходимо поместить в стандартный модуль книги. После этого в мастере функций в категории Определенные пользователем (User Defined) будет доступна функция Extract_Number_from_Text , которую можно будет применять как обычную функцию на листе.
Для извлечения только чисел
=Extract_Number_from_Text( A1 ; 0)
или
=Extract_Number_from_Text( A1 )
Для извлечения только текста
=Extract_Number_from_Text( A1 ; 1)

Подробнее о создании определяемых пользователем функций и их использовании читайте в статье Что такое определяемая пользователем функция (UDF)?

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

Выбор ячейки или диапазона с текстом (Sheet1 ! $A$2:$A$10 ) — Здесь указывается диапазон с исходными значениями, из которого нужно сохранить только цифры или только текст.

Вывести данные в соответствующую ячейку (Sheet1! $A$2 ) — указывается одна ячейка, с которой следует начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку диапазона с текстом (источник), если вы хотите внести изменения сразу в несколько ячеек (как на рисунке). Пожалуйста, будьте осторожны с этим указанием, так как результат работы кода может оказаться не таким, как вы ожидали, и вы не сможете восстановить прежние данные, если не закроете файл без сохранения.

Храните только цифры, храните только текст — думаю, не нужно объяснять. Здесь вы выбираете, что оставить в результате.

Небольшое дополнение к использованию кода
В коде есть строка:

If LCase(sSymbol) Like "*[0-9.,;:-]*" Then

Он отвечает за содержание текстовых символов, которые могут встречаться внутри чисел и должны быть оставлены здесь (а не удалены вместе с другими нечисловыми символами). Соответственно, если некоторые из этих символов не нужны в окончательном тексте — их нужно просто удалить. Чтобы остались только числа (без запятых, например). ):

If LCase(sSymbol) Like "*[0-9]*" Then

Если вы хотите исключить из удаления точку (т.е. цифры и точка будут извлечены) в дополнение к числам :

If LCase(sSymbol) Like "*[0-9.]*" Then

и т.д.
Скачать пример:

Из текста в xls (99,0 KiB, 16 798 скачиваний)

Была ли статья полезной? Поделитесь ссылкой со своими друзьями.

Видеоуроки

  • Создание списка дат на основании даты начала и окончания
  • Использование относительных ссылок в макросах
  • Перейти к указанной строке в Power Query
  • Предыдущая строка в Power Qwery
  • Создание списка дат на основании даты начала и окончания
  • Использование относительных ссылок в макросах
  • Перейти к указанной строке в Power Query
  • Предыдущая строка в Power Qwery
  • Найти среди чисел только те, которые составляют указанную сумму
  • Вычисляемые поля в сводных таблицах
  • Объединение и суммирование файлов через PowerQuery
  • ВПР с поиском по нескольким листам
  • Основы работы с макросами
  • Как ускорить и оптимизировать код VBA
  • Поиск и замена данных на листах Excel
  • Основы работы со сводными таблицами в Excel
  • Группировка данных в сводных таблицах
  • Защита листов и ячеек в Excel
  • Списки автозаполнения в MS Excel
  • План-фактный анализ в Excel при помощи PowerQuery
  • Данные из Google Sheets при помощи Power Query
  • Интерактивный(живой) график в MS Excel
  • Данные из файлов XML при помощи Power Query
  • Простой прогноз продаж в Excel с учетом сезонности
Поиск по меткам

Здравствуйте скрипт при обработке ячейки:
Пр-ка ТНВД 5301 (пер.) 50-1006315-Б2 выдает: 530150-1006315-2
вопрос есть возможность что бы он брал только последних 15-20 сим.
с заглавными А-Я, или хотя бы цифирки.
Буду признателен за предложения.
Возможно есть проще варианты(12000 стр.записеи) обрезани до последних 10-20сим.

Читайте так же:
Как найти и заменить текст в комментариях в Excel?

Александр, последние 15-20 символов можно взять при помощи функции ПРАВСИМВ(RIGHT). Так же можно совместить:
=Extract_Number_from_Text(ПРАВСИМВ(A1))
Более сложные вариации извлечения делаются под конкретные данные с учетом различных нюансов.

Добрый день!
Спасибо за замечательный код! Подскажите пожалуйста, как оставить среди неудаляемых символов пробел?
Например, в ячейке было «Т-образный поворот SPB-RF60 TE-200 SS316L».
Ваш код оставляет (после некоторой модификации) «60200316».
А хотелось бы «60 200 316».
Я не понимаю как вписать пробел в строку
Like «*[0-9.,;:-]*» Then

Это место должно было быть в конце ряда, а не в середине.

Здравствуйте.
Использовал функцию, вызванную на VBA, все нормально, при вызове мастера функций он нормально отрабатывает
Но при выполнение в эксель, выскакивает сообщение, что здесь используется циклическая ссылка и потом появляется значение 0. В чем дело?

Добрый день!
А что надо добавить в код/формулу чтобы из строки
«ТП ВЕТЧИННАЯ ВАРЕНАЯ 400Г (162854) /Ш/»
— вытащить только цифры до буквы Г?
— или без содержимого скобок
Заранее большое спасибо!

Нашла пока только такой вариант решения:
=ЛЕВСИМВ(B5;ПОИСК(«г «;B5)-1)
Затем в другой колонке
=ЕСЛИОШИБКА(ПСТР(C5;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(C5;СТРОКА($1:$99);1));СТРОКА($1:$99)));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(C5;СТРОКА($1:$99);1));СТРОКА($1:$99))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(C5;СТРОКА($1:$99);1));СТРОКА($1:$99)))+1);1000)
И в третьей колонке = из предыдущей и преобразовать в числовой формат макросом
Результат достигнут, но если расскажите как можно было сделать изящнее — обязательно запомню))

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test(Text) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item — 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function

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

Денис, спасибо. Но есть все равно пара замечаний, раз уж предлагаете воспользоваться функцией. Неплохо было бы добавить и пример применения. Иначе как тому, кто зайдет почитать использовать Ваше решение?
Я умею использовать регулярки и по коду вижу, что помимо текста надо указать не только шаблон, но еще и номер элемента, который получить. Что является немаловажным замечанием.
Для извлечения первого числа(без разделения групп разрядов) — =(RegExpExtract( A1 ;»d»;1)
Для извлечения первого текста(что не очень удобно, если чисел в тексте много) — =(RegExpExtract( A1 ;»D»;1)
Оба варианта слегка халтурны, но для понимания общего смысла использования подойдут.
В общем и целом я бы в функцию еще параметр IgnoreCase добавил(при извлечении текста по шаблону может потребоваться). Да и Multiline тоже иногда может играть роль(зависит от шаблона и текста), поэтому его тоже лучше ставить в True сразу в случае с такими функциями.

Читайте так же:
Как найти и заменить все пустые ячейки на определенное число или текст в Excel?

Как написать первую букву строки с заглавной буквы в Excel (с помощью формулы и VBA)

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

При работе с текстовыми данными распространенной задачей является приведение данных к единообразию путем выделения заглавными буквами первой буквы каждой ячейки (или заглавными буквами каждого слова во всех ячейках).

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

Сделать первую букву заглавной, используя формулу

Если вы хотите получить выгоду, вы можете выбрать один из двух сценариев:

  • Первая буква каждого слова
  • Только первая буква первого слова.

Делайте первую букву каждого слова заглавной

Поскольку в Excel есть функция, позволяющая это сделать, это довольно легко осуществить.

Функция PROPER , цель существования которой — использовать первую букву каждого слова с заглавной буквы.

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

Ниже приведена формула, которую вы можете использовать:

Это сделает первую букву каждого слова в указанной ячейке заглавной.

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

Используйте заглавные буквы только в первой букве первого слова

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

Однако вы все еще можете (легко) сделать это с помощью комбинаций формул.

Может быть два сценария, в которых вы захотите это сделать:

  • Напишите первую букву первого слова и оставьте ее без изменений
  • Напишите первую букву первого слова и измените остальные буквы на строчные (так как некоторые буквы могут быть прописными)

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

Рассмотрим, как это сделать.

Сделайте первую букву первого слова с заглавной буквы и оставьте все как есть

Предположим, у вас есть набор данных, показанный ниже, и вы хотите использовать только первую букву (а остальные оставить без изменений).

Ниже приведена формула, которая сделает это:

В приведенной выше формуле функция LEFT используется для извлечения первого символа из строки в ячейке. Затем он использует функцию UPPER, чтобы изменить регистр первой буквы на верхний. Затем он объединяет остальную часть строки (которая извлекается с помощью функции RIGHT).

Читайте так же:
Как извлечь данные из других книг или текстовых файлов в Excel?

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

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

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

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

Предположим, что у вас есть следующий набор данных:

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

Позвольте мне объяснить, как работает эта формула:

  • LOWER (A2) — переводит весь текст в нижний регистр.
  • UPPER (LEFT (A2,1) — преобразует первую букву текстовой строки в ячейке в верхний регистр.
  • Функция REPLACE используется только для замены первого символа его версией в верхнем регистре.

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

Сделать первую букву заглавной с помощью VBA

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

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

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

Вы можете даже создать надстройку и использовать код VBA во всех своих книгах (и даже поделиться им с коллегами).

Теперь позвольте мне предоставить вам некоторые коды VBA.

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

А ниже приведен код, в котором первая буква текста будет заглавной, а все остальное — строчными:

Вы должны поместить этот код в обычный модуль в редакторе Visual Basic.

Капитализировать ячейки Excel можно с помощью следующих методов. В зависимости от вашего сценария можно использовать два метода: формулу или VBA.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector