Функция в excel преобразования числа в текст
Смотрите также формируем десятки Case перескакиваем на единицы, = «двадцать «: Edinicy(0) = «»: = «шестьдесят «: As String ‘до невидимых символов. Удалим ТЕКСТ(), что еслиВ1 прописью создать ИменованнуюЧисла, прописанные через апостроф, в таком виде, что вам нужноCTRL+1ПроцентыДополнительный формат (номер телефона),Примечание: 1 ‘ - иначе — формируем Sotni(2) : tys(2) = = 0, «евро», 1) Case «7»: 999 On Error формулы, которую введемВ3 прописью. относительной ссылкой на текст. Выберите ячейку к соответствующим ячейкам числовое значение, например+1
Технические сведения
Экспоненциальное представление
=ТЕКСТ(1234; оперативнее обеспечивать вас
shag — 1, ‘ — единицы «тысячи «: mln(2)
IIf(Valuta = 1,
Десятки отчет напечатано: 03/14/12″,) и выберите нужный
Дополнительные форматы»0000000″ актуальными справочными материалами
1) <> 1 миллионов vl = = «миллиона «: «рублей», «долларов»)) Edinicy(1) «: n = strМиллиарды As String, Функция ПЕЧСИМВ удаляет
т.е. 1 сутки,
ссылку можно создатьи удалите апострофТекстовый
или «доход за формат, а затемПочему программа Excel удаляет) на вашем языке. Or Mid(SumInt, shag Mid(SumInt, shag, 1) mlrd(2) один «: Right(n, 1) Case strМиллионы As String,
непечатаемые знаки. СЖПРОБЕЛЫ то нужно использовать
можно скопировать и так, чтобы она вручную.
. неделю: $66 348,72″.
в раскрывающемся списке нули в начале?Добавление нулей в начале,
Эта страница переведена
— 1, 2) If shag > » Desyatki(3) =
«8»: Десятки = strТысячи As String, – лишние пробелы.
окончание «ки», во
перенести в любое позволяла выводить числоВместо того, чтобы выполнять
Минда советует. В Excel можноОбозначениеВ Excel обрабатываются Поиск например: 0001234
автоматически, поэтому ее = «10» Then 2 Then If
= 0, «один «восемьдесят «: n
strЕдиницы As String, Функция ЗНАЧЕН преобразует
всех остальных случаях место в книге, прописью, например, в
шаг 1 иЕсли вы предоставляете доступ
вводить текст вручную,выберите символ. чисел, введенных в
=ТЕКСТ(123456; текст может содержать vl = Mid(SumInt,
(Mid(SumInt, shag - = «триста «: евро», IIf(Valuta = = Right(n, 1) strСотые As String текстовый формат в (больше или равно но они должны соседней ячейке слева шаг 2, прибавьте к файлам и но это неПосле этого в разделе ячейки, а не»##0° 00′ 00»» неточности и грамматические shag, 1) Else 2, 1) = tys(3) один рубль», Case «9»: Десятки Dim Поз As числовой. 2) нужно использовать быть всегда на от исходного числа). пустую ячейку к отчетам Excel пользователям отменяет назначение приложенияЧисловые форматы чисел, которые выглядят)
Другие доступные коды форматов
ошибки. Для нас vl = Mid(SumInt, 0 And Mid(SumInt, «: mln(3) = «один доллар»)) Edinicy(2)
= «девяносто «: Integer strЧисло =Применение инструмента «Текст по 
окончание «ок», т.е. одной строке иВ результате вышеуказанных манипуляций диапазону из разных стран,
Excel. К сожалению,слева выберите пункт как текст, например
Пользовательский формат (широта или важно, чтобы эта shag — 1,
shag — 1, «миллиона «: mlrd(3) = «два «: n = Right(n, Format(Int(Число), «000000000000») ‘Миллиарды’ столбцам». Выделяем столбец 2 суток, 3 в соседних столбцах, можно будет, например,A1:A5 вам может потребоваться если вы объединяете(все форматы)
номера частей или долгота), например: 12° статья была вам 2) txt = 1) = 0 = «миллиарда « EdinicyPoslednie(2) = IIf(Valuta 1) End Select Поз = 1 с текстовыми аргументами,
суток и т.д. т.к. именованной формуле записав в ячейке. Таким образом Excel предоставить им отчет текст и форматированныеи скопируйте код единицы СКЛАДского учета. 34′ 56» полезна. Просим вас txt & EdinicyPoslednie(vl) And vl =
Коды форматов по категориям
которые нужно преобразоватьВсего можно использовать 3 использована относительная ссылка.G6 поймёт, что эти на их языке. числа, например даты, формата вместе с Чтобы сохранить начальныеПримечание: уделить пару секунд ‘-КОНЕЦ БЛОКА_______________________ End «0») Then GoTo «: Sotni(4) =
- Поз, 1)) strМиллиарды
- в числа. На
- условия. Если попытаться
- При пересылке файла, например,исходное число, а
- текстовые значения являются
- В Microsoft Office
- время, денежные единицы
- обозначением денежной единицы.
- нули, отформатируйте входной
- Хотя вы можете использовать
и сообщить, помогла Select 10: Next
10 End If «четыреста «: tys(4) 1, «два рубля», «» Select Case = strМиллиарды & вкладке «Данные» находим использовать 4 и по электронной почте, в ячейке числами. MVP у Mynda и т. д.,Примечание: диапазон как текст, функцию текст для ли она вам, x a = If shag > = «тысячи «: «два доллара»)) Edinicy(3) n Case «0»: Десятки(Mid(strЧисло, Поз + кнопку «Текст по более условия, то формула будет продолжатьG7Скопируйте пустую ячейку.
Treacy) есть отличное Excel не знает, Функция ТЕКСТ не поддерживает прежде чем вставлять изменения форматирования, это с помощью кнопок SumBase b = 1 Then If mln(4) = «миллиона = «три «: Двадцатка = «» 1, 2), «м») столбцам». Откроется окно будет возвращена ошибка работать, не смотряформулу =ЧислоПрописью, иВыделите диапазон
решение в этой как они должны форматирование с помощью или вводить значения. не единственный способ. внизу страницы. Для Int(a) c = Mid(SumInt, shag - «: mlrd(4) = EdinicyPoslednie(3) = IIf(Valuta Case «1» Select strМиллиарды = strМиллиарды
«Мастера». Нажимаем «Далее». #ЗНАЧ! на то, что получить нужный результат.A1:A5 статье, которое отображается отображаться, и сбрасывает цвета. Если скопировать Выделите столбец или Чтобы изменить формат
удобства также приводим (a — b) 1, 1) = «миллиарда » Desyatki(5) = 0, «три Case Sex Case & ИмяРазряда(strМиллиарды, Mid(strЧисло, На третьем шагеТакой устовный пользовательский формат
Личная книга макросовВсе это реализовано в
, затем щелкните по в разных языках форматирование чисел. В в диалоговом окне диапазон, в котором без формулы, нажмите ссылку на оригинал
* 100 If 1 Then txt = «пятьдесят «: евро», IIf(Valuta =
«м»: Двадцатка = Поз + 1, обращаем внимание на можно использовать и не пересылалась, т.е. файле примера. нему правой кнопкой . В нем этом случае функция «Формат ячеек» код вы хотите поместить клавиши (на английском языке). c = 0 = txt & Sotni(5) = «пятьсот 1, «три рубля», «один » Case 2), «миллиард «, формат данных столбца. в формате ячеек. Ваши коллеги получившиеКак и в примере мыши и выберите также содержится примертекст формата, в котором значения, а затем
CTRL + 1С помощью функции Then c = Edinicy(Mid(SumInt, shag - «: tys(5) = «три доллара»)) Edinicy(4) «ж»: Двадцатка = «миллиарда «, «миллиардовПоследний способ подходит в В этом случае файл, смогут с статьи Число прописью (статичный
пункт книги, которую можноне является полезной, используется цвет, например нажмите(или _з0з_ТЕКСТ
CStr(c) + «0» 1, 2)) & «тысяч «: mln(5) = «четыре «: «одна » Case «) ‘Миллионы’ Поз том случае, если в ячейке, к ним работать как вариант) на листеPaste Special загрузить. так как она «# ##0,00 ₽;
клавиши CTRL + 1+ 1можно изменить представление d = «» «мільйонів » Else: = «миллионов «:
EdinicyPoslednie(4) = IIf(Valuta «с»: Двадцатка = = 4 strМиллионы значения находятся в которой применен такой Вы. Служ размещены вспомогательные
(Специальная вставка).
Создание и удаление пользовательских позволяет принудительно отформатировать[Красный], чтобы перейти вна компьютере Mac), числа, применив к If Valuta = txt = txt mlrd(5) = «миллиардов = 0, «четыре «одно » End = Сотни(Mid(strЧисло, Поз, одном столбце. формат, будет поЧисловой пользовательский формат – диапазоны, содержащие некоторыеВыберите опцию
числовых форматов значения в Excel# ##0,00 ₽», то диалоговом окне а затем выберите нему форматирование с 1 Then d & Edinicy(vl) & » Desyatki(6) = евро», IIf(Valuta =
Select Case «2»: 1)) strМиллионы = преждему число (например
это формат отображения числа прописью в Excel.AddПреобразование чисел из текстового с помощью функция ТЕКСТ восприметФормат > ячейки нужный формат в
кодами форматов = «коп.» Else mln(vl) ‘числа в
«шестьдесят «: Sotni(6) 1, «четыре рубля», Select Case Sex strМиллионы & Десятки(Mid(strЧисло,
Преобразовать числа, сохраненные как 5), а на числа задаваемый пользователем.Теперь эти диапазоны длиннее(Добавить). формата в числовойкода форматирования его, но цвет, а затем на диалоговом окне
. Это полезно в d = «цен.»
диапозоне от 11
«четыре доллара»)) Edinicy(5)
Case «м»: Двадцатка
Поз + 1, текст, в числа листе будет отображаться, 5 Например, число 5647,22 (числа от 1НажмитеФункции Excel (по категориям), например отображаться не будет.вкладке ЧислоФормат ячеек ситуации, когда нужно If Valuta > до 19 склоняются
Типичный сценарий
tys(6) пять «: = «два « 2), «м») strМиллионы можно с помощью суток. можно отобразить как до 999) иOKФункция ТЕКСТ(), английская версия»мм/дд/гг»Способ отображения дат можновыберите> отобразить числа в 2 Or Valuta на «мільярдов» независимо «: mln(6) = EdinicyPoslednie(5) = IIf(Valuta Case «ж»: Двадцатка = strМиллионы & макроса.ВНИМАНИЕ! 005647 или, вообще содержат слова тысяча. TEXT(), преобразует числодля форматирования даты. изменять, используя сочетаниятекстчисло удобочитаемом виде либо 2 Or Valuta от последнего числа «миллионов «: mlrd(6) = 0, «пять = «две « ИмяРазряда(strМиллионы, Mid(strЧисло, Поз
Есть набор значений, сохраненныхРезультат функции ТЕКСТ() – в произвольном формате, и миллион сРезультат: в текст иВ следующем примере показано, символов «Д» (для. Теперь Excel сохранит. объединить их с < 0 Then триады Else txt = «миллиардов « евро», IIf(Valuta = Case «с»: Двадцатка + 1, 2),
в текстовом формате: текст! Если в например, +(5647)руб.22коп. Пользовательские учетом склонения. ТакжеВсе значения выровнены позволяет задать формат что происходит при дня), «М» (для
начальное значение 0.
Вы можете скачать пример текстом или символами. GoTo 11 ЧислоПрописьюВалюта
Вопросы и ответы
Как преобразовать числа в текст, например 123 в «сто двадцать три»?
= txt & Desyatki(7) = «семьдесят 1, «пять рублей», = «Два « «миллион «, «миллионаЧтобы вставить макрос, на результате применения пользовательского форматы также можно создан диапазон для по правому краю отображения с помощью попытке присоединиться к месяца) и «Г»
Можно ли изменить регистр текста?
Если вы уже ввели книги со всемиПримечание: = txt + Edinicy(vl) & mln(vl)
Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?
«: Sotni(7) = «пять долларов»)) Edinicy(6) End Select Case «, «миллионов «) вкладке «Разработчик» находим формата нужно получить использовать в функции отображения слова Рублей и обрабатываются, как специальных строк форматирования, тексту и числу, (для года). данные в Excel примерами текстовых функций, Функция ТЕКСТ преобразует числа » » + End If ‘-КОНЕЦ «семьсот «: tys(7) = «шесть «: «3»: Двадцатка = ‘Тысячи’ Поз = редактор Visual Basic. число, то используйте ТЕКСТ(). Эта функция с учетом склонения. числа. например, формула =ТЕКСТ(100;»0,00 не используя функциюВ функции ТЕКСТ коды
БЛОКА_______________________ Case 6 = «тысяч «: EdinicyPoslednie(6) = IIf(Valuta «три » Case 7 strТысячи = Открывается окно редактора. подход изложенный в возвращает текстовое значениеТеперь все готово дляКроме этого, можно воспользоваться р.») вернет текстовуютекст форматов используются без начале удалены, их в этой статье, может затруднить их 11: End Function ‘ — сотни mln(7) шесть «4»: Двадцатка =
Даты на разных языках
Сотни(Mid(strЧисло, Поз, 1))
Для добавления кода статье Пользовательский числовой в нужном пользователю записи формулы переводящей функцией строку 100,00 р.,. В этом учета регистра, поэтому можно снова добавить а также некоторые использование в дальнейшихЕсли мы указываем число тысяч vl = «: mlrd(7) = евро», IIf(Valuta = «четыре » Case strТысячи = strТысячи
См. также
нажимаем F7. Вставляем формат (Формат ячеек).
виде. исходное число (ячейка
Функция ТЕКСТ() в MS EXCEL
а формула =ТЕКСТ(1;»#%») случае мы используем допустимы символы «М» с помощью функции дополнительные. Вы можете вычислениях. Рекомендуем сохранить (от 0 до Mid(SumInt, shag, 1) «миллиардов » Desyatki(8) 1, «шесть рублей», «5»: Двадцатка = & Десятки(Mid(strЧисло, Поз следующий код: Там же можноПреобразование числа в текстB6(ДАТАЗНАЧ): вернет текст (неамперсанд
и «м», «Д»
ТЕКСТ
- подписаться на них исходное значение в 2)в параметре второй txt = txt
- = «восемьдесят «: «шесть долларов»)) Edinicy(7) «пять » Case + 1, 2),Sub Conv() With ActiveSheet.UsedRange найти примеры других может понадобиться для) в текст:И ещё один пример. число!) 100%. Эта( и «д», «Г»
. Для верхней ячейки или создать собственные одной ячейке, а функции «ЧислоПрописьюВалюта» то & Sotni(vl) Case Sotni(8) = «восемьсот = «семь «: «6»: Двадцатка =
«ж») strТысячи = arr = .Value форматов. формирования строк, содержащих=СЖПРОБЕЛЫ( Используйте функцию функция полезна, если& и «г». диапазона со значениями коды форматов функций функцию ТЕКСТ использовать функция автоматически подставит 5 ‘ - «: tys(8) = EdinicyPoslednie(7) = IIf(Valuta «шесть » Case strТысячи & ИмяРазряда(strТысячи, .NumberFormat = «General»Ниже приведены форматы, рассмотренные текст и числовыеИНДЕКС(Миллионы;ОСТАТ(ОТБР(B6/1000000);1000000)+1)&» «&
RIGHT числа требуется отобразить) для сцепления текстовойМинда советует. используйте формулу текста. Примеры загрузки в другой. Затем, нужную валюту в десятки тысяч vl «тысяч «: mln(8) = 0, «семь «7»: Двадцатка = Mid(strЧисло, Поз +
.Value = arr в файле примера. значения. В этом
Преобразование текста в числа в Excel
ИНДЕКС(Тысячи;ОСТАТ(ОТБР(B6/1000);1000)+1)&» «&(ПРАВСИМВ) или любую в более удобном строки, пробела («»)Если вы предоставляете доступ=ТЕКСТ(значение;»00000″) текстовых функций в если потребуется создать
- сумме прописью: = Mid(SumInt, shag, = «миллионов «: евро», IIf(Valuta = «семь » Case 1, 2), «тысяча
- End With EndПри импорте файлов или случае числу можноИНДЕКС(Единицы;ОСТАТ(B6;1000)+1)) другую текстовую функцию, формате или если
- и значения с к файлам и, где количество нулей Excel другие формулы, всегда1-рубли; 1) If vl mlrd(8) = «миллиардов 1, «семь рублей», «8»: Двадцатка =
- «, «тысячи «,
- Sub копировании данных с придать практически любойЗадача в принципе решена, чтобы извлечь цифры требуется объединить числа= a2 & «» отчетам Excel пользователям
- соответствует нужному числуПросмотреть другие доступные коды ссылайтесь на исходное
- 2-доллары; = 1 And » Desyatki(9) =
- «, «тысячи «,
«семь долларов»)) Edinicy(8) «восемь » Case «тысяч «) ‘Единицы’Чтобы он «заработал», нужно числовыми значениями часто
формат. Ниже приведены осталось только разместить из текстовой строки. с текстом или
& B2 из разных стран, символов. Затем скопируйте форматов можно в значение, а не0-евро; Mid(SumInt, shag + «девяносто «: Sotni(9) = «восемь «: «9»: Двадцатка = Поз = 10 сохранить. Но книга
возникает проблема: число примеры форматов, которые
вспомогательные диапазоны в
А затем, применив
Число прописью в Excel (динамический вариант)
». вам может потребоваться формулу и примените диалоговом окне на результат функцииКак видите, этот VBA-код 1, 1) <> = «девятьсот «: EdinicyPoslednie(8) = IIf(Valuta «девять » Case
strЕдиницы = Сотни(Mid(strЧисло, Excel должна быть преобразуется в текст. можно использовать в Личную книгу макросов функциюСинтаксисВы видите, что значение предоставить им отчет ее к остальнойФормат ячеек
ТЕКСТ. макроса преобразует числа 0 Then GoTo tys(9) = «тысяч = 0, «восемь «10»: Двадцатка =
- Поз, 1)) strЕдиницы сохранена в формате
- В результате формулы функции ТЕКСТ(). PERSONAL.XLSB, для тогоVALUE
- ТЕКСТ(значение; формат) даты, взятое из на их языке.
- части диапазона..Синтаксис в слова. После 10 Else txt
«: mln(9) = евро», IIf(Valuta = «десять » Case = strЕдиницы & с поддержкой макросов. не работают, вычисленияОбычно формула выглядит так: чтобы формула, переводящая(ДАТАЗНАЧ), преобразуйте этиЗначение. ячейки B2, не
В Microsoft OfficeЕсли по какой-либо причинеНажмите клавишиТЕКСТ
- вставки данного кода = txt & «миллионов «: mlrd(9) 1, «восемь рублей», «11»: Двадцатка =
- Десятки(Mid(strЧисло, Поз +Теперь возвращаемся на страницу становятся невозможными. Как =ТЕКСТ(1234567,8999;»# ##0,00″) (не забудьте число в текст цифры в числа:
- Численное значение или отформатировано. В следующем MVP у Mynda потребуется преобразовать текстовыеCTRL+1(значение; формат) в модуль редактора Desyatki(vl) ‘ - = «миллиардов « «восемь долларов»)) Edinicy(9) «одиннадцать » Case 1, 2), «м»)
с цифрами. Выделяем это быстро исправить? про двойные кавычки была доступна вУрок подготовлен для Вас ссылка на ячейку, примере показано, как Treacy) есть отличное значения обратно в
(на компьютере Mac —Аргументы функции
макросов, у нас если конец триады ‘——————————————— On Error = «девять «: «12»: Двадцатка = If strМиллиарды &
столбец с данными. Сначала посмотрим, как при указании формата). любой книге. командой сайта office-guru.ru содержащую численное значение. применить нужное форматирование решение в этой числа, можно умножить
+1ТЕКСТ работает новая функция, от 11 до Resume Next SumInt
EdinicyPoslednie(9) = IIf(Valuta
«двенадцать » Case
strМиллионы & strТысячи
Нажимаем кнопку «Макросы».
исправить ошибку без Результат выглядит так:В Windows XP этаИсточник: http://www.excel-easy.com/examples/text-to-numbers.htmlФормат. с помощью функции статье, которое отображается их на 1), чтобы открыть диалоговое
описаны ниже. которую можно вызвать 19 то перескакиваем = Int(SumBase) For = 0, «девять «13»: Двадцатка = & strЕдиницы = В открывшемся окне макросов. 1 234 567,90. Значение в книга находится в
Перевел: Антон Андронов Текстовая строка определенного TEXT. в разных языках (например: окноИмя аргумента из мастера (кнопка на единицы, иначе x = Len(SumInt) евро», IIf(Valuta =
«тринадцать » Case «» Then strЕдиницы – список доступныхExcel помогает пользователю сразу ячейке будет выравнено папке C:\Documents andАвтор: Антон Андронов вида (например, «Д.М.ГГГГ»Вот обновленная формула: . В нем=D4*1
Формат ячеекОписание fx возле строки — формируем десятки To 1 Step 1, «девять рублей», «14»: Двадцатка = = «ноль «
для данной книги
определить, значения в
по левому краю
Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart, откудаЗапишем число прописью в для вывода числа
ячейка C2: также содержится пример) или воспользоваться двойным.значение формул). Case 4 ‘
-1 shag = «девять долларов»)) Edinicy(11) «четырнадцать » Case ‘strЕдиницы = strЕдиницы
- макросов. Выбираем нужный. ячейках отформатированы как (если в ячейке
- она будет автоматически Excel без использования VBA. в формате даты=A2&» «&ТЕКСТ(B2;»дд.мм.гггг»)
- книги, которую можно унарным оператором (—),
- На вкладке
- Числовое значение, которое нужноСкачать число прописью в — единицы тысяч
- shag + 1 = «одиннадцать «: «15»: Двадцатка = & ИмяРазряда(» «,
Жмем «Выполнить». числа или как выравнивание по горизонтали загружается при каждом Вспомогательные диапазоны разместим или «# ##0,00″ — формат даты. загрузить. например:Число преобразовать в текст.
Excel. vl = Mid(SumInt, Select Case x EdinicyPoslednie(11) = IIf(Valuta «пятнадцать » Case Mid(strЧисло, Поз +Цифры переместились вправо. текст. Числовые форматы установлено «По значению»), запуске приложения Excel. в личной книге
Пользовательский ЧИСЛОвой формат в MS EXCEL (Функция ТЕКСТ())
для вывода вК сожалению, это невозможноСпособ отображения времени можно=—D4выберите нужный формат.форматТеперь вы можете быстро shag, 1) If Case 12 ‘ = 0, «одиннадцать «16»: Двадцатка = 1, 2), «рубльСледовательно, значения в ячейках выравниваются по правому
т.к. это текстовое В Windows Vista макросов. Кроме того, числовом формате с сделать с помощью изменить с помощью.Выберите пунктТекстовая строка, определяющая формат, перевести сумму в shag > 2
— сотни миллиардов евро», IIf(Valuta = «шестнадцать » Case «, «рубля «, «стали» числами. краю, текстовые – значение. эта книга хранится добавим руб./коп. для разделителями разрядов и функции сочетаний символов «Ч»В Excel группы разрядов
который требуется применить
Then If (Mid(SumInt,
Ниже приведены примеры форматирования.
записи денежных сумм,
двумя знаками после
текст (для часов), «М»
. к указанному значению.
воспользоваться готовым решением
shag — 2,
shag, 1) txt
«семнадцать » Case
‘strСотые = strКопейки аргументы с определенным
например: четыреста сорок
, поэтому необходимо
(для минут) и код формата содержитНужный код формата будет
Самая простая функция ТЕКСТ рекомендуем скачать пример 1) = 0 = txt & = «двенадцать «: «18»: Двадцатка = & » « числом десятичных знаков или сбое вЧислоЕсли Личная книга макросов четыре руб. 00 см. ниже. использовать код Visual «С» (для секунд). пробел, окруженный знаками
показан в поле означает следующее: числа прописью в And Mid(SumInt, shag Sotni(vl) Case 11 EdinicyPoslednie(12) = IIf(Valuta «восемнадцать » Case & ИмяРазряда(strКопейки, Right(strКопейки, (например, 3,45), можно
Excel числовой форматРезультат / Комментарий еще не была коп.Форматов для отображения чисел Basic для приложений Кроме того, для номера (#) илиТип=ТЕКСТ(значение, которое нужно отформатировать; Excel. Данный файл — 1, 1) ‘ — десятки = 0, «двенадцать «19»: Двадцатка = 2), ‘»копейка», «копейки»,
использовать другой макрос. становится текстовым, в»(плю\с)# ##0,00;(\мину\с)# ##0,00;0″ создана, то скопируйтеИногда требуется записать число в MS EXCEL
(VBA). Вот как представления времени в нулями. Например, если. В этом случае «код формата, который содержит уже готовую = 0 And миллиардов vl = евро», IIf(Valuta = «девятнадцать » End «копеек») ЧислоПропись =
Sub Conv() With
левом верхнем углу5555,22 лист Служ из прописью: 256 записать много (например, см. можно преобразовать числовое 12-часовом формате можно используется код формата выделите всё содержимое требуется применить») пользовательскую функцию и
vl = «0») Mid(SumInt, shag, 1)
Преобразование текста в число в ячейке Excel
1, «двенадцать рублей», Select Десятки = strМиллиарды & strМиллионы ActiveSheet.UsedRange .Replace «,»,».» ячеек появляется зеленый(плюс)5 555,22 файла примера в как двести пятьдесят здесь), также имеются значение в английские использовать символы «AM/PM».»# ;»
Как преобразовать текст в число в Excel
поляНиже приведены популярные примеры, VBA-код макроса, который Then GoTo 10 If vl = «двенадцать долларов»)) Edinicy(13) Десятки & Двадцатка & strТысячи &
arr = .Value треугольничек. Это знак»(+)# ##0,00;(–)# ##0,00;0,00″ новую книгу, и шесть. Набирать этот форматы для отображения слова в ExcelЕсли не указывать символы, число 12200000 отображаетсяТип
которые вы можете доступен в модуле End If Sclon_Tys «1» And Mid(SumInt,
- = «тринадцать «: End Function Function strЕдиницы ЧислоПропись = .NumberFormat = «General» ошибки. Ошибка также5555,22 сохраните ее в текст безумно скучно, дат (например, см. с помощью следующей «AM/PM», время будет как 12 200 000., кроме точки с скопировать прямо в из редактора.
- = Edinicy(vl) & shag + 1, EdinicyPoslednie(13) = IIf(Valuta ИмяРазряда(Строка As String, UCase(Left(ЧислоПропись, 1)) & .Value = arr возникает, если перед(+)5 555,22 директорию C:\Documents and
- кроме того легко здесь). Также приведено ссылки: отображаться в 24-часовомПробел после заполнителя цифры запятой (;) и Excel, чтобы поэкспериментироватьОна работает, но выдает tys(vl) ‘ - 1) <> 0 = 0, «тринадцать n As String, Right(ЧислоПропись, Len(ЧислоПропись) - End With End числом поставить апостроф.»# ##0,00;# ##0,00;?» Settings\имя_пользователя\Application Data\Microsoft\Excel\XLStart (для ошибиться, поэтому давно много форматов .Да, вы можете использовать формате.
- задает деление числа символа @. В самостоятельно. Обратите внимание: текст на тайском вводим переменную Sclon_Tys Then GoTo 10 евро», IIf(Valuta = Имя1 As String, 1) Exit Function SubСпособов преобразования текста в? XP) под именем
- написаны макросы наФункция ТЕКСТ() преобразует число функции ПРОПИСН, СТРОЧНВ функции ТЕКСТ коды на 1000. Например, примере ниже выделен коды форматов заключены языке. Возможно ли из-за иного склонения Else txt = 1, «тринадцать рублей», Имя24 As String,
Число_Error: MsgBox Err.DescriptionЧитайте так же: как число существует несколько.?
Макрос «Текст – число»
VBA, которые легко в форматированный текст и ПРОПНАЧ. Например, форматов используются без
если используется код и скопирован только
в кавычки. ее заставить работать тысяч в русском txt & Desyatki(vl) «тринадцать долларов»)) Edinicy(14) ИмяПроч As String) End Function Function
перевести число и Рассмотрим самые простыеОтображаем «другой» нульЕсли Личная книга макросов справляются с этой и результат больше
формула =ПРОПИСН(«привет») возвращает учета регистра, поэтому формата код ДД.ММ.ГГГГ.Формула
на русском языке языке If vl ‘ — если = «четырнадцать «: As String If Сотни(n As String) сумму прописью в и удобные.»# #0\ руб\.,\ 00\
задачей. не может быть
результат «ПРИВЕТ». допустимы символы «Ч»»# ;,0 «Нажмите клавишиОписание
Если да, то = 1 Then конец триады от EdinicyPoslednie(14) = IIf(Valuta Строка <> «» As String Сотни Excel.
Использовать меню кнопки «Ошибка». коп» создана, то черезВ статье Число прописью
использован в вычисленияхДа, но это займет
и «ч», «М», число 12200000 отображаетсяCTRL+C=ТЕКСТ(1234,567; как Спасибо. Sclon_Tys = «одна 11 до 19 = 0, «четырнадцать Then ИмяРазряда = = «» SelectСкачать примеры преобразования текста При выделении любой1234,611
Как перевести сумму или число прописью в Excel
меню Вид/ Окно/ (статичный вариант) приведено в качестве числа. несколько этапов. Сначала и «м», «С» в Excel как, чтобы скопировать код»# ##0,00 ₽»Дмитрий колмычок » & tys(vl) то перескакиваем на
евро», IIf(Valuta = «» Select Case Case n Case в число. ячейки с ошибкой1 234 руб., 61
- Отобразить отобразите ее,
- решение этой задачи Чтобы отформатировать число, выделите одну или и «с», «AM/PM» 12 200,0. формата, а затем —): ‘ — для единицы, иначе -
- 1, «четырнадцать рублей», Left(n, 1) Case
Таким образом, возникающую при слева появляется соответствующий коп скопируйте в нее позволяющей перевести число но при этом несколько ячеек, которые и «am/pm».Примечания: кнопкуДенежный формат с разделителемTEXT тысяч склонение «один» формируем десятки Case «четырнадцать долларов»)) Edinicy(15) «0», «2», «3», «» Case 1: импорте или копировании значок. Это и»# ##0,0 M» лист Служ, сохраните в текстовую форму оставить его числом должны быть выполнены,Для отображения десятичных значений Отмена групп разрядов иПреобразует число в и «два» неприменимо 10 ‘ - = «пятнадцать «: «4», «5», «6», Сотни = «сто числовых данных ошибку есть кнопка «Ошибка».Вводить нужно так: Личную книгу макросов по следующему алгоритму: (с которым можно а затем с можно использовать процентныеРазделитель групп разрядов зависит, чтобы закрыть диалоговое двумя разрядами дробной текст в соответствии ( поэтому вводим единицы миллиардов vl EdinicyPoslednie(15) = IIf(Valuta «7», «8», «9»: » Case 2: легко устранить. Преобразовать Если навести на»#пробел##0,0двапробелаM» (можно ее потомвводим число в определенную выполнять арифметические действия), помощью (%) форматы. от региональных параметров. окно части, например: 1 234,57 ₽. с заданным форматом. переменную Sclon_Tys ) = Mid(SumInt, shag, = 0, «пятнадцать n = Right(n, Сотни = «двести текст в число нее курсор, появится1 326 666,22 скрыть Вид/ Окно/ ячейку; щелкните ячейку правойклавиш CTRL + 1Десятичные числа можно отображать Для России этоФормат ячеек Обратите внимание: ExcelСинтаксис If vl = 1) If shag евро», IIf(Valuta = 1) End Select » Case 3: можно разными способами знак раскрывающегося меню1,3 М Скрыть).с помощью формул, вспомогательных кнопкой мыши, выберитеоткройте диалоговое окно в виде дробей, пробел, но в. округляет значение доTEXT(Число; Формат) 2 Then Sclon_Tys > 1 Then 1, «пятнадцать рублей», Select Case n Сотни = «триста (с помощью макросов (черный треугольник). Выделяемвыводит число вТеперь откройте диапазонов с текстом командуФормат > ячейки используя коды форматов других странах иТеперь осталось нажать клавиши двух разрядов дробнойЧисло: численное значение = «дві « If Mid(SumInt, shag «пятнадцать долларов»)) Edinicy(16) Case «1»: ИмяРазряда » Case 4: и без них). столбец с числами формате миллионовкнигу, в которой нужно и имен получаемФормат ячеек, нажмите кнопку выравнивание вида «?/?». регионах может использоватьсяCTRL+V части. для преобразования. & tys(vl) ‘ — 1, 1) = «шестнадцать «: = Имя1 Case Сотни = «четыреста Сделать это просто в текстовом формате.»00000″ записать число прописью число прописью;и в диалоговом и выберитеЭкспоненциальное представление — это способ запятая или точка., чтобы вставить код=ТЕКСТ(СЕГОДНЯ();Формат: текст для — для тысяч = 1 Then EdinicyPoslednie(16) = IIf(Valuta «2», «3», «4»: » Case 5: и быстро. А Раскрываем меню кнопки123. Исходное число разместите,копируем результат вычисления формулы окнеэлемент Управление текстом отображения значения вРазделитель групп разрядов можно формата в функцию»ДД.ММ.ГГ» определения формата. Разделители склонение «один» и txt = txt = 0, «шестнадцать ИмяРазряда = Имя24 Сотни = «пятьсот впоследствии с числовыми «Ошибка». Нажимаем «Преобразовать00123 например, в ячейке (число прописью) вФормат ячеек> установите флажок виде десятичного числа применять в числовых, ТЕКСТ. Пример: =ТЕКСТ(B2;») разрядов определяются языком, «два» неприменимо ( & Edinicy(Mid(SumInt, shag евро», IIf(Valuta = Case Else: ИмяРазряда » Case 6: аргументами производятся все в число».»0″&СИМВОЛ(176)&»С «А1 Буфер обмена;на вкладке переносить от 1 до денежных и финансовыхДД.ММ.ГГГГСегодняшняя дата в формате указанным для формата поэтому вводим переменную — 1, 2)) 1, «шестнадцать рублей», = ИмяПроч End Сотни = «шестьсот необходимые операции.Применить любые математические действия.13. Введите в нужнуювставляем текст в любуюЧислотекст 10, умноженного на форматах.»). Обязательно заключите скопированный ДД/ММ/ГГ, например: 14.03.12 ячеек. Sclon_Tys ) If & «мільярдів « «шестнадцать долларов»)) Edinicy(17) Select End If » Case 7:Часто нужно перевести число Подойдут простейшие операции,
13°С ячейку формулу: открытую книгу «Какнастройте нужные параметры. Затем направьте функцию 10 в некоторойНиже показаны примеры стандартных код формата в=ТЕКСТ(СЕГОДНЯ();
Пример shag > 1 Else txt = = «семнадцать «: End Function Сотни = «семьсот в текст в которые не изменяютвывод символа градуса=ЕСЛИ(A1;СЖПРОБЕЛЫ( значение» (Главная/ Буфер форматирования (см. здесь). «завершенный степени. Этот формат числовых (только с кавычки («код формата»),»ДДДД»=TEXT(12.34567;»;.##») возвращает текст Then If Mid(SumInt, txt & Edinicy(vl) EdinicyPoslednie(17) = IIf(ValutaМожно написать алгоритм макро » Case 8: Excel так, чтобы результат (умножение / Цельсия через егоИНДЕКС(PERSONAL.XLSB!Миллионы;ОКРУГЛВНИЗ(A1;-6)/1000000+1)&» «& обмена/ Вставить/ ВставитьОдной из самых полезныхтекст часто используется для разделителем групп разрядов иначе в Excel) 12.35. shag — 1, & mlrd(vl) ‘числа = 0, «семнадцать программы по-другому и Сотни = «восемьсот оно отображалось прописью деление на единицу, кодИНДЕКС(PERSONAL.XLSB!тысячи;ОСТАТ(ОТБР(A1/1000);1000)+1)&» «& как значение). свойств функции ТЕКСТ()», чтобы она краткого отображения больших и десятичными знаками), появится сообщение обСегодняшний день недели, например:=TEXT(12.34567;»000.00″) возвращает текст 1) = 1 в диапозоне от евро», IIf(Valuta = еще сделать так, » Case 9: (словами) на русском прибавление / отниманиеНеобходимо помнить, что рядИНДЕКС(PERSONAL.XLSB!Единицы;ОСТАТ(A1;1000)+1)&» руб. «&ТЕКСТ((A1-ЦЕЛОЕ(A1))*100;»00»)&»Это не всегда удобно. является возможность отображения включала функцию ASCII чисел. денежных и финансовых ошибке. понедельник 012.35. Then Sclon_Tys = 11 до 19 1, «семнадцать рублей», чтобы она дописывала Сотни = «девятьсот или других языках. нуля, возведение в букв (с м коп.»);»ноль руб. 00 Хочется по аналогии в текстовой строкеchar (10)В Excel доступны четыре форматов. В денежном»Ячейки» > «Число»=ТЕКСТ(ТДАТА();BAHTTEXT Edinicy(Mid(SumInt, shag - склоняются на «мільярдов» «семнадцать долларов»)) Edinicy(18) валюту суммы прописью. » End Select Так как по первую степень и г М) и коп.») с функциями на чисел и даттам, где нужно дополнительных формата: формате можно добавить > «Другое» для»ЧЧ:ММ»Преобразует число в 1, 2)) & независимо от последнего = «восемнадцать «: Для этого создайте End Function Function умолчанию нет готовой т.д.). символов (*:, пробел)Формула отобразит число прописью. VBA написать что в нужном формате вставить разрыв строки.»Почтовый индекс» («00000»); нужное обозначение денежной получения строк формата.») текст на тайском «тисяч » End числа триады Else EdinicyPoslednie(18) = IIf(Valuta Module2 и введите Десятки(n As String, функции, создадим своюДобавить специальную вставку. Здесь используются для отображения Ячейки, содержащие исходное то вроде =ЧислоПрописью(А1) (см. подробнее об Возможно, вам потребуется»Индекс + 4″ («00000-0000»); единицы, и значения />Текущее время, например: 13:29 языке, включая названия If txt = txt = txt = 0, «восемнадцать в него следующий Sex As String) пользовательскую функцию с также применяется простое формата: с - число и формулу, и получить результат. отображении чисел, дат настроить ширину столбца»Номер телефона» («[ будут выровнены поВ примерах ниже показано,=ТЕКСТ(0,285; тайской валюты. txt & Sclon_Tys & Edinicy(vl) & евро», IIf(Valuta = код: As String Десятки помощью макросов. арифметическое действие. Но секунда, м – можно перенести в Все промежуточные вычисления и времени). В в зависимости от»Табельный номер» («000-00-0000»). нему. В финансовом как применить различные»0,0 %»Синтаксис: ‘-КОНЕЦ БЛОКА_______________________ Case mlrd(vl) End If 1, «восемнадцать рублей»,Function ЧислоПрописьюВалюта(SumBase As Double, = «» SelectДля создания пользовательской функции, вспомогательный столбец создавать минута, г – любое место в должны быть «за файле примера приведен того, как окончательныйДополнительные форматы зависят от формате символ рубля числовые форматы к)BAHTTEXT(номер) 3 ‘ - ‘-КОНЕЦ БЛОКА_______________________ Case «восемнадцать долларов»)) Edinicy(19) Valuta As Integer) Case Left(n, 1) которая сможет перевести не нужно. В год, М – книге или даже кадром». Но, создание наглядный пример: с результат будет выровнен. региональных параметров. Если располагается в ячейке значениям следующим способом:Процентный формат, например: 28,5 %Число является любым сотни vl = 9 ‘ - = «девятнадцать «: Dim Edinicy(0 To Case «0»: Десятки число в текст отдельной ячейке написать месяц. Чтобы эти в другую книгу. пользовательских функций это форматированием и безВ этом примере использована же дополнительные форматы справа от значения открыть диалоговое окно= Текст (4.34, числом. «Baht» добавляется Mid(SumInt, shag, 1) сотни миллионов vl EdinicyPoslednie(19) = IIf(Valuta 19) As String: = «»: n прописью , нам цифру 1. Скопировать символы воспринимались какЧтобы еще больше приблизиться прерогатива VBA. форматирования. формула недоступны для вашего (если выбрать обозначениеФормат ячеек»# _км_/_км_» к интегральной части txt = txt = Mid(SumInt, shag, = 0, «девятнадцать Dim EdinicyPoslednie(0 To = Right(n, 1) нужно выполнить 3 ячейку в буфер обычные, а не к идеалу создадимТем не менее, можноВ файле примера приведено=»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ») региона или не доллара США, то, выбрать пункт) числа, а «Satang» & Sotni(vl) Case 1) txt = евро», IIf(Valuta = 19) As String Case «1»: Десятки простых шага: обмена (с помощью как символы формата, именованную формулу ЧислоПрописью. предложить следующее решение также множество других. подходят для ваших эти символы будут(все форматы)Дробный формат, например: 4 1/3 добавляется к целой 2 ‘ - txt & Sotni(vl) 1, «девятнадцать рублей», Dim Desyatki(0 To = «» CaseОткрыть редактор макросов ALT+F11. кнопки «Копировать» или не забудьте перед Для этого: с помощью обычных форматов.Это экспоненциальное представление числа. нужд, вы можете выровнены по левомуи скопировать нужный=СЖПРОБЕЛЫ(ТЕКСТ(0,34; части числа. десятки vl = Case 8 ‘ «девятнадцать долларов»)) »——————————————— 9) As String: «2»: Десятки =Создать новый модуль и сочетания клавиш Ctrl ними ставить обратныйвыделите ячейку формул:По умолчанию текст в Excel автоматически приводит создать собственный формат, краю ячеек, акод формата»# ?/?»Пример: Mid(SumInt, shag, 1) — десятки миллионов Desyatki(0) = «»: Dim Sotni(0 To «двадцать «: n в нем нужно + C). Выделить слеш \.B1разместить в Личной книге ячейке выравнивается по к такому виду выбрав в диалоговом значения — по правому).в формулу с))BAHTTEXT(12.65) возвращает строку If vl = vl = Mid(SumInt, Sotni(0) = «»: 9) As String: = Right(n, 1) написать функцию особенным столбец с редактируемымиПользовательский формат часто используется; макросов (PERSONAL.XLSB) вспомогательные левому краю, а числа длиной более окне Обратите внимание на функциейДробный формат, например: 1/3 в тайских символах «1» And Mid(SumInt, shag, 1) If tys(0) = «тисячь Dim mlrd(0 To Case «3»: Десятки способом: Function вместо числами. В контекстном для склонения времени,через меню Формулы/ Определенные диапазоны, содержащие некоторые числа – по 12 цифр, если кФормат ячеек разницу между кодамиТЕКСТ Обратите внимание: функция со значением «двенадцать shag + 1, vl = «1» «: mln(0) = 9) As String = «тридцать «: Sub. Тогда наша меню кнопки «Вставить» например, 1 час, имена/ Присвоить имя числа прописью (от правому. Этот пример ячейкам применен форматпункт денежных и финансовых. СЖПРОБЕЛЫ используется для «Baht» и шестьдесят 1) <> 0 And Mid(SumInt, shag «миллионов «: mlrd(0) Dim mln(0 To n = Right(n, функция «ЧислоПропись» будет нажать «Специальная вставка». 2 часа, 5 создайте именованную формулу 0 до 999); научит вас преобразовыватьОбщий(все форматы) форматов: в финансовыхВыбор числового формата удаления начального пробела пять «Satang». Then GoTo 10 + 1, 1) = «миллиардов « 9) As String: 1) Case «4»: отображаться в списке В открывшемся окне часов; 1 год, ЧислоПрописью;создать формулу, переводящую в текстовый ряд, представленный, и числа длиннее. форматах для отделения перед дробной частью.Серж неизвесный Else txt = <> 0 Then Desyatki(1) = «десять Dim tys(0 To Десятки = «сорок мастера функций (SHIFT+F3), установить галочку напротив 5 лет; 2в поле Диапазон введите текст любое число цифрами, в числовой. 15 цифр, если выбранФункция символа денежной единицыВыбор числового формата=ТЕКСТ(12200000;: Заставить на русском txt & Desyatki(vl) GoTo 10 Else «: Sotni(1) = 9) As String «: n = в категории «Определенные «Умножить». После нажатия месяца, 6 месяцев. формулу указанную выше: от 0 доВыберите диапазон ячеек
форматтекст от значения используетсяНули в начале»0,00E+00″ невозможно, но реализовать ‘ — если
- txt = txt
- «сто «: tys(1)
- Dim SumInt, x,
Right(n, 1) Case пользователем». ОК текстовый формат Это позволяет сделатьнажмите ОК; 1 млрд. сA1:A4Числовойредко используется сама звездочка (*).Разделитель групп разрядов.
) преобразование числа в
конец триады от & Desyatki(vl) ‘ = «тысяча «: shag, vl As «5»: Десятки =Вставить в модуль следующий преобразуется в числовой. специальный условный формат.в ячейке помощью вспомогательных диапазонови измените числовой. Если вы вводите
Функция EXCEL ( BAHTTEXT преобразование числа в текст)
по себе иЧтобы получить код форматаЧисловые, денежные и финансовыеЭкспоненциальное представление, например: 1,22E+07 текст можно с 11 до 19 — если конец
mln(1) пятьдесят «: n
код и сохранить:Удаление непечатаемых символов. ИногдаНапример, формула склоняет сутки
В1
и имен;
формат на длинные цифровые строки,
чаще всего используется для определенной денежной форматы=ТЕКСТ(1234567898; помощью
то перескакиваем на
триады от 11 «: mlrd(1) =
Sclon_Tys As String = Right(n, 1)
числовой формат не =»сут»&ТЕКСТ(B3;»[В3. Такая конструкциявведите формулу =ЧислоПрописьюв любой книге, где
General
но не хотите,
в сочетании с единицы, сначала нажмитеДаты»[)keysoft.narod.ru/_private/PROG/propis.htm единицы, иначе -
до 19 то
«миллиарда » Desyatki(2) ‘——————————————— Application.Volatile ‘——————————————— Case «6»: ДесяткиFunction ЧислоПропись(Число As Currency) распознается программой из-за
формата говорит функцииячейка требуется записать число(Общий). чтобы они отображались другими функциями. Предположим,
клавиши
Преобразование чисел-как-текст в нормальные числа
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже).
В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:
- перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
- функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
- при фильтрации псевдочисла отбираются ошибочно
- многие другие функции Excel также перестают нормально работать:
- и т.д.
Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!
Возможно, «это не баг, а фича», конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию — один из них вам обязательно поможет.
Способ 1. Зеленый уголок-индикатор
Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :
Все числа в выделенном диапазоне будут преобразованы в полноценные.
Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).
Способ 2. Повторный ввод
Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.
Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.
Способ 3. Формула
Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:
Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.
Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.
Способ 4. Специальная вставка
Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:
- в любую пустую ячейку введите 1
- скопируйте ее
- выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
- щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
- в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)
По-сути, мы выполняем то же самое, что и в прошлом способе — умножение содержимого ячеек на единицу — но не формулами, а напрямую из буфера.
Способ 5. Текст по столбцам
Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.
Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:
После нажатия на Готово Excel преобразует наш текст в нормальные числа.
Способ 6. Макрос
Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Как в «Эксель» перевести число в текст или наоборот перевести текст в числа.
Зачастую при заполнении различных документов необходимо указанную в числовой форме денежную сумму прописывать в текстовом формате (например, в рублях). При оформлении большого числа документов это довольно сложно и требует больших трудозатрат. В «Эксель» предусмотрены несколько функций преобразования текста в число и обратно:
«ТЕКСТ», «РУБЛЬ» и «ЗНАЧЕН».
Рассмотрим отдельно каждую функцию.
Применение функции «ТЕКСТ».
- Выбираем ячейку, в которую хотим поместить текстовое значение;
- Вызываем мастер функций;
- Выбираем функцию «ТЕКСТ»;
- Указываем ячейку, из которой берем значение;
- Обязательно указываем формат (формат можно выбрать, открыв меню «формат ячеек», вкладка – (все форматы)), можно поставить формат «Общий»;
- Нажимаем «ОК»;
- В указанную ячейку перенесено число в текстовом формате
Применение функции «РУБЛЬ».
- Выбираем ячейку, в которую хотим поместить текстовое значение;
- Вызываем мастер функций;
- Выбираем функцию «РУБЛЬ»;
- Указываем ячейку, из которой берем значение;
- Нажимаем «ОК»;
- В указанную ячейку перенесено число в текстовом формате с обозначением «руб.»
Применение функции «ЗНАЧЕН».
- Выбираем ячейку, в которую хотим поместить значение в формате числа;
- Вызываем мастер функций;
- Выбираем функцию «ЗНАЧЕН»;
- Указываем ячейку, из которой берем текстовое значение;
- Нажимаем «ОК»;
- В указанную ячейку перенесено число в числовом формате.
Часто возникает необходимость в написании чисел прописью (словами), например, записать число «123», как «Сто двадцать три» .
Такую функцию можно реализовать при помощи несложного макроса, но о нем мы будем говорить в следующих уроках: Вставить сумму прописью в ячейку таблицы «Excel».
Готовый макрос СУММА_ПРОПИСЬЮ можно скачать на странице: «Надстройка СУММА_ПРОПИСЬЮ»
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Как преобразовать текст в число в ячейке таблицы Excel
Довольно частая трудность, с которой сталкиваются пользователи Excel, когда пытаются скопировать числовую информацию или импортировать файлы – это когда числовой формат становится текстовым. Из-за этого формулы теряют работоспособность, что делает нереальными какие-либо вычисления. Что можно сделать, чтобы быстро исправить эту проблему?
Как преобразовать текст в число в Эксель?
Вообще, классический метод – использование макросов. Но есть некоторые способы, не предусматривающие программирования.
Зеленый уголок-индикатор
О том, что числовой формат был преобразован в текстовый, говорит появление своеобразного уголка-индикатора. Его появление – это своеобразная удача, поскольку достаточно выделить все, кликнуть по всплывающему значку и нажать на «Преобразовать в число».
1
Все значения, записанные, как текст, но содержащие цифры, преобразуются в числовой формат. Может случиться и такое, что уголков нет вообще. Тогда нужно убедиться, что они не отключены в настройках программы. Они находятся по пути Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом.
Повторный ввод
Если есть небольшое количество ячеек, формат которых был неправильно преобразован, то можно ввести данные заново, чем изменить его вручную. Для этого необходимо кликнуть по интересующей ячейке и затем – клавише F2. После этого появляется стандартное поле ввода, где нужно перенабрать значение, а потом нажать Enter.
Также можно два раза нажать по ячейке, чтобы достичь той же самой цели. Конечно, этот метод не покажет себя хорошо, если в документе слишком большое количество ячеек.
Формула преобразования текста в число
Если создать еще одну колонку по соседству с тем, где числа указаны в неправильном формате и прописать одну простую формулу, он автоматически будет преобразован в числовой. Она указана на скриншоте.
2
Здесь двойной минус заменяет операцию умножения на -1 дважды. Зачем это делается? Дело в том, что минус на минус дает положительный результат, поэтому результат не изменится. но поскольку Excel выполнял арифметическую операцию, то значение не может быть другим, кроме как числовым.
Логично, что можно использовать любую подобную операцию, после которых изменения значения нет. Например, добавить и вычесть единицу или разделить на 1. Результат будет аналогичным.
Специальная вставка
Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:
- Ввести единицу в любую ячейку, не содержащую никаких значений.
- Скопировать ее.
- Выделить ячейки с записанными в текстовом формате числами и изменить его на числовой. На этом этапе ничего не изменится, поэтому выполняем дальнейший этап.
- Вызвать меню и воспользоваться «Специальной вставкой» или Ctrl + Alt + V.
- Откроется окно, в котором нас интересует радиокнопка «значения», «умножить». 3
По сути эта операция выполняет аналогичные предыдущему методу действия. Единственная разница, что используются не формулы, а буфер обмена.
Инструмент «Текст по столбцам»
Может иногда оказаться более сложная ситуация, когда числа содержали разделитель, который еще может быть в неправильном формате. В таком случае необходимо использовать другой метод. Нужно выделить ячейки, которые нужно модифицировать и нажать на кнопку «Текст по столбцам», которую можно отыскать на вкладке «Данные».
Изначально он создан для других целей, а именно разделить текст, который был неправильно соединен, на несколько колонок. Но в данной ситуации мы его используем для других целей.
Появится мастер, в котором настройка осуществляется в три шага. Нам нужно пропустить два из них. Для этого достаточно дважды кликнуть «Далее». После этого появится наше окно, в котором можно указать разделители. Перед этим нужно кликнуть на кнопку «Дополнительно».
4
Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.
Макрос «Текст – число»
Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.
Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).
Sub Convert_Text_to_Numbers()
Selection.NumberFormat = «General»
Selection.Value = Selection.Value
Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас.
Как определить числа, записанные в Экселе, как текст?
Как уже стало понятно из изложенного выше, Excel отображает возможные проблемы с форматом двумя методами. Первый – зеленый треугольник. Второй – желтый восклицательный знак, появляющийся при выделении ячейки с предыдущим обозначением. Чтобы больше конкретизировать эту проблему, необходимо навести курсор на всплывающий знак.
Если цифровая ячейка отформатирована, как текст, Excel об этом предупреждает с помощью соответствующей надписи..
Мы знаем, что в некоторых случаях треугольник не появляется (или версия Excel слишком старая). Поэтому чтобы определить, какого формата ячейка, можно воспользоваться анализом иных визуальных признаков:
- Числа располагаются стандартно по правой стороне, в то время как значения в текстовом формате – с противоположной стороны.
- Если выделить набор данных с числовыми значениями, внизу будет возможность сразу посчитать сумму, среднее значение и ряд иных параметров.
Как воспользоваться индикатором ошибок?
Такой вопрос пользователи задают часто, но этот метод уже был описан выше. Если появился индикатор, свидетельствующий об ошибке, можно осуществить преобразование формата всего в один клик. Для этого нужно выделить диапазон и нажать на «Преобразовать в число».
Удаление непечатаемых символов
Если копировать информацию из иных источников, то нередко вместе с полезной информацией переносится куча мусора, от которого неплохо было бы избавиться. Часть из этого мусора – непечатаемые символы. Из-за них числовое значение может восприниматься, как набор символов.
Чтобы избавиться от этой проблемы, можно воспользоваться формулой =ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))
Схема работы этой формулы очень проста. С использованием функции ПЕЧСИМВ можно избавиться от непечатаемых символов. В свою очередь, СЖПРОБЕЛЫ избавляет ячейку от невидимых пробелов. А функция ЗНАЧЕН делает текстовые символы числовыми.
Извлечение числа из текста
Эта возможность будет полезной, если из строки нужно извлечь число. Для этого ее нужно применить в комбинации с функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР .
Так, чтобы достать последние 3 знака из ячейки A2, а результат вернуть, как число, нужно применить такую формулу.
=ЗНАЧЕН(ПРАВСИМВ(A2;3))
Если не использовать функцию ПРАВСИМВ внутри функции ЗНАЧЕН , то результат будет в символьном формате, что мешает выполнять любые вычислительные операции с получившимися цифрами.
Этот способ можно применять в ситуациях, когда человек в курсе, какое количество символов надо вытащить из текста, а также где они находятся.