Excel текст число с разделителем

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

Конвертация числа в текстовый вид

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.

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



2. Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».



3. В открывшемся окне форматирования переходим во вкладку «Число», если оно было открыто в другом месте. В блоке настроек «Числовые форматы» выбираем позицию «Текстовый». Для сохранения изменений жмем на кнопку «OK» в нижней части окна.



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



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



6. Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.



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

Урок: Как изменить формат в Excel

Способ 2: инструменты на ленте

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

1. Выделяем элементы, данные в которых нужно преобразовать в текстовый вид. Находясь во вкладке «Главная» кликаем по пиктограмме в виде треугольника справа от поля, в котором отображается формат. Оно расположено в блоке инструментов «Число».





2. В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».



3. Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ. Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.

1. Устанавливаем курсор в первый элемент диапазона, в котором будет выводиться результат преобразования. Щелкаем по значку «Вставить функцию», который размещен около строки формул.



2. Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».



3. Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:

   =ТЕКСТ(значение;формат)

   Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».

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

   В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.

   После того, как все требуемые параметры введены, щелкаем по кнопке «OK».



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



5. Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».



6. Далее, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимаем выделение с области, которая содержит формулы. Кликаем по ней правой кнопкой мыши. Происходит запуск контекстного списка действий. Выбираем в нем позицию «Специальная вставка». Среди вариантов действий в открывшемся списке выбираем «Значения и форматы чисел».
   

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



7. В любом случае, в выбранный диапазон будут вставлены данные в текстовом виде. Если же вы все-таки выбрали вставку в исходную область, то ячейки, содержащие формулы, можно очистить. Для этого выделяем их, кликаем правой кнопкой мыши и выбираем позицию «Очистить содержимое».

На этом процедуру преобразования можно считать оконченной.

Урок: Мастер функций в Excel

Конвертация текста в число

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

Способ 1: преобразование с помощью значка об ошибке

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

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



2. Открывается список действий. Выбираем в нем значение «Преобразовать в число».



3. В выделенном элементе данные тут же будут преобразованы в числовой вид.

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

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



2. Открывается уже знакомый нам список. Как и в прошлый раз, выбираем позицию «Преобразовать в число».

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

Способ 2: конвертация при помощи окна форматирования

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

1. Выделяем диапазон, содержащий цифры в текстовом варианте. Кликаем правой кнопкой мыши. В контекстном меню выбираем позицию «Формат ячеек…».



2. Выполняется запуск окна форматирования. Как и в предыдущий раз, переходим во вкладку «Число». В группе «Числовые форматы» нам нужно выбрать значения, которые позволят преобразовать текст в число. К ним относится пункты «Общий» и «Числовой». Какой бы из них вы не выбрали, программа будет расценивать цифры, введенные в ячейку, как числа. Производим выбор и жмем на кнопку. Если вы выбрали значение «Числовой», то в правой части окна появится возможность отрегулировать представление числа: выставить количество десятичных знаков после запятой, установить разделителями между разрядами. После того, как настройка выполнена, жмем на кнопку «OK».



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

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

Способ 3: конвертация посредством инструментов на ленте

Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.

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



2. Далее прощелкиваем уже не раз описанным нами способом каждую ячейку преобразуемой области с применением клавиш F2 и Enter.

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

Способ 4: применение формулы

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

1. В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.



2. Жмем на клавишу Enter, после чего получаем готовое преобразованное значение. Для того, чтобы применить данную формулу для всех других ячеек диапазона, используем маркер заполнения, который ранее был применен нами для функции ТЕКСТ.



3. Теперь мы имеем диапазон, который заполнен значениями с формулами. Выделяем его и жмем на кнопку «Копировать» во вкладке «Главная» или применяем сочетание клавиш Ctrl+C.



4. Выделяем исходную область и производим щелчок по ней правой кнопкой мыши. В активировавшемся контекстном списке переходим по пунктам «Специальная вставка» и «Значения и форматы чисел».



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

Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1». Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)

Урок: Как сделать автозаполнение в Excel

Способ 5: применение специальной вставки

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

1. В любую пустую ячейку на листе вписываем цифру «1». Затем выделяем её и жмем на знакомый значок «Копировать» на ленте.



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



3. В окне специальной вставки выставляем переключатель в блоке «Операция» в позицию «Умножить». Вслед за этим жмем на кнопку «OK».



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

Способ 6: использование инструмента «Текст столбцами»

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

1. Выделяем фрагмент листа, содержимое которого нужно преобразовать. Переходим во вкладку «Данные». На ленте инструментов в блоке «Работа с данными» кликаем по значку «Текст по столбцам».



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



3. Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».



4. А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».



5. Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».



6. Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».



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

Способ 7: применение макросов

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

1. Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».



2. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:

   
Sub Текст_в_число()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub


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



3. Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».



4. Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».



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

Урок: Как создать макрос в Экселе

Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами». Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.

Преобразование чисел-как-текст в нормальные числа

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа 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 и скопируйте туда следующий код:

Sub Convert_Text_to_Numbers()
    Selection.NumberFormat = "General"
    Selection.Value = Selection.Value
End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) — и моментально преобразовать псевдочисла в полноценные.

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

P.S.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

• Деление слипшегося текста по столбцам
• Вычисления без формул специальной вставкой
• Преобразование текста в числа с помощью надстройки PLEX

Функция

ТЕКСТ(

)

, английская версия TEXT()

,

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

=ТЕКСТ(100;»0,00 р.»)

вернет текстовую строку 100,00 р., а формула

=ТЕКСТ(1;»#%»)

вернет текст (не число!) 100%. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.

Синтаксис

ТЕКСТ(значение; формат)

• 
  Значение.
  
  Численное значение или ссылка на ячейку, содержащую численное значение.
• 
  Формат.
  
  Текстовая строка определенного вида (например, «Д.М.ГГГГ» для вывода числа в формате даты или «# ##0,00» для вывода в числовом формате с разделителями разрядов и двумя знаками после запятой. Правила форматирования см. ниже.

Форматов для отображения чисел в MS EXCEL много (например, см.

здесь

), также имеются форматы для отображения дат (например, см.

здесь

). Также приведено много форматов

в статье к функции ТЕКСТ() на сайте Microsoft

.

Функция

ТЕКСТ()

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

Формат ячеек

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

Формат ячеек

на вкладке

Число

настройте нужные параметры форматирования (см.

здесь

).

Одной из самых полезных свойств функции

ТЕКСТ()

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

чисел

,

дат

и

времени

). В

файле примера

приведен наглядный пример: с форматированием и без форматирования.

В

файле примера

приведено также множество других форматов.

Функция ТЕКСТ

​Смотрите также​​ формируем десятки Case​ перескакиваем на единицы,​ = «двадцать «:​ Edinicy(0) = «»:​ = «шестьдесят «:​ As String ‘до​ невидимых символов. Удалим​ ТЕКСТ(), что если​В1​ прописью создать Именованную​Числа, прописанные через апостроф,​ в таком виде,​ что вам нужно​CTRL+1​Проценты​Дополнительный формат (номер телефона),​Примечание:​ 1 ‘ -​ иначе — формируем​ Sotni(2) = «двести​

​ EdinicyPoslednie(0) = IIf(Valuta​​ n = Right(n,​​ 999 999 999​ их с помощью​ в ячейке​​будет содержать число​​ формулу ЧислоПрописью с​ расцениваются программой, как​ то сначала примените​ объединить текст и​(на компьютере Mac —​Дроби​

​ например: (123) 456-7898​​ Мы стараемся как можно​ единицы If Mid(SumInt,​ десятки Case 7​ «: tys(2) =​ = 0, «евро»,​ 1) Case «7»:​ 999 On Error​ формулы, которую введем​В3​ прописью.​ относительной ссылкой на​ текст. Выберите ячейку​ к соответствующим ячейкам​ числовое значение, например​+1​

Технические сведения

​Экспоненциальное представление​

​=ТЕКСТ(1234;​​ оперативнее обеспечивать вас​

​ shag — 1,​​ ‘ — единицы​​ «тысячи «: mln(2)​

Общие сведения

​ «отчет напечатано: 03/14/12»,​) и выберите нужный​

• ​Дополнительные форматы​»0000000″​ актуальными справочными материалами​

​ 1) <> 1​ миллионов vl =​ = «миллиона «:​ «рублей», «долларов»)) Edinicy(1)​ «: n =​ strМиллиарды As String,​ Функция ПЕЧСИМВ удаляет​

​ (Mid(SumInt, shag -​​ = «триста «:​ евро», IIf(Valuta =​ = Right(n, 1)​ strСотые As String​ текстовый формат в​ (больше или равно​ но они должны​​ соседней ячейке слева​​ шаг 2, прибавьте​​ к файлам и​​ но это не​После этого в разделе​ ячейки, а не​»##0° 00′ 00»»​​ неточности и грамматические​​ shag, 1) Else​​ 2, 1) =​​ tys(3) = «тысячи​

Скачивание образцов

​ 1, «один рубль»,​ Case «9»: Десятки​ Dim Поз As​ числовой.​ 2) нужно использовать​ быть всегда на​ от исходного числа).​ пустую ячейку к​ отчетам Excel пользователям​ отменяет назначение приложения​Числовые форматы​ чисел, которые выглядят​)​

Другие доступные коды форматов

​ ошибки. Для нас​ vl = Mid(SumInt,​ 0 And Mid(SumInt,​​ «: mln(3) =​​ «один доллар»)) Edinicy(2)​

1. ​ = «девяносто «:​​ Integer strЧисло =​​Применение инструмента «Текст по​ ​ окончание «ок», т.е.​​ одной строке и​В результате вышеуказанных манипуляций​​ диапазону​​ из разных стран,​

2. ​ Excel. К сожалению,​​слева выберите пункт​​ как текст, например​

3. ​Пользовательский формат (широта или​​ важно, чтобы эта​​ shag — 1,​

4. ​ shag — 1,​ «миллиона «: mlrd(3)​​ = «два «:​​ n = Right(n,​ Format(Int(Число), «000000000000») ‘Миллиарды’​ столбцам». Выделяем столбец​​ 2 суток, 3​​ в соседних столбцах,​ можно будет, например,​A1:A5​ вам может потребоваться​ если вы объединяете​(все форматы)​

5. ​ номера частей или​​ долгота), например: 12°​​ статья была вам​ 2) txt =​ 1) = 0​​ = «миллиарда «​​ EdinicyPoslednie(2) = IIf(Valuta​ 1) End Select​​ Поз = 1​​ с текстовыми аргументами,​

6. ​ суток и т.д.​​ т.к. именованной формуле​​ записав в ячейке​. Таким образом Excel​ предоставить им отчет​​ текст и форматированные​​и скопируйте код​ единицы СКЛАДского учета.​ 34′ 56»​ полезна. Просим вас​ txt & EdinicyPoslednie(vl)​ And vl =​

​ Desyatki(4) = «сорок​ = 0, «два​ Dim Двадцатка As​ strМиллиарды = Сотни(Mid(strЧисло,​

Коды форматов по категориям

​ которые нужно преобразовать​Всего можно использовать 3​ использована относительная ссылка.​G6​ поймёт, что эти​​ на их языке.​​ числа, например даты,​​ формата вместе с​​ Чтобы сохранить начальные​​Примечание:​​ уделить пару секунд​ ‘-КОНЕЦ БЛОКА_______________________ End​​ «0») Then GoTo​​ «: Sotni(4) =​

​ евро», IIf(Valuta =​ String Двадцатка =​

• ​ Поз, 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Миллионы​ значения находятся в​ которой применен такой​ Вы.​ Служ размещены вспомогательные​

​ числовых форматов​ значения в 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​ не используя функцию​В функции ТЕКСТ коды​

​ и нули в​ которые вы найдете​​ в текст, что​​ CStr(c) + d​

Почему Excel преобразует введенные числа во что-то вроде «1,22E+07»?

​ БЛОКА_______________________ Case 6​ = «тысяч «:​ EdinicyPoslednie(6) = IIf(Valuta​ «три » Case​ 7 strТысячи =​ Открывается окно редактора.​​ подход изложенный в​​ возвращает текстовое значение​Теперь все готово для​Кроме этого, можно воспользоваться​​ р.») вернет текстовую​​текст​ форматов используются без​ начале удалены, их​ в этой статье,​ может затруднить их​ 11: End Function​ ‘ — сотни​ mln(7) = «миллионов​​ = 0, «шесть​​ «4»: Двадцатка =​

Даты на разных языках

См. также

​ нажимаем F7. Вставляем​ формат (Формат ячеек).​

​ виде.​ исходное число (ячейка​

​VALUE​

support.office.com

Функция ТЕКСТ() в 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​ в файле примера.​ значения. В этом​

excel2.ru

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

​ИНДЕКС(Тысячи;ОСТАТ(ОТБР(B6/1000);1000)+1)&» «&​(ПРАВСИМВ) или любую​ в более удобном​ строки, пробела («»)​Если вы предоставляете доступ​=ТЕКСТ(значение;»00000″)​ текстовых функций в​ если потребуется создать​

1. ​ сумме прописью:​​ = Mid(SumInt, shag,​​ = «миллионов «:​ евро», IIf(Valuta =​​ «семь » Case​​ 1, 2), «тысяча​
2. ​ End With End​При импорте файлов или​ случае числу можно​​ИНДЕКС(Единицы;ОСТАТ(B6;1000)+1))​​ другую текстовую функцию,​ формате или если​
3. ​ и значения с​ к файлам и​, где количество нулей​ Excel​ другие формулы, всегда​​1-рубли;​​ 1) If vl​ mlrd(8) = «миллиардов​ 1, «семь рублей»,​ «8»: Двадцатка =​

​ «семь долларов»)) Edinicy(8)​​ «восемь » Case​ «тысяч «) ‘Единицы’​Чтобы он «заработал», нужно​ числовыми значениями часто​

​ формат. Ниже приведены​ осталось только разместить​​ из текстовой строки.​​ с текстом или​

​ & B2​ из разных стран,​​ символов. Затем скопируйте​​ форматов можно в​ значение, а не​0-евро;​ Mid(SumInt, shag +​ «девяносто «: Sotni(9)​ = «восемь «:​​ «9»: Двадцатка =​​ Поз = 10​ сохранить. Но книга​

​ возникает проблема: число​ примеры форматов, которые​
​ вспомогательные диапазоны в​
​ А затем, применив​

​ символами.​

office-guru.ru

Число прописью в 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 DataMicrosoftExcelXLStart, откуда​Запишем число прописью в​ для вывода числа​

​ячейка 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.​ в личной книге​

excel2.ru

Пользовательский ЧИСЛОвой формат в MS EXCEL (Функция ТЕКСТ())

​ для вывода в​К сожалению, это невозможно​Способ отображения времени можно​=—D4​выберите нужный формат.​формат​Теперь вы можете быстро​ shag, 1) If​ Case 12 ‘​ = 0, «одиннадцать​ «16»: Двадцатка =​ 1, 2), «рубль​Следовательно, значения в ячейках​ выравниваются по правому​

​ т.к. это текстовое​ В Windows Vista​ макросов. Кроме того,​ числовом формате с​ сделать с помощью​ изменить с помощью​.​Выберите пункт​Текстовая строка, определяющая формат,​ перевести сумму в​ shag > 2​

​ — сотни миллиардов​ евро», IIf(Valuta =​ «шестнадцать » Case​ «, «рубля «,​ «стали» числами.​ краю, текстовые –​ значение.​ эта книга хранится​ добавим руб./коп. для​ разделителями разрядов и​ функции​ сочетаний символов «Ч»​В Excel группы разрядов​

​(все форматы)​

​Самая простая функция ТЕКСТ​ рекомендуем скачать пример​ 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)​

excel2.ru

Преобразование текста в число в ячейке 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,​

1. ​ = «тринадцать «:​ End Function Function​ strЕдиницы ЧислоПропись =​ .NumberFormat = «General»​ ошибки. Ошибка также​5555,22​ сохраните ее в​ текст безумно скучно,​ дат (например, см.​ с помощью следующей​ «AM/PM», время будет​ как 12 200 000.​, кроме точки с​ скопировать прямо в​ из редактора.​
2. ​ = Edinicy(vl) &​ shag + 1,​ EdinicyPoslednie(13) = IIf(Valuta​ ИмяРазряда(Строка As String,​ UCase(Left(ЧислоПропись, 1)) &​ .Value = arr​ возникает, если перед​(+)5 555,22​ директорию C:Documents and​
3. ​ кроме того легко​ здесь). Также приведено​ ссылки:​ отображаться в 24-часовом​Пробел после заполнителя цифры​ запятой (;) и​ Excel, чтобы поэкспериментировать​Она работает, но выдает​ tys(vl) ‘ -​ 1) <> 0​ = 0, «тринадцать​ n As String,​ Right(ЧислоПропись, Len(ЧислоПропись) -​ End With End​ числом поставить апостроф.​»# ##0,00;# ##0,00;?»​ Settingsимя_пользователяApplication DataMicrosoftExcelXLStart (для​ ошибиться, поэтому давно​ много форматов .​Да, вы можете использовать​ формате.​
4. ​ задает деление числа​ символа @. В​ самостоятельно. Обратите внимание:​ текст на тайском​ вводим переменную Sclon_Tys​ Then GoTo 10​ евро», IIf(Valuta =​ Имя1 As String,​ 1) Exit Function​ Sub​Способов преобразования текста в​?​ XP) под именем​
5. ​ написаны макросы на​Функция ТЕКСТ() преобразует число​ функции ПРОПИСН, СТРОЧН​В функции ТЕКСТ коды​ на 1000. Например,​ примере ниже выделен​ коды форматов заключены​ языке. Возможно ли​ из-за иного склонения​ Else txt =​ 1, «тринадцать рублей»,​ Имя24 As String,​

​ Число_Error: MsgBox Err.Description​Читайте так же: как​ число существует несколько.​?​

​ PERSONAL.XLSB.​

Макрос «Текст – число»

​ 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​

exceltable.com

Как перевести сумму или число прописью в Excel

​ меню Вид/ Окно/​ (статичный вариант) приведено​ в качестве числа.​ несколько этапов. Сначала​ и «м», «С»​ в Excel как​, чтобы скопировать код​»# ##0,00 ₽»​Дмитрий колмычок​ » & tys(vl)​ то перескакиваем на​

​ евро», IIf(Valuta =​ «» Select Case​ Case n Case​ в число.​ ячейки с ошибкой​1 234 руб., 61​

1. ​ Отобразить отобразите ее,​
2. ​ решение этой задачи​ Чтобы отформатировать число,​ выделите одну или​ и «с», «AM/PM»​ 12 200,0.​ формата, а затем —​)​:​ ‘ — для​ единицы, иначе -​
3. ​ 1, «четырнадцать рублей»,​ Left(n, 1) Case​

​ 0: Сотни =​

​Таким образом, возникающую при​ слева появляется соответствующий​ коп​ скопируйте в нее​ позволяющей перевести число​ но при этом​ несколько ячеек, которые​ и «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»: Десятки =​Вставить в модуль следующий​ преобразуется в числовой.​ специальный условный формат.​в ячейке​ помощью вспомогательных диапазонов​и измените числовой​. Если вы вводите​

exceltable.com

Функция EXCEL ( BAHTTEXT преобразование числа в текст)

​ по себе и​Чтобы получить код формата​Числовые, денежные и финансовые​Экспоненциальное представление, например: 1,22E+07​ текст можно с​ 11 до 19​ — если конец​

​ mln(1) = «миллион​​ Integer: Dim txt,​
​ «пятьдесят «: 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, то периодически встречаете такие проблемы как выгруженные точки вместо запятых, неправильный формат данных, слепленные слова или значения и многое другое. На этот случай Excel предоставляет несколько возможностей по нормализации данных и у каждого из них есть свои плюсы и минуса.

Разобрать слитый текст на необходимые составляющие возможно произвести с помощью:

Мастер разбора текстов

Рассмотрим самый простой способ разделить текст в ячейке, не по сути, а по исполнению. Для примера, очень наглядно это можно продемонстрировать на ФИО в одной ячейке, которые необходимо разделить на отдельные столбики для удобства сортировки и фильтрации.

Для выполнения задачи вызываем диалоговое окно «Мастер текстов (разбор)» и в 3 шага разделяем текст:

1. Для начала нужно выделить данные, которые необходимо разделить, следующим шагом на вкладке «Данные» в разделе «Работа с данными» нажимаете иконку «Текст по столбцам» и в вызванном диалоговом окне мастера указываем формат рабочего текста. Выбираем 2 вида форматов:

• С разделителями – это когда существует текст или символ, который условно будет отделять будущее содержимое отдельных ячеек;
• Фиксированной ширины – это когда при помощи пробелов в тексте имитируется столбики одинаковой ширины.

1. Вторым шагом, в нашем примере, указываем символ, выполняющий роль разделителя. В случаях, когда в тексте идут подряд пару разделителей, несколько пробелов, к примеру, то установка флажка для пункта «Считать последовательные разделители одним» укажет для Excel принимать их за один разделитель. Дополнительное условие «Ограничитель строк» поможет указать, что текстовые значения, содержащиеся в кавычках не делить (к примеру, название фирмы «Рудольф, Петер и Саймон»);
2. Последним шагом, для уже разделённых столбиков, нужно указать в диалоговом окне мастера, предварительно выделив их, выбрать необходимый формат получаемых данных:

• Общий – не проводит изменения данных, оставляя их в первоначальном виде, будет оптимальным выбором в большинстве случаев;
• Текстовый – данный формат, в основном, необходим для столбиков с числовыми значениями, которые программа в обязательном порядке должна интерпретировать как текст. (К примеру, это числа с разделителем по тысяче или номер пластиковой карточки);
• Дата – этот формат используется для столбиков с датами, кстати, формат самой даты можно выбрать в выпадающем списке.

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

Рассоединяем текст с помощью формул

Для этого способа нам понадобятся возможности сочетаний функций ПОИСК и ПСТР. При помощи функции ПОИСК мы будем искать все пробелы, которые есть между словами (например, между фамилией, именем и отчеством). Потом функцией ПСТР выделяем необходимое количество символов для дальнейшего разделения.

И если с первыми двумя словами понятно, что и как разделять, то разделителя для последнего слова нет, а это значит что нужно указать в качестве аргумента условно большое количество символов, как аргумент «число_знаков» для функции ПСТР, например, 100, 200 или больше.

А теперь поэтапно рассмотрим формирование формулы для разделения текста в ячейке:

• Во-первых, нам необходимо найти два пробела, которые разделяют наши слова, для поиска первого пробела нужна формула: =ПОИСК(» «;B2;1), а для второго подойдет: =ПОИСК(» «;B2;C2+1);
• Во-вторых, определяем, сколько символов нужно выделить в строке. Поскольку позиции разделителя мы уже определили, то символов для разделения у нас будет на один меньше. Значит, будем использовать функцию ПСТР для изъятия слов, с ячейки используя как аргумент «количество_знаков» результат работы предыдущей формулы. Для определения первого слова (Фамилии) нужна формула: =ПСТР(B2;1;ПОИСК(» «;B2;1)), для определения второго значения (Имя): =ПСТР(B2;ПОИСК(» «;B2;1)+1;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1) -ПОИСК(» «;B2;1)), а теперь определим последнее значение (Отчество): =ПСТР(B2;ПОИСК(» «;B2;ПОИСК(» «;B2;1)+1)+1;100).

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

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

Выдергиваем слова с помощью макросов VBA

Рассмотрим два способа разделить текст в ячейке:

1. Выдергиваем отдельные слова по разделителю;
2. Делим текст без пробелов.

Способ №1.

Поскольку вас интересует автоматическое деление текста, значит надо написать хорошую функцию на VBA и внедрить ее в рабочую книгу. Для начала переходим на вкладку «Разработчик» и выбираем «Visual Basic» или вызываем эту возможность с помощью горячего сочетания клавиш Alt+F11. (детальнее в статье «Как создать макрос в Excel»).

Создаем новый модуль в меню «Insert» наживаем пункт «Module» и переносим в него нижеприведенный код:

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

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

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

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

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

Может быть задача проще — необходимо извлечь односоставной текст, убрав цифры вначале и в конце строки, учитывая, что сам текст всегда следует после разделителя(например, тире):
12.08-АГСВ2
12.08-АГСВ1
01.03-ОВ2
12.03-КЖ6.1
Из этих данных надо получить только текст после тире(-) и отсечь цифры на конце:
АГСВ
АГСВ
ОВ
КЖ
Формула будет работать почти по тому же принципу, что и формула выше, но она проще:
=ПСТР( A1 ;ПОИСК(«-«; A1 )+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР( A1 ;ПОИСК(«-«; A1 )+1;999);СТРОКА( $1:$99 );1));0)-1)
В данном случае мы при помощи ПОИСК(«-«; A1 ) ищем сначала позицию тире, далее при помощи ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР( A1 ;ПОИСК(«-«; A1 )+1;999);СТРОКА( $1:$99 );1));0) находим именно в отсеченном тексте позицию первой цифры. Передаем эти значения в ПСТР , которая отбирает из этого текста все от первого тире(+1) до первого числа, идущего после текста.

СПОСОБ 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

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

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

Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:

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

Выберите ячейку для вывода данных(Лист1! $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, 12 857 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

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

Александр, последние 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 «Текст по столбцам».

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

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

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

Содержание

• 1 Пример 1. Делим текст с ФИО по столбцам с помощью формул
• 1.1 Приступаем к делению первой части текста — Фамилии
• 1.2 Приступаем к делению второй части текста — Имя
• 1.3 Приступаем к делению третьей части текста — Отчество
• 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Пример 1. Делим текст с ФИО по столбцам с помощью формул Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях.

В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера. У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.

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

Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу

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

Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст.

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

Функция будет выглядеть следующим образом:

Далее протягиваем формулу и получаем позиции 1-го и 2-го пробела.

Приступаем к делению первой части текста — Фамилии

Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:

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

• текст — это ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
• число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

Формула будет выглядеть следующим образом:

Приступаем к делению второй части текста — Имя

Снова используем функцию =ПСТР(текст- начальная_позиция- число_знаков), где

• текст — это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
• число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2

Получаем итоговую формулу:

Приступаем к делению третьей части текста — Отчество

И снова функция =ПСТР(текст- начальная_позиция- число_знаков), где

• текст — это тот же текст ФИО, в нашем примере это ячейка A2;
• начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
• — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50

Видео: Формулы в Эксель

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

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией

• Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
• Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
• Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

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

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

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

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

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

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

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

Теперь основная суть приема.

Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки

То есть ищем запятую, в тексте, начиная с позиции 1

Шаг 2. Далее в ячейке E1 прописываем формулу для нахождения второго знака (в нашем случае запятой). Формула аналогичная, но с небольшими изменениями.

1. Во-первых: закрепим столбец искомого значения и текста, чтобы при протягивании формулы вправо ссылки на ячейки не сдвигалась. Для этого нужно написать доллар перед столбцом B и A — либо вручную, либо выделить A1 и B1, нажать три раза клавишу F4, после этого ссылки станут не относительными, а абсолютными.
2. Во-вторых: третий аргумент — начало позиции мы рассчитаем как позиция предыдущего разделителя (мы его нашли выше) плюс 1 то есть D1+1 так как мы знаем, что второй разделитель точно находится после первого разделителя и нам его не нужно учитывать.

Пропишем формулу и протянем ее вниз.

Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее

Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.

Начальная позиция у нас 1, количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз

Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1

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

Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1 Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.

Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.

Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением.

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

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

Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя

а первого текста как

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

В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.

Внимание! В комментариях заметили, что так как в конце текста у нас нет разделителя, то у нас не считается количество символов от последнего разделителя до конца строки, поэтому последний разделенный текст отсутствует.

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

Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.

Таким образом последний текст будет рассчитываться начиная от последней запятой до чуть меньше 1000 знаков, то есть до конца строки, что нам и требуется.

Оба варианта выложу для скачивания.

1. Скачать пример: Как разделить текст по столбцам с помощью функции_1.xlsx (исправлено: доп поле)
2. Скачать пример: Как разделить текст по столбцам с помощью функции_2.xlsx (исправлено: заведомо большое число)

(Пока оценок нет)

Как разделить текст по ячейкам формула в Excel

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

Как разделить текст на две ячейки Excel

Допустим на лист Excel были импортированные данные из другой программы. Из-за несовместимости структуры данных при импорте некоторые значение из разных категорий были внесены в одну ячейку. Необходимо из этой ячейки отделить целые числовые значения. Пример таких неправильно импортированных данных отображен ниже на рисунке:

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

В ячейку B3 введите следующую формулу:

Теперь скопируйте эту формулу вдоль целого столбца:

Выборка чисел из строк в отдельные ячейки.

Описание формулы для разделения текста по ячейкам:

Функция ПСТР возвращает текстовое значение содержащие определенное количество символов в строке. Аргументы функции:

1. Первый аргумент – это ссылка на ячейку с исходным текстом.
2. Второй аргумент – это позиция первого символа, с которого должна начинаться разделенная строка.
3. Последний аргумент – это количество символов, которое должна содержать разделенная строка.

С первым аргументом ПСТР все понятно – это ссылка на ячейку A3. Второй аргумент мы вычисляем с помощью функции НАЙТИ(«]»;A3)+2. Она возвращает очередной номер символа первой закрывающейся квадратной скобки в строке. И к этому номеру мы добавляем еще число 2, так как нам нужен номер символа после пробела за квадратной скобкой. В последнем аргументе функция вычисляет какое количество символов будет содержать разделенная строка после разделения, учитывая положение квадратной скобки.

Обратите внимание! Что в нашем примере все исходные и разделенные строки имеют разную длину и разное количество символов. Именно поэтому мы называли такую формулу – гибкой, в начале статьи. Она подходит для любых условий при решении подобного рода задач. Гибкость придает ей сложная комбинация из функций НАЙТИ. Пользователю формулы достаточно определить закономерность и указать их в параметрах функций: будут это квадратные скобки либо другие разделительные знаки. Например, это могут быть пробелы если нужно разделить строку на слова и т.п.

В данном примере функция НАЙТИ во втором аргументе определяет положение относительно первой закрывающейся скобки. А в третьем аргументе эта же функция вычисляет положение нужного нам текста в строке относительно второй открывающийся квадратной скобки. Вычисление в третьем аргументе более сложное и оно подразумевает вычитание одной большей длинны текста от меньшей. А чтобы учитывать еще 2 пробела следует вычитать число 3. В результате чего получаем правильное количество символов в разделенной строке. С помощью такой гибкой формулы можно делать выборку разной длинны разделенного текста из разных длинны исходных строк.

Извлечение слов из текста в Excel

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

Важное замечание!

Задача 1. Подсчет количества слов в тексте

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

Итак, слова в тексте отделены друг от друга пробелами. Пробелов всегда на 1 меньше, чем слов (действительно, между двумя словами — один пробел, между тремя — два и т.д.). Значит, достаточно подсчитать пробелы и прибавить к ним единицу, чтобы узнать число слов в ячейке.

В Excel есть замечательные функции ДЛСТР() и ПОДСТАВИТЬ() . Первая подсчитывает количество символов в указанной ячейке, а вторая умеет заменять указанный символ на любой другой или на пусто (обозначается как двойные кавычки без пробела между ними «» ).

Функция ПОДСТАВИТЬ() имеет 4 аргумента:

1) Ячейка с текстом

2) То, что нужно заменить (указывается в кавычках)

3) То, на что нужно заменить (указывается в кавычка)

4) Необязательный аргумент — номер вхождения заменяемого текста. То есть, если в тексте 4 пробела, мы можем заменить только тот, номер которого укажем. Если аргумент опущен — заменяются все символы.

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

Прибавим к ней единицу — и получим число слов в ячейке.

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

Задача 2. Извлечение первого слова из текста

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

Найти символ в тексте поможет функция НАЙТИ() . У нее есть 3 аргумента:

1) Искомый текст;

2) Текст, в котором ищем;

3) С какого символа начать поиск (необязательный, если опущен — функция ищет с самого начала).

Результатом работы функции является позиция первого символа найденного текста в строке. Например, формула =НАЙТИ(«плюс»;»Один плюс один») вернет в результате число 6 (именно с этой позиции начинается искомое слово «плюс»). Если в строке несколько раз содержится искомое слово, то вернется результат для первого вхождения.

Функция НАЙТИ работает с учетом регистра и без подстановочных знаков. Если нужно их использовать, или не учитывать регистр — есть аналогичная функция ПОИСК() .

Теперь мы можем найти позицию первого пробела

Извлечь символы до пробела поможет функция ЛЕВСИМВ . Ей нужно указать текст, а также число символов, которое нужно извлечь начиная с первого. Если найденный пробел, например, 6-ой символ, то нам нужно извлечь 6-1=5.

Формула для извлечения первого слова:

Если в строке одно слово (то есть нет пробела между первым и вторым словом), данная формула вернет ошибку. Чтобы этого избежать, нужно добавить к ней функцию ЕСЛИОШИБКА() .

Теперь формула корректно сработает для любого числа слов в ячейке.

Задача 3. Извлечение последнего слова из текста

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

• подсчитаем, сколько в ячейке пробелов, воспользовавшись приемом из Задачи 1.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

• с помощью функции ПОДСТАВИТЬ заменим последний пробел в тексте на символ, которого там точно нет (например, «*» или «$»). Чтобы заменить именно последний пробел, добавим в качестве необязательного аргумента формулу из предыдущего пункта (то есть число пробелов, так как оно равно номеру последнего пробела)

=ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))

• теперь, когда последний пробел заменен на «*», мы можем узнать его позицию с помощью функции НАЙТИ() . В качестве текста, в котором ищем, укажем предыдущую формулу

=НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

• если из общей длины текста мы вычтем найденную позицию звездочки, то получим число символов после звездочки (то есть длину последнего слова в ячейке)

=ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

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

Как и в предыдущей задаче, можно обернуть в ЕСЛИОШИБКА , чтобы верно работало с ячейками, в которых только 1 слово.

=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«*»;ПОДСТАВИТЬ(A1;» «;»*»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))));A1)

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

Function РЕВЕРС(str As String)

Это макрофункция. Теперь, используя ее, можно отразить текст зеркально, извлечь первое слово, как в Задаче 2 и отразить его обратно. Формула:

Берите на вооружение, если любите макросы.

Задача 4. Извлечение n-ного слова из текста

Самый интересный пример. Для решения подобной задачи нужно применить фантазию. Разберем решение поэтапно.

Помимо уже использовавшихся в статье функций, нам понадобятся еще 2:

1) ПОВТОР() . Умеет повторять указанный текст указанное число раз.

2) ПСТР() . Имеет 3 аргумента:

• исходный текст;
• позиция, с которой нужно начать извлечение;
• число символов, которые нужно извлечь.

Теперь мы вооружены всем нужным инструментарием. Приступаем:

• Для начала воспользуемся функцией ПОВТОР() , чтобы вместо 1 пробела в тексте вставить число пробелов, равное длине исходного текста. Количество повторений узнаем через уже известную функцию ДЛСТР() . Итак, формула:

=ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)))

• Теперь каждое слово отделено друг от друга числом символов, равным общей длине строки. Чтобы извлечь слово под номером n, нам нужно узнать позицию какого-нибудь пробела между словом n и n-1 в обработанном удлиненном тексте. Это может сделать формула ниже (+1 в ней нужно для правильного поиска первого слова):

=ДЛСТР(A1)*(n-1)+1

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

• Теперь мы может извлечь из найденной позиции число символов ДЛСТР(A1) (на рисунке — m ) и извлеченный текст всегда будет захватывать нужное нам слово целиком (с некоторым количеством предшествующих и последующих пробелов)

=ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1))

• Осталось избавиться от лишних пробелов функцией СЖПРОБЕЛЫ()

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;ДЛСТР(A1)));ДЛСТР(A1)*(n-1)+1;ДЛСТР(A1)))

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

Задача 5. Извлечение имя файла из полного пути к нему

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

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

Берем формулу из задачи 3 и заменяем в ней пробелы на «».

Получим следующую формулу:

Вставляем в ячейку и вуаля! Имя файла извлечено.

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

Поддержать наш проект и его дальнейшее развитие можно вот здесь .

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

Функция ТЕКСТ преобразует численное значение в текст и позволяет задать формат отображения с помощью специальных строк форматирования.

Описание функции ТЕКСТ

=ТЕКСТ(A1,"0,00р.")

=ТЕКСТ(A1,"0,00р.")& " в час"

Синтаксис

=ТЕКСТ(значение; формат)

Аргументы

Форматы чисел

Если в дробной части числа больше цифр, чем заполнителей в формате, число округляется до количества разрядов, равного количеству заполнителей. Если в целой части числа больше цифр, чем заполнителей в формате, отображаются дополнительные цифры. Если формат содержит слева от запятой только знаки номера ( #), то числа, меньшие 1, будут начинаться с запятой, например ,47.

Разделитель групп разрядов

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

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

Часы, минуты и секунды

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

Форматы денежных сумм, процентов и экспоненциального представления

Символы валют. Чтобы указать перед числом знак доллара ($), введите его в начале аргумента формат (например, «$# ##0,00»). Чтобы включить в числовой формат один из указанных ниже символов валют, нажмите клавишу NUM LOCK и введите с цифровой клавиатуры код ANSI соответствующего символа.

• Примечание.   Необходимо использовать цифровую панель клавиатуры. При использовании клавиши ALT с цифрами верхнего ряда клавиш коды ANSI не генерируются.
• Процентные значения.    Чтобы отображать числа в виде процентов — например, 0,08 как 8% или 2,8 как 280%, — включите в аргумент формат знак процента (%).
• Экспоненциальное представление.    Для отображения чисел в экспоненциальном формате включите в аргумент формат код из числа указанных ниже.

Включение текста и добавление пробелов

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

Замечания

• Аргумент формат не может содержать звездочку (*).
• Функция ТЕКСТ преобразует число в форматированный текст, и результат больше не может быть использован в вычислениях в качестве числа. Чтобы отформатировать число и при этом оставить его числом, щелкните ячейку правой кнопкой мыши, выберите команду Формат ячеек и в диалоговом окне Формат ячеек на вкладке Число настройте нужные параметры форматирования. Для получения дополнительных сведений об использовании диалогового окна Формат ячеек нажмите кнопку справки (?) в верхнем правом углу окна.

Пример

Видео работы функции

Дополнительные материалы

Файл Excel с примерами использования функции ТЕКСТ (на английском языке)