Преобразование числа в текст и обратно в microsoft excel

VBA Convert Integer (Number) to String

We have already covered an introduction to string functions in our VBA Strings and Substrings Functions tutorial. In this tutorial, we are going to look at how to convert an integer to a string (click here to learn about converting Strings to Numbers). The reason you would want to convert a number or date to a string is in order to use string manipulation functions on these values.

The VBA CStr Function

The VBA CStr Function allows you to convert a number, date or boolean data type to a string.

The syntax of the CStr Function is:

CStr(expression) where expression is the number or date that you want to convert.

The following code shows you how numbers are outputted compared to text, including by using the CStr Function.

Both CStr(8) and the word Text are displayed as text and are left aligned whereas the two numbers are right aligned within the immediate window.

Как изменить или преобразовать число в текст в Excel?

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

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

Преобразование числа в текст с помощью команды «Формат ячеек»

Преобразование числа в текст с помощью Kutools for Excel

Конвертировать или указывать номера на английскую или китайскую валюту:Kutools for Excel’s Числа заклинаний функция, вы можете указать числа на английские и китайские слова в Excel. Например, это может означать “12.75”К”Двенадцать долларов и семьдесят пять центов”.

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

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

Если вы просто хотите преобразовать число в текст без форматирования, вы можете использовать формулу: = ТЕКСТ (A1, “0”);

1, В ячейке E1, пожалуйста, введите формулу= ТЕКСТ (A1, “0”).

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

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

Если вы хотите отобразить начальный ноль в цифрах, вы можете использовать эту формулу: = ТЕКСТ (A1, “00000”); такие как конвертировать 23 в 00023.

Как указано выше, при применении этой формулы вы получите следующий результат:

И если вы хотите преобразовать числа в текст, который содержит несколько десятичных цифр, вы можете использовать эту формулу: = ТЕКСТ (A1, “0.000”), Такие как конвертировать 23 в 23.000.

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

Внимание: вы можете определить стиль текста. Например, если вы хотите преобразовать числа в текст с форматом номера телефона, вы можете заменить «000» на «00-00000» или другие

Преобразование числа в текст с помощью команды «Формат ячеек»

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

1, Выберите номера, которые вы хотите преобразовать в текст.

2, Щелкните правой кнопкой мыши выбранный диапазон и выберите Формат ячейкиs из контекстного меню. Смотрите скриншот:

3. В Формат ячеек в диалоговом окне выберите Текст в Категория в поле под Число и нажмите OK кнопка. Смотрите скриншот:

4, Затем числа в выбранном диапазоне преобразуются в текст.

Преобразование числа в текст с помощью Kutools for Excel

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

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

1, Выберите диапазон с номерами, которые вы хотите изменить на текст.

2. Щелчок Kutools > >Преобразование между текстом и номером, Смотрите скриншот:

3. В Преобразование между текстом и номером диалоговое окно, проверьте Номер для текста и нажмите OK or применять кнопка. И все номера были преобразованы в текст в исходном диапазоне. Смотрите скриншот:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию.

Связанная статья:

Как изменить или преобразовать текст в число в Excel?

Office Tab

Принесите удобные вкладки в Excel и другое программное обеспечение Office, как Chrome, Firefox и новый Internet Explorer.

Kutools for Excel

Удивительно! Увеличьте производительность в течение 5 минут. Не нужно никаких специальных навыков, сэкономить два часа каждый день!

Новые возможности 300 для Excel, сделать Excel намного проще и мощнее:

• Объединить ячейки / строки / столбцы без потери данных.
• Объединение и объединение нескольких листов и книг.
• Сравнение диапазонов, копирование нескольких диапазонов, преобразование текста в дату, преобразование единиц и валют.
• Подсчет количества цветов, Пейджинговые субтитры, Расширенный сортировка и Суперфильтр,

Инструмент «текст по столбцам».

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

1. Выберите позиции (можно и весь столбец), которые вы хотите конвертировать, и убедитесь, что их формат установлен на Общий.
2. Перейдите на вкладку «Данные», группу «Инструменты данных» и нажмите кнопку «Текст по столбцам» .
3. На шаге 1 мастера распределения выберите «Сразделителями» в разделе «Формат исходных данных и сразу чтобы завершить преобразование, нажмите «Готово» .

Это все, что нужно сделать!

Как увеличить шрифт при печати на принтере?

Return types

The function name determines the return type as shown in the following:

Function	Return type	Range for expression argument
CBool	Boolean	Any valid string or numeric expression.
CByte	Byte	0 to 255.
CCur	Currency	-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDate	Date	Any valid date expression.
CDbl	Double	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec	Decimal	79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is 7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
CInt	Integer	-32,768 to 32,767; fractions are rounded.
CLng	Long	-2,147,483,648 to 2,147,483,647; fractions are rounded.
CLngLng	LongLong	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; fractions are rounded. (Valid on 64-bit platforms only.)
CLngPtr	LongPtr	-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems; fractions are rounded for 32-bit and 64-bit systems.
CSng	Single	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr	String	Returns for CStr depend on the expression argument.
CVar	Variant	Same range as Double for numerics. Same range as String for non-numerics.

Инструмент «Текст столбцами»

Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.

Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).

Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение

Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами

Именованные выражения форматов

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

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

Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.
Если копировать информацию из иных источников, то нередко вместе с полезной информацией переносится куча мусора, от которого неплохо было бы избавиться. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Microsoft Excel обычно довольно хорошо распознает данные ячейки. Во многих случаях это будет форматирование ячеек, содержащих текст в виде текста, числа в виде чисел, даты в качестве дат — вы поймете, как это сделать. Однако это не всегда работает, особенно если вы скопировали текст из другого источника.

Excel преобразовать число в текст прописью – Офис Ассист

Добрый день уважаемый читатель!

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

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

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

Решить эту проблему можно несколькими способами:

1. С помощью формул;
2. С помощью пользовательской функции;
3. С помощью VBA.

Создание сумм прописью с помощью формул

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

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

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

С помощью пользовательской функции

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

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

После, переносите его в папку AddIns, которую можно найти по пути C:\Documents and Settings\User\Application Data\Microsoft\AddIns, только вы вместо «User» подставляете имя вашего пользователя Windows.

Еще может возникнуть проблема в отображении папки «Application Data», она может быть скрыта, тогда вам в настройках «Свойства папки» нужно активировать пункт «Показать скрытые файлы и папки».

Теперь собственно и приступим к включении этой надстройки. В Excel выбираем «Файл» — «Параметры» — «Надстройки».

В диалоговом окне

«Настройки» ставим галочку напротив включаемой надстройки «Сумма Прописью» и нажимаем «ОК».

Следующим шагом, ставим курсор на нужную ячейку и вызываем «Мастер функций».

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

В результате мы получаем возможность получить следующие данные:

С помощью vba

Вот еще один способ на любителя. Конечно создать сумму прописью в Excel с помощью макроса не самый распространённый способ, но им достаточно часто пользовались и пользуются до сих пор. Большой сложности он не представляет, вам нужно:

• Комбинацией горячих клавиш ALT+F11 вызвать редактор Visual Basic;
• С помощью меню «Insert» — «Module», создать пустой новый модуль;
• Скопировать предоставленный код макроса в тело модуля.

Получается готовая пользовательская функция VBA, которая умеет превращать любую сумму от 0 до 9 999 999 в ее текстовое воплощение, создается сумма прописью.

Код нужного макроса выглядит так:

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

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

=СУММАПРОПИСЬЮ(A3)&» руб. «&ТЕКСТ((A3-ЦЕЛОЕ(A3))*100;»00″)&» коп.«

Приставку «Module2» в моей формуле можете опустить, так как таким образом, программа разделяет 2 похожие формулы, одна от установленных надстроек, а вторая от макроса.

Возвращаемые типы Return types

Тип возвращаемого значения определяется по имени функции в соответствии со следующей таблицей: The function name determines the return type as shown in the following:

Функция Function	Тип возвращаемых данных Return type	Диапазон выражения-аргумента Range for expression argument
CBool CBool	Boolean Boolean	Любое допустимое строковое или числовое выражение. Any valid string or numeric expression.
CByte CByte	Byte Byte	От 0 до 255. 0 to 255.
CCur CCur	Currency Currency	От -922 337 203 685 477,5808 до 922 337 203 685 477,5807. -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDate CDate	Date Date	Любое допустимое выражение даты. Any valid date expression.
CDbl CDbl	Double Double	От -1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; от 4,94065645841247E-324 до 1,79769313486232E308 для положительных значений. -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec CDec	Decimal Decimal	79 228 162 514 264 337 593 543 950 335 для чисел без десятичных знаков. 79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. Для чисел с 28 десятичными знаками диапазон составляет 7,9228162514264337593543950335. For numbers with 28 decimal places, the range is 7.9228162514264337593543950335. Наименьшим возможным числом, отличным от нуля, является число 0,0000000000000000000000000001. The smallest possible non-zero number is 0.0000000000000000000000000001.
CInt CInt	Integer Integer	От -32 768 до 32 767, дробная часть округляется. -32,768 to 32,767; fractions are rounded.
CLng CLng	Long Long	От -2 147 483 648 до 2 147 483 647, дробная часть округляется. -2,147,483,648 to 2,147,483,647; fractions are rounded.
CLngLng CLngLng	LongLong LongLong	От -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807; дробная часть округляется. -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; fractions are rounded. (Действительно только для 64-разрядных платформ). (Valid on 64-bit platforms only.)
CLngPtr CLngPtr	LongPtr LongPtr	От -2 147 483 648 до 2 147 483 647 для 32-разрядных систем; от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807 для 64-разрядных систем; дробная часть округляется в обоих типах систем. -2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems; fractions are rounded for 32-bit and 64-bit systems.
CSng CSng	Single Single	От -3,402823E38 до -1,401298E-45 для отрицательных значений; от 1,401298E-45 до 3,402823E38 для положительных значений. -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr CStr	String String	Результат, возвращаемый функцией CStr, зависит от аргумента выражение. Returns for CStr depend on the expression argument.
CVar CVar	Variant Variant	Диапазон совпадает с типом Double для числовых значений и Same range as Double for numerics. с типом String для нечисловых значений. Same range as String for non-numerics.

Поиск в строке

Функция	Параметры	Описание	Пример
InStr	Текст1, текст2	Находит положение текста	InStr(«Иван Петров»,»в»)
InStrRev	Проверка текста, соответствие текста	Находит позицию текста с конца	InStrRev(«Иван Петров»,»в»)

InStr и InStrRev — это функции VBA, используемые для поиска текста в тексте. Если текст поиска найден, возвращается позиция (с начала строки проверки) текста поиска. Когда текст поиска не найден, возвращается ноль. Если какой-либо текст имеет значение null, возвращается значение null.

InStr Описание параметров

InStr() Start, String1, String2, Compare

• Start : это число, указывающее начальную позицию поиска слева
• String1: текст, в котором будем искать
• String2: текст, который будем искать
• Compare как vbCompareMethod: см. Раздел «» для получения более подробной информации.

Использование InStr и примеры

InStr возвращает первую позицию в тексте, где найден данный текст. Ниже приведены некоторые примеры его использования.

Sub PoiskTeksta()

    Dim name As String
    name = "Иван Петров"

    ' Возвращает 3 - позицию от первой 
    Debug.Print InStr(name, "а")
    ' Возвращает 10 - позиция первого "а", начиная с позиции 4
    Debug.Print InStr(4, name, "а")
    ' Возвращает 8
    Debug.Print InStr(name, "тр")
    ' Возвращает 6
    Debug.Print InStr(name, "Петров")
    ' Возвращает 0 - текст "ССС" не найдет
    Debug.Print InStr(name, "ССС")

End Sub

InStrRev Описание параметров

InStrRev() StringCheck, StringMatch, Start, Compare

• StringCheck: текст, в котором будем искать
• StringMatch: Текст, который будем искать
• Start : это число, указывающее начальную позицию поиска справа
• Compare как vbCompareMethod: см. Раздел «Сравнить» для получения более подробной информации.

Использование InStrRev и примеры

Функция InStrRev такая же, как InStr, за исключением того, что она ищет с конца строки

Важно отметить, что возвращаемая позиция является позицией с самого начала. Поэтому, если существует только один экземпляр элемента поиска, InStr () и InStrRev () будут возвращать одно и то же значение

В следующем коде показаны некоторые примеры использования InStrRev.

Sub IspInstrRev()

    Dim name As String
    name = "Иван Петров"

    ' Обе возвращают 1 - позицию, только И
    Debug.Print InStr(name, "И")
    Debug.Print InStrRev(name, "И")

    ' Возвращает 11 - вторую в
    Debug.Print InStrRev(name, "в")
    ' Возвращает 3 - первую в с позиции 9
    Debug.Print InStrRev(name, "в", 9)

    ' Returns 1
    Debug.Print InStrRev(name, "Иван")

End Sub

Функции InStr и InStrRev полезны при работе с базовым поиском текста. Однако, если вы собираетесь использовать их для извлечения текста из строки, они могут усложнить задачу. Я написал о гораздо лучшем способе сделать это в своей статье Как легко извлечь любой текст без использования VBA InStr.

Операторы и выражения

Выражение — это значение или группа значений, выражающая отдельное значение. Каждое выражение вычисляется до отдельного значения. Например, выражение 10+20 имеет результат 30.

Выражения состоят из одной или более частей:

• Константы
• Переменные
• Операторы
• Массивы
• Элементы массива
• Функции

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

Выражения могут также иметь результатом одно из специальных значений Empty или Null.

Значение Empty представляет неинициализированную переменную типа Variant (или результат выражения, содержащий неинициализированную переменную типа Variant).

Значение Null представляет выражение, содержащее неверные данные.

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

Элементы данных, над которыми оператор выполняет действие, называются операндами. Например, в выражении 10+20 числа 10 и 20 являются операндами оператора сложения. Выражение может содержать один, несколько операторов или ни одного.

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

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

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

Смена формата ячейки.

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

Итак, первый быстрый способ видоизменения заключается в следующем:

1. Выберите ячейки с цифрами в текстовом формате.
2. На вкладке «Главная » в группе «Число» выберите « Общий» или « Числовой» в раскрывающемся списке «Формат» .

Или же можно воспользоваться контекстным меню, вызвав его правым кликом мышки.

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

Этот способ не слишком удобен и достался нам «в наследство» от предыдущих версий Excel, когда еще не было индикатора ошибки в виде зелёного уголка.

Примечание. Этот метод не работает в некоторых случаях. Например, если вы примените текстовый формат, запишете несколько цифр, а затем измените формат на «Числовой». Тут ячейка все равно останется отформатированной как текст.

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

Совет. Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

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