Как отделить цифру от числа в excel - Word и Excel - помощь в работе с программами

0 / 0 / 0

Регистрация: 08.09.2012

Сообщений: 3

Как отделить одну цифру числа от других

08.09.2012, 21:03. Показов 14675. Ответов 2

Как автоматически убрать первую цифру 7 от 11-ти значного числа , вот пример пример.xlsx

Заранее благодарен

Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782	08.09.2012, 21:03
2

2633 / 1333 / 255

Регистрация: 25.10.2010

Сообщений: 2,194

08.09.2012, 22:15

0 / 0 / 0

Регистрация: 08.09.2012

Сообщений: 3

08.09.2012, 22:18

[ТС]

ты супер ,спасибо за помощь

Источник

Разделить текст в ячейке Excel по столбцам.

Смотрите также вернуть!!! привык както формулами  использовать одну (две) не вся номенклатура. бампера следующая количество символов. Именно Из-за несовместимости структуры_Boroda_Очень нужно !!! получить, в соотв. заменой не получается, С1 можно ввести
кода используются номера кредитных  них сотрется и работают формулы вРассмотрим,
GIG_ant обходится, если формулой
UDF вместо кучи А там где ячейка E771SXA000 поэтому мы называли данных при импорте
: Если сначала всегдаТо что есть: с Правилами
 т.к эксель принимает
формулу0000000000 карт или другие заменится на новую. Excel».как разделить текст в: ту Михаил:
никак низя подскажите стандартных формул.
код попадался передФайл с данными такую формулу – некоторые значение из «Отд. №»То что нужно
Амортизатор задний 271.00.00 * за универсальный=ПСТР(A1;НАЙТИ(» «;A1)+1;ДЛСТР(A1))
, благодаря чему для
числовые коды, содержащие Если нет пустыхВ Excel есть ячейкахПонял!!! Вы хитро плз. как вставить
И кстати макросы текстом, так это прилагаю. Помогите очень гибкой, в начале разных категорий были=ПСТР(E25;7;9)Иван леконтцев
плаза — код символ-заменитель :(
которая нам вернёт
отображения всех цифр 16 цифр или столбцов, можно предварительно несколько разных способовExcel по столбцам формулой
сделали цикл функцией пользовательскую функцию в разрешать не нужно, и можно руками надо… статьи. Она подходит
внесены в однуЕсли нужно не: текст с числами «271.00.00» или «271.00.00Guest текст после кода десять кода продукта,
более, необходимо использовать вставить столбцы. Как сгрупприровать данные в. В Excel есть ПСТР(A1;СТРОКА($1:$30);1) добавив формулу ексель так что если UDF в исправить, а весьsulfur для любых условий ячейку. Необходимо из текстом, а числами

excel-office.ru

Разделить текст Excel на несколько ячеек.

в столбце А. плаза»?: Смотрите текстовые функции.Подходит, нет? включая ведущими нулями текстовый формат. Это это сделать, смотрите таблице по нашим специальная функция –
массива. Значит цикл бы она была Personal.xls или как текст даже тот: во-первых, если у при решении подобного этой ячейки отделитьКод=—ПСТР(E25;7;9) результаты в столбцахGuest Например =ПОДСТАВИТЬ()sergejkov в Excel. Дополнительные вызвано тем, что
в статье «Как условиям, например, по «Текст по столбцам». все же можно
в каждой новой у меня в кусок в 3 вас так мало рода задач. Гибкость целые числовые значения.vitaliyms В и С.: Побольше бы данных,kim: Pavel55 , вау!
сведения об этой в Excel используется добавить строку, столбец датам, по определенному Где она находится организовать(хоть и с книге и не MyFunc.xls: тыс позиций, очень номенклатуры, то ее придает ей сложная Пример таких неправильно: А бывает ещёв ячейках столбца хочется посмотреть из: Для замены # на простом столбце проблеме читайте в точность до 15
в Excel» тут. периоду, шагу цен, и как с такими наворотами, но спрашивала постоянно про =MyFunc.xls!ExtractString(A1) много времени занимает. проще вручную перебрать комбинация из функций импортированных данных отображен и так В прописываете такую чего код состоит. на пусто можно все получилось! статье Создание или цифр. Любые цифрыМожно указать формат
сумм, т. д. ней работать, смотрите можно !). Благодарю. включение макросов.=MyFunc.xls!ExtractNumber(A1) Так что спасибово-вторых, у нас НАЙТИ. Пользователю формулы
ниже на рисунке:_Boroda_
формулу: ..И формат желательно =ПОДСТАВИТЬ(A1;»#»;»»)ОГРОМНОЕ СПАСИБО!!! удаление пользовательского числового после 15-й округляются Ячеек столбцов. Например,

Как это сделать, в статье «Разделить Беру формулу наHugoА формулы тут всем огромное кто наименования не стандартизованы. достаточно определить закономерностьСначала определим закономерность, по: А почто сразу
=ЛЕВСИМВ (A1;НАЙТИ (« *.xlsДля звездочки =ПОДСТАВИТЬ(A1;»*»;»»)попробую это на формата с сохранением до нуля, что, если вы делите смотрите в статье
текст Excel на вооружение.: Если у Вас в копилке должны помог. Есть и такие, и указать их которой можно определить,

excel-office.ru

Форматирование чисел в виде текста

 не показываете все «;A1)-1)ВладимирПро артикулы не более сложных столбцах начальных нулей в наверняка, будет неприемлемым. столбец на дни «Как группировать в несколько ячеек» тут.ЗЫ: Не в ещё нет Personal.xls, быть, или вот,Добрый день, подскажите где номер идет в параметрах функций: что данные из варианты?в ячейках столбца: Это я был. понял… ;)

числовых кодах.Если число имеет текстовый и месяцы, год, Excel».Здесь рассмотрим, обиду Hugo, ваш то его проще несколько версий: пожалуйста как дополнить в начале строки. будут это квадратные разных категорий, несмотря=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($E242;»Цех №»;ПОВТОР(» «;999));999*СТОЛБЕЦ(A242);999)) С другую:GuestkimsergejkovВ некоторых случаях номера формат, это легко

то можно указатьЗдесь рассмотрим,как разделить в пример с макросом всего создать такFunction GetNumeric(t As вышеприведённый код, что

Кто знает что скобки либо другие на то, чтоvitaliyms=ПРАВСИМВ (A1;ДЛСТР (A1)-ДЛСТР

: Пишет ИМЯ?: А кажись понял.: на более сложных может отформатирован и определить, поскольку число «Формат данных столбца»как разделить текст в
Excel формулой текст числа, тоже вполне подходит. — включаете запись Range) б цифры переносились там еще у разделительные знаки. Например, они находятся в: Прошу прощения но (B1)-1)

Guest Так? данных не работает хранящиеся в ячейках, будет выровнено в — «дата». ячейке Excel на

коды на составляющиеHugo

 макроса, как местоDim j As

не только с вас попадается. это могут быть одной и той нашёл ещё вариантпосле копируйте ячейки
: Вот примерsergejkov — надо как текст, который ячейке по левомуНажимаем кнопку «Готово». несколько ячеек..: Чего обижаться. Я сохранения макроса выбираете Integer, l As латинскими буквами ноЯ бы на пробелы если нужно же строке. В (я просто разбираюсь и обратно вставляетеvikttur: вот теперь так отделить именно цифры(артикул) позже может привести краю, а не Получилось так.Например, предложение изУ нас такая б такую формулу Personal.xls, записываете простой String и с русскими, вашем месте раскошелился разделить строку на нашем случае нас с новым документом но уже через: Вас давно просят как нужно :)
в начале строки, к проблемам с по правому.Так можно разделить текст первой ячейки разнести таблица с данными. не в жисть макрос (например, пишитеFor j = и как можно на услуги фрилансера, слова и т.п. интересуют только числа, поэтому не все спец. вставку только четкие критерии кода. спасибо! а не просто вычислениями или ФруктыВыделите ячейку или диапазон
из одной ячейки по словам вВ столбце А написан не сочинил :) в А1 что-нибудь). 1 To Len(t) убрать лишние перенесенные т.к. это задачаВ данном примере функция которые находятся вне варианты сразу выложил)

support.office.com

как разделить столбец на цифры и текст

 значения. возможно потребуетсяИз «Подшипник 180202куда дэньги слать
первыймассив символов :( понятнее порядки сортировки.
ячеек, содержащий числа, на большее количество
несколько других ячеек, код, составленный через

Макросом проще, и
Останавливаете запись.If IsNumeric(Mid(t, j, символы, например убрать коммерческая и занудная. НАЙТИ во втором
квадратных скобок. Какимvikttur удалить лишнии пробелы… генератора» удалится «генератора», ;)иначе при отсутствии

 Это иногда происходит которые требуется отформатировать столбцов. Например, здесь ФИО, дату, т.д.

слеш из кода контроля больше -Теперь через Alt+F11 1)) Then l первый дефис: -ПС-12212 Мало кто захочет аргументе определяет положение

способом можно быстро

: Это у когоВадим гусев

Вам так иps вопрос про артикула в строке

при импорте или

как текст. Выделение разделили дату отдельно

Например, у нас

 и года. Нам можно любые знаки заходите в редактор, = l &
Sub CodeToNextColumn() Dim
решать ее для относительно первой закрывающейся выбрать из строк

 же такие кривые: Это делается еще нужно? *# , *## из наименования пропадает копировании чисел из ячейки или диапазона. по столбцам –
есть список с нужно их разделить. препинания в любую слева в окне
Mid(t, j, 1) cell As Range, вас бесплатно скобки. А в

 целые числа и рученьки? Так испортить
до Экселя.Guest
пока открыт. первое слово :( базы данных или
Совет: число, месяц, год. фамилиями, именами вВ ячейке В27 группу добавить. Иногда
пректов открываете свойNext j rgX As Object

deniking третьем аргументе эта
поместить их в данные — этоОткрываете эксель и

: Работает. Только мнеСмысл в томможет быть есть другого источника данных.
Можно также выделить пустые
Для большей наглядности

 одном столбце. Нам пишем такую формулу.

 бывает нужно. Personal.xls, в модуле

GetNumeric = Val(l) Set rgX =: Здравствуйте, же функция вычисляет
отдельные ячейки? Эффективным еще уметь нужно.

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

=ЗНАЧЕН(ЛЕВСИМВ(A27;НАЙТИ(«/»;A27;1)-1))vikttur удаляете свой ненужныйEnd Function CreateObject(«VBScript.RegExp») rgX.IgnoreCase =deniking положение нужного нам решением является гибкаяЛогика: унифицируем разделитель нужный документ (я не коды.

 в конце строки на буквы и необходимо преобразование чисел

 как текст, а можно выделить некоторые в одном столбце,Пояснение к формуле.
: Вторую формулу можно
макрос, вместо негоPublic Function ExtractNumber(S

 True ‘Ищем без. Это классическая задача текста в строке

 формула основана на между текстами и так понял -Есть мысли?
указано количество еиниц цифры?

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

Guest в упаковке(*6, *24sergejkov обратно в числовой.

 Такие числа будут итоговые) выделить другим другом столбце. Например, мы написали «-1».

=ПРАВСИМВ(A1;ДЛСТР(A1)-ДЛСТР(E1))Теперь эти функцииDim i As
(есть номера с Вот решение:
квадратной скобки. Вычисление

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

Guest доступны в любой
Integer, str As буквами в нижнем
Sub CodeToNextColumn() Dim в третьем аргументе
следующую формулу:

на пробел) и окно импорта данных.

 таких позиций непод # я9450/4450 ХИЛЛс ДИЕТА чисел из текстовогоНа вкладке Подробнее, смотрите в
людей в списке. чтобы знак слеша: или еще проще: книге под эти String регистре) rgX.Pattern =

cell As Range, более сложное иТеперь скопируйте эту формулу извлекаем между пробелами

 В нем будет очень много. подразумевал цифры. еще кон.д/кошек S/D 156гр*24 формата в числовой.Главная

 статье «Разделительная линия Подробнее об этом

 не писался с=ПОДСТАВИТЬ(A1;E1;)

 пользователем на этомFor i =

 «[-A-Zd]{2,}» ‘Номер - rgX As Object оно подразумевает вычитание
вдоль целого столбца: каждое второе слово: возможность задать типДумаю просто их

 бывают варианты /#5280 ХИЛЛc НБМожно также использовать функциюв группе
строк в таблице

 читайте в статье кодом в столбцеDV_68 компьютере в разделе
1 To Len(S) это последовательность не

 Set rgX = одной большей длинныВыборка чисел из строк=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(» «&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($E244;»№»;» «);СИМВОЛ(10);» «));» разделителей полей.
так4 и оставить и /## Корм сух.д/КОТЯТ Курица/овощи текст для преобразованияЧисло

Excel» тут. «В Excel по В.
: Можно пойти от «Определённые пользователем».If InStr(1, «1234567890,», менее 2-х символов,
CreateObject(«VBScript.RegExp») rgX.IgnoreCase = текста от меньшей. в отдельные ячейки.

 «;ПОВТОР(» «;99));99*СТОЛБЕЦ(A244)*2;99))Smvikttur
вот :( 300гр*6 числа в текстщелкните стрелку рядомМожно разделить ячейку
имени определить пол».Копируем формулу по обратного, тогда без

Макросы можно не Mid(S, i, 1)) состоящая ‘из дефисов,
True ‘Ищем без

 А чтобы учитывать
_Boroda_
: А что, в
: Пытать Вас, что
kim
2806 12589 Brit

в заданном числовом с полем по диагонали и,
Выделяем ячейки, текст

planetaexcel.ru

Как в Excel отделить текст от цифры в одной ячейке и перенести из по другим. (в пояснении скрины)

столбцу. Получится так.

массива.

 разрешать, по крайней <> 0 Then латинских букв и различения по регистру еще 2 пробела
Функция ПСТР возвращает текстовое: Охохохонюшки славной Курдюмовке таперича
ли? Что предпочитаете: Я так понимаю,
Care Cat Light формате. Примеры этот
Числовой формат в каждой части
в которых нужноВ столбце С устанавливаемСначала цифры: мере на 2000 str = str цифр. Application.ScreenUpdating =

 (есть номера с следует вычитать число значение содержащие определенное
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(» «&СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($E244;СИМВОЛ(10);» «);»№»;» «));» не модно различать — средневековье или Вы вообще хотите д/стареющих кошек, склонных
способ сохранения начальныхи выберите пункт написать текст. Читайте, разделить или весь формат «Дата». Какими

=—ПРАВСИМВ(A1;СЧЁТ(ПОИСК({0:1:2:3:4:5:6:7:8:9};A1))) так, может на & Mid(S, i, False ‘Отключаем обновление буквами в нижнем 3. В результате
количество символов в «;ПОВТОР(» «;99));99*СТОЛБЕЦ(A244)*2;99)) понятия «число» и
электрический стул? :) от них избавиться?
к полноте всех нулей в числовых
Текстовый

 как это сделать, столбец. способами установить формат,Затем остальное: 2003/7/10 иначе, не 1) экрана для ускорения регистре) rgX.Pattern = чего получаем правильное строке. Аргументы функции:_Boroda_ «цифра» ?Вы написали, что

Отделить в ячейки буквы от цифр (Формулы/Formulas)

Guest пород 7,5кг кодахсм. Дополнительные сведения
. в статье «КакЗаходим на закладке «Данные» смотрите в статье=ПОДСТАВИТЬ(A1;C1;) знаю.Next

 процесса. Set cell «[-A-Zd]{2,}» ‘Номер -
количество символов вПервый аргумент – это

: Виктор> В нем формула работает, значит,
: Про звездочки вам

ПРО ПАК БИСКВИТ об использовании функцииПримечание:
разделить ячейку в
в раздел «Работа «Число Excel. Формат»
DV_68

GIG_antExtractNumber = str = Cells(1) ‘Начинаем

 это последовательность не разделенной строке. С ссылка на ячейкуvikttur
будет возможность задать

 кодом является все, ведь давно ответили. Смат Риводс Мини текст отображается функция Если пункт Excel» здесь. с данными» и

 здесь.: Поторопился, при одинаковых: Спасибо Hugo.End Function с верхней левой менее 2-х символов,
помощью такой гибкой с исходным текстом.: Это же надо… тип разделителей полей что после последнего Ответы читали? 200гр
текст.Текстовый

Удобнее работать в выбираем функцию «Текст
В ячейке С27 цифрах не пройдет((

Буду пробывать, яPublic Function ExtractString(S beer beer beer

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

 Одинаковой дорогой идете,А как быть hands пробела?!

Guestkimsergejkovне виден, прокрутите

excelworld.ru

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

таблице, где в по столбцам». В пишем такую формулу.kim понимаю что язык As String) While Not IsEmpty(cell) латинских букв и выборку разной длинны позиция первого символа, товарищи с»Подшипник 180202 генератора»: Даже не знаю: =ЕСЛИ(ЕЧИСЛО(—ЛЕВСИМВ(A1;1));ЛЕВСИМВ(A1;МИН(ПОИСК(«/»;A1);ПОИСК(» «;A1))-1);A1): Имеем столбец с список до конца ячейках не видны появившемся окне «Мастер =ДАТАЗНАЧ(ПСТР(A27;НАЙТИ(«/»;A27;1)+1;255)): Можно еще: программирования намного гибчеDim i As ‘Идем по ячейкам

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

цифр. Application.ScreenUpdating = разделенного текста из с которого должнаvitaliymsс. Бiльшая Курдюмiвка — где здесь как Вас отблагодарить!Для первого столбца. подобным содержаним: с помощью полосы нули. Как их текстов», в строкеПояснение в формуле.=—ПРАВСИМВ(A1;СЧЁТ(ИНДЕКС(-ПСТР(A1;СТРОКА($1:$99);1);)))

чем стандартные формулы Integer, str As вниз, пока не False ‘Отключаем обновление разных длинны исходных начинаться разделенная строка.: 00789 текст, где код?Это именно то, По аналогии для0202 Гантеля резиновая прокрутки. убрать, скрыть, заменить, «Формат исходных данных»В формуле написали=ПРОСМОТР(9E+307;—ПРАВСИМВ(A1;СТРОКА($1:$15))) екселя, и все String дойдем до пустой.

экрана для ускорения строк.

Последний аргумент – этоСПАСИБО

?)»Подножки Universal Great

что нужно!

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

второго напишите сами? 150 ммЧисловой формат списка читайте в статье

указываем – «С «+1» — этоIvian
таки жалко чтоFor i = ‘Если ячейка - процесса. Set cell
deniking количество символов, котороеbuchlotnikСаша русанов

Wall Hover» -Оромнейшая Вам благодарность!kim020543 Кольцо резиновое в Excel Online «Как убрать нули разделителями». Нажимаем кнопку значит, чтобы в: Здравствуйте! Столкнулся с есть такие задачи 1 To Len(S) не объединенная и = Cells(1) ‘Начинаем: Доброго времени суток. должна содержать разделенная: до кучи на: Встроенный бейсик в здесь, похоже, безМаксим: Кстати если для 70 ммСоветы: в Excel».

«Далее». ячейке с датой проблемой изъятия цифр которые невозможно реализоватьIf InStr(1, «QWERTYUIOPASDFGHJKLZXCVBNM,.-<>=*/ в ней есть с верхней левой Помогите человеку, который строка. регулярках с паттерном нем напиши программу кода?: Помогите разобраться. Нужно первой строки правильно02064 Кольцо резиновое Примечание:Здесь указываем в строке не был написан из текста (формат только силами ексель(без «, UCase(Mid(S, i, номер согласно шаблону ячейки активного листа. в VBA неС первым аргументом ПСТР

«d+» читаешь ячейку распознаешь»Подрамник двигателя на удалить код наименований так — 9450/4450 80 ммЧтобы использовать десятичные знакиМы стараемся как «Символом – разделителем слеш (косая черточка). ячейки: общий). Покопав ВБА). Может в 1))) <> 0 — копируем номер While Not IsEmpty(cell) шарит, а слышал все понятно –Часто приходится оптимизировать структуру знаки по кодам BQб» — с товаров который стоитто достаточноПодскажите пожалуйста: в числах, хранящихся можно оперативнее обеспечивать является» — «пробел».«255» — это форум нашел эту будущем разработчики включат

exceltable.com

Нужен макрос для Excel, отделяющий текст от цифр

 Then str = вправо. If Not ‘Идем по ячейкам только поверхностно. это ссылка на данных после импорта
в ansi и этим что? вконце. в exel=ЕСЛИ(ЕЧИСЛО(—ЛЕВСИМВ(A1;1));ЛЕВСИМВ(A1;ПОИСК(» «;A1)-1);A1)Как разделить цифровой как текст, возможно, вас актуальными справочными Поставили «пробел», потому количество символов, которые тему и вот в стандарт какие
str & Mid(S, cell.MergeCells And rgX.Test(cell)
вниз, пока неЗадача такая, в
ячейку A3. Второй в Excel. Некоторые
вставляешь или в
Guest не очень силен,Dophin
код(часто кроме цифр придется вводить эти материалами на вашем
что в нашем могут отобразиться в данная комбинация сработала:
нибудь простейшие циклы i, 1) Then cell.Offset(, 1)

 дойдем до пустой. exel есть список аргумент мы вычисляем разные значения попадают одну ячейку или
: даже если так разбираюсь по-тихоньку, поэтому: давайте пример, а?) содержит «/») и числа с десятичными языке. Эта страница списке имя и ячейке. По факту,
Но все испортил (или запросы) вотNext = rgX.Execute(cell)(0) Set ‘Если ячейка - деталей которые с с помощью функции в одну и

 в другую (цифры всё будет работать,замечательно. лучше готовую формулу.kim текста в разные разделителями.
переведена автоматически, поэтому фамилия написаны через отображается столько символов, / (слэш). Число тогда было быExtractString = Application.Trim(str) cell = cell.Offset(1) не объединенная и номером находятся в НАЙТИ(«]»;A3)+2. Она возвращает туже ячейку образуя в одном диапазонепросто на самом Очень нада. Формат: Приблизительно так… столбцы?При вводе номер, который ее текст может пробел. Если бы сколько есть в выходит совсем другое. полегче!End Function Wend Application.ScreenUpdating = в ней есть одной ячейке, номер очередной номер символа целую строку как таблицы, русские буквы деле есть и у всех ячеекsergejkovpsкакто я виде начинается с нуля содержать неточности и имя и фамилия дате (после слеша).Надо изъять цифрыМихаил С.Function NumbersOnly(srcStr As
True ‘Включаем обновление номер согласно шаблону состоит из цифр первой закрывающейся квадратной

 одно значение. Возникает вроде за 180 позиции без номеров, примерно такой: совсем не на форуме подобный — например, кода грамматические ошибки. Для (др. слова) былиКопируем формулу по из подобной строки:: К данной задаче String) As String экрана. End Sub — копируем номер и символов латинского скобки в строке. вопрос: как разбить

и далее) есть номера передАктиватор замка электрический понимаю как это код, но сейчас продукта — Excel нас важно, чтобы написаны через запятую, столбцу. Получится так.Образец №112/54 это не относится.
Dim objRegEx AsGIG_ant вправо. If Not алфавита, нужно его И к этому строку на ячейкиvitaliyms текстом. S11-6205360 работает :((((( не могунайти. да приведет к удалению эта статья была то мы быВнимание!Но и этоHugo Object: Добрый всем день. cell.MergeCells And rgX.Test(cell) отделить от названия номеру мы добавляем в Excel. Программа: Уважаемые умы прошуНо мне нужноАмортизатор 5-й дверибуду вникать и и обрабатывал он ноль по умолчанию. вам полезна. Просим указали разделителем «запятая».Если числа разделены еще не все,: Не относится, ноSet objRegEx =Данная тема уже Then cell.Offset(, 1) и перенести в еще число 2,

CyberForum.ru

Отделить формулой текст от чисел

 располагает разными поисковыми помощи ещё раз.
чтоб уюрать эти правый 6309020-0000 RTFM :( не совсем корректно. Если это не вас уделить паруВ этом окне, в не слешем, а к полученным цифрам всёж использовать комбинацию CreateObject(«VBScript.RegExp») обсуждалась но именно

 = rgX.Execute(cell)(0) Set соседний столбик. так как нам
функциями: одни ищутВ этой же номераАмортизатор задний 271.00.00
остается вопрос сNew является вас не секунд и сообщить, разделе «Образец разбора другим знаком, то

надо добавить числовой
из 4-х формул,

With objRegEx в таком разрезе cell = cell.Offset(1)Пример *** ****Кронштейн

нужен номер символа по ячейках другие
таблице нужно чтобыGuest плаза
отсутствием артикула в: Я не специалист
устраивают, можно создать помогла ли она данных» сразу видно, в формуле указываем
префикс (пусть 42),
с повторами, массивную
.Global = True

ответа не нашел. Wend Application.ScreenUpdating =
заднего брызговика 57707XA270 после пробела за ищут по содержимому
в ячейке Н25: Вот это, то
Амортизатор задний BQ2915050-60AO начале строки ПРО по формулам, но пользовательский числовой формат, вам, с помощью какие столбцы у
это знак.
как я понял,
— это имхо

.Pattern = «[^0-9,]» Задача такова: есть
True ‘Включаем обновление*** ****Кронштейн заднего квадратной скобкой. В
ячеек. Ведь выполнять было тоже самое
что надо!!!!!!!Нужно как то ПАК БИСКВИТ Смат попробую ) который начинает Excel, кнопок внизу страницы.
нас получатся. Нажимаем
Примеры других формул
это можно осуществить

перебор… могли мелкомягкие ‘»D»
столбец содержащий значения: экрана. End SubС
брызговика 7707XA280 последнем аргументе функция
поиск по текстовой
что и в
Спасибо огромное!!!!!!! отделить эти номера
Риводс Мини 200грПредположим, что текст
чтобы сохранить начального
Для удобства также кнопку «Далее».
выбора данных с

 командой СЦЕПИТЬ. Файл одну формулу придумать

NumbersOnly = .Replace(srcStr, несколько букв+несколько цифр. уважением, Aksima*** ****Накладка заднего вычисляет какое количество строке, которая содержится ячейки Е25 толькоJuliaКазанскийпотому как вся «0202 Гантеля резиновая нуля. Например если приводим ссылку на

Здесь, в разделе разными условиями смотрите прилагаю. на каждое действие. vbNullString) Возможно ли неСкорее всего, для бампера E771SXA000 символов будет содержать в ячейке ¬– цифры без букв: вот пример
: Или четкий критерий, строка ереносится в 150 мм» находится ввода или вставки оригинал (на английском «Поместить в:» указываем в статье «КакЗаранее спасибо!
GIG_antEnd With применяя макросов отделить примера выбрали лишьи надо разделенная строка после
это также распространеннаякитинKH 8625—8шт. из чего состоит стлбец артикулов :( в ячейке А1,

 коды продуктов 10 языке) .
диапазон столбцов, в сделать выбор вВладимир: К данной задачеSet objRegEx = буквы от чисел часть всей номенклатуры*** ****Кронштейн заднего разделения, учитывая положение потребность пользователей Excel.: и вам здравствуйте!!!BG 1802001-3—4шт. код, например цифры,ps и еще то можно в значного на листе,

Если необходимо, чтобы приложение которые поместятся новые ячейке Excel» тут.

: Помимо слэша, желательно это не относится.{/post}{/quote} Nothing и расположить в . брызговика следующая ячейка квадратной скобки. Их мы икак то так

KH 23020007—18шт. знаки «-» и вопрос сразу -

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

Пока не понялEnd Function

соседних столбцах ?Теле-ту 57707XA270Обратите внимание! Что в будем использовать для=ПСТР(E25;ПОИСК(«№»;E25)+1;25)KH 8629/8—8шт. «.», латинские буквы. как избавиться от формулу

номера следующим 0784367998 типы чисел какВнимание! ячейке из большого

 данные, которые сработали. как работает, ноGIG_antHugo: Спасибо огромное за
*** ****Кронштейн заднего нашем примере все разделения строк.viktturKH 23020008—12шт.Или пример с

 возможны символов в=ЛЕВСИМВ(A1;НАЙТИ(» «;A1)) для 784367998. В
текст, можно применить

Столбцы этого диапазона текста число, чтобы
И поместите туда

 мою веру в: to Hugo: Почему так макросов помощь!!! Действительно это
брызговика следующая
исходные и разделенные
Допустим на лист Excel
: Если номер всегда

подскажите пожалуйста как ~30 строками в конце строки типа

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

 оно считалось, смотрите формулу, которая это «негибкие» формулы екселяя с макросами боитесь? :) только часть текса ячейка 7707XA280 строки имеют разную

были импортированные данные 3-значный: отделить символы которые
Excel, что есть *# , *##

значение кода «0202»

создать пользовательский числовой числового. Например, если иначе информация в в статье «Не сделала. вам Михаил удалось не очень дружен,Ведь намного проще как пример, а

*** ****Накладка заднего

 длину и разное из другой программы.=ПРАВБ(E25;3) находятся до —шт. и что надо («звездочка число») ?А в ячейку

planetaexcel.ru

формат, состоящий из

Источник

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

Разбейте или разделите номер на отдельные цифры с помощью формулы
Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

Разбейте или разделите номер на отдельные цифры с помощью формулы

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

1. Выберите пустую ячейку (говорит ячейка C1), чтобы найти первую разделенную цифру числа в ячейке A1, затем введите формулу = MID ($ A1; COLUMN () — (COLUMN ($ C1) — 1); 1) в строку формул, а затем нажмите Enter .

Внимание: В формуле A1 — это ячейка с номером, который нужно разделить на цифры, а C1 — это ячейка для определения первой разделенной цифры. Пожалуйста, измените их по своему усмотрению.

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

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

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

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

1. Выделите ячейки с номерами, которые нужно разбить на цифры, затем нажмите Кутулс > Слияние и разделение > Разделить клетки. Смотрите скриншот:

2. в Разделить клетки диалоговое окно, выберите Разделить на столбцы вариант в Тип и в разделе Указать разделитель выберите Указать ширину и введите число 1 в текстовое поле. Щелкните значок OK кнопку.

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

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

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

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (12)

Оценок пока нет. Оцените первым!

Источник

Извлечем из числа 123 456 789 единицы (789), десятки (8), сотни (7), тысячи (456) и миллионы (123) .

Введем число от 0 до 1 млрд. в ячейку B1 . Нижеследующие формулы извлекают (см. Файл примера ):

миллионы: =ОКРУГЛВНИЗ(B1;-6)/1000000
тысячи: =ОСТАТ(ОТБР(B1/1000);1000)
0-999: =ОСТАТ(B1;1000)
сотни: =ОСТАТ(ОТБР(B1/100);10)
десятки: =ОСТАТ(ОТБР(B1/10);10)
единицы: =ОСТАТ(ОТБР(B1);10)

В ячейке B1 можно отобразить, например, миллионы (123М), не изменяя само число. Это можно сделать, применив соответствующий числовой пользовательский формат .

ПРИМЕНЕНИЕ : Приведенные выше решения использованы в статье Число прописью .

Применение деления в программе Microsoft Excel

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

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».

«равно»

(=)

(/)

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

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

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.

«=»

«равно»

«/»

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

Способ 3: деление столбца на столбец

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

«=»

«/»

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

Способ 4: деление столбца на константу

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

«равно»

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

Способ 5: деление столбца на ячейку

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

«=»

(/)

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

Способ 6: функция ЧАСТНОЕ

Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.

Посмотрим применение данной функции на примере.

«Вставить функцию»

После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

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

Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш – «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.

Мы рады, что смогли помочь Вам в решении проблемы.

Как разбить число на отдельные цифры в Excel?

Разбейте или разделите номер на отдельные цифры с помощью формулы
Разбейте или разделите номер на отдельные цифры с помощью Kutools for Excel

Kutools for Excel Утилита Split Cells от позволяет легко разделить число на отдельные цифры или разбить содержимое ячеек по пробелу, запятой, новой строке или другим разделителям на отдельные строки или столбцы в Excel, как показано на скриншоте ниже. показал. Загрузите полную 60-дневную бесплатную версию Kutools for Excel прямо сейчас!

Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Разбиение или разделение числа на отдельные цифры с помощью формулы

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

1. Выберите пустую ячейку (говорит ячейка C1) для поиска первой разделенной цифры числа в ячейке A1, затем введите формулу = MID ($ A1, COLUMN () — (COLUMN ($ C1) — 1), 1) в строку формул , а затем нажмите клавишу Enter .

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

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

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

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

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

Перед применяя Kutools for Excel , сначала загрузите и установите его лы .

1. Выберите ячейки с номером, который нужно разделить на цифры, затем нажмите Kutools > Объединить и разделить > Разделить ячейки . См. Снимок экрана:

2. В диалоговом окне Разделить ячейки выберите параметр Разделить на столбцы в разделе Тип и в поле «Укажите разделителя, выберите «Указать ширину» и введите число 1 в текстовое поле. Нажмите кнопку OK .

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

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

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

Вкладка Office — просмотр, редактирование и управление с вкладками Рабочие книги в Excel:

Вкладка Office предоставляет интерфейс с вкладками, как в веб-браузерах, таких как Google Chrome, новые версии Internet Explorer и Firefox, в Microsoft Excel. . Он станет незаменимым помощником в работе и сэкономит время. См. Демонстрацию ниже:

Нажмите, чтобы получить бесплатную пробную версию вкладки Office!

Источник

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

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

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

Вот что мы рассмотрим в этой статье:

Как извлечь число в конце текста
Получаем число из начала текста
Как извлечь все числа из текста
Извлекаем числа без формул при помощи Ultimate Suite

Когда дело доходит до извлечения части текстового значения заданной длины, Эксель предоставляет три текстовых функции (ЛЕВСИМВ, ПРАВСИМВ и ПСТР) для быстрого выполнения этой задачи. А вот когда дело доходит до извлечения числа из буквенно-цифровой строки, Microsoft Excel … не предоставляет ничего.

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

Или вы можете запустить инструмент «Извлечь (Extract)» из надстройки Ablebits Ultimate Suite и выполнить эту операцию одним щелчком мыши. Ниже вы найдете полную информацию обо всех этих методах.

Как извлечь число из конца текстовой строки.

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

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

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

=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9}; ячейка &»0123456789″))

О логике вычислений мы поговорим чуть позже. На данный момент просто замените слово «ячейка» ссылкой на позицию, содержащую исходный текст (в нашем случае A2), и запишите получившееся выражение в любую пустую клетку той же строки, скажем, в B2:

=МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))

Хотя формула содержит константу массива, это обычное выражение, которое вводится обычным способом: нажатием клавиши Enter.

Как только позиция первой цифры определена, можно использовать функцию ПРАВСИМВ для извлечения числа. Чтобы узнать, сколько символов нужно извлечь, вы вычитаете позицию первой цифры из общей длины строки и добавляете единицу к результату, потому что первая цифра также должна быть включена:

=ПРАВСИМВ(A2;ДЛСТР(A2)-B2+1)

Где A2 — исходная ячейка, а B2 — позиция первой цифры.

На следующем скриншоте показаны результаты:

Чтобы исключить вспомогательный столбец, содержащий позицию первой цифры, вы можете встроить формулу МИН непосредственно в функцию ПРАВСИМВ следующим образом:

=ПРАВСИМВ(A2;ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)

Чтобы формула возвращала именно число, а не числовую строку, вложите ее в функцию ЗНАЧЕН:

=ЗНАЧЕН(ПРАВСИМВ(A2;ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1))

Или просто примените двойное отрицание, использовав два знака «минус»:

=—ПРАВСИМВ(A2;ДЛСТР(A2)-МИН(ПОИСК({0;1;2;3;4;5;6;7;8;9};A2&»0123456789″))+1)

Другой способ извлечь число из конца строки — использовать вот такое выражение:

=ПРАВСИМВ( ячейка ;СУММ(ДЛСТР( ячейка ) — ДЛСТР(ПОДСТАВИТЬ( ячейка ; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

=ПРАВСИМВ(A2;СУММ(ДЛСТР(A2) — ДЛСТР(ПОДСТАВИТЬ(A2; {«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

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

=ПРАВСИМВ(A2; ДЛСТР(A2) — МАКС(ЕСЛИ(ЕЧИСЛО(ПСТР(A2; СТРОКА(ДВССЫЛ( «1:»&ДЛСТР(A2))); 1) *1)=ЛОЖЬ; СТРОКА(ДВССЫЛ( «1:»&ДЛСТР(A2))); 0)))

На скриншоте ниже вы видите результат ее работы.

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

Примечание. Если вы используете Excel 2019 или более ранние версии, нужно использовать формулу массива, нажав при вводе комбинацию Ctrl+Shift+Enter. Если у вас Office365, вводите как обычно, через Enter.

Как извлечь число из начала текстовой строки

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

=ЛЕВСИМВ( ячейка ;СУММ(ДЛСТР( ячейка )-ДЛСТР(ПОДСТАВИТЬ( ячейка ;{«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

Используя этот метод для A2, извлекаем число при помощи такого выражения:

=ЛЕВСИМВ(A2;СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;{«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»))))

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

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

=ЛЕВСИМВ(A2;ПОИСКПОЗ(ЛОЖЬ;ЕЧИСЛО(—ПСТР(A2;СТРОКА($1:$94);1));0)-1)

Или чуть модифицируем, чтобы ускорить расчеты:

=ЛЕВСИМВ(A2; ПОИСКПОЗ(ЛОЖЬ; ЕЧИСЛО(ПСТР(A2; СТРОКА(ДВССЫЛ( «1:»&ДЛСТР(A2)+1)); 1) *1); 0) -1)

Если у вас Excel 2019 и ниже, вводите ее как формулу массива, используя Ctrl+Shift+Enter. В Office365 и выше можно вводить как обычно.

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

Как получить число из любой позиции в тексте

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

=СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10)

Где A2 — исходная текстовая строка.

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

Обратите внимание, что в этом случае в тексте могут находиться несколько чисел. Все они будут извлечены и объединены в единое целое.

Однако, изучив результаты, вы можете заметить один незначительный недостаток: если исходный текст в ячейке не содержит числа, формула возвращает ноль, как в строке 7 на скриншоте выше. Чтобы исправить это, вы можете заключить формулу в оператор ЕСЛИ, который проверит, содержит ли исходный текст какое-либо число. Если это так, формула извлекает это число, в противном случае возвращает пустую строку:

=ЕСЛИ(СУММ(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;{«0″;»1″;»2″;»3″;»4″;»5″;»6″;»7″;»8″;»9″};»»)))>0; СУММПРОИЗВ(ПСТР(0&A2; НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2; СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 1)) * СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))); 0); СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))))+1; 1) * 10^СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))/10);»»)

В отличие от всех предыдущих примеров, результатом этих формул является число. Чтобы убедиться в этом, просто обратите внимание на выровненные по правому краю значения в столбце B и усеченные ведущие нули (например, 88 вместо 088).

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

Мы будем искать позицию знака «№» и позицию следующего за ним первого пробела. То, что находится между ними, как раз и будет номером счёта:

=ПСТР(ПОДСТАВИТЬ(A2;» «;»»);НАЙТИ(«№»;ПОДСТАВИТЬ(A2;» «;»»))+1;НАЙТИ(» «;A2;НАЙТИ(«№»;A2;1))-НАЙТИ(«№»;A2;1)-1)

На скриншоте ниже вы видите, как это работает.

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

Используем формулу

=ПРОСМОТР(2^64;—ЛЕВСИМВ(ПСТР(A1&»_0″;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1&»_0123456789″));15); {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}))

или заменяем список цифр функцией:

=ПРОСМОТР(2^64;—ЛЕВСИМВ(ПСТР(A1&»_0″;МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9};A1&»_0123456789″));15); СТРОКА($A$1:$IV$16)))

Как видите, получаем только первое число, независимо от его расположения:

И еще один пример. Давайте попробуем достать все числа из текста, разграничив их каким-то разделителем. Например, дефисом “-“.

В этом случае придется использовать формулу массива:

{=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(СЦЕП(ЕСЛИ(ЕЧИСЛО(—ПСТР(A2;СТРОКА($1:$94);1));ПСТР(A2;СТРОКА($1:$94);1);» «)));» «;»-«)}

Мы нашли все числа в тексте, как вы видите на скриншоте ниже:

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

Поскольку между ними есть разделители, то вы легко можете распределить эти числа в отдельные ячейки. Как это сделать — читайте в материале 8 способов разделить ячейку Excel на две или несколько.

Как выделить число из текста с помощью Ultimate Suite

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

С надстройкой Ultimate Suite, добавленной на вашу ленту Excel, вы можете быстро получить число из любой буквенно-цифровой строки:

Перейдите на вкладку Ablebits Data > Text и нажмите Извлечь (Extract) :

Выделите все ячейки с данными, которые нужно обработать.
На панели инструмента «Извлечь (Extract)» установите переключатель «Извлечь числа (Extract numbers)».
В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите поле «Вставить как формулу (Insert as formula)» или оставьте его пустым (по умолчанию).

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

Нажмите кнопку «Вставить результаты (Insert Results)». Готово!

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

Если установлен флажок «Вставить как формулу», вы увидите выражение в строке формул. Любопытно узнать, какое именно? Просто скачайте пробную версию Ultimate Suite и убедитесь сами

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

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

Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…

Как умножить число на процент и прибавить проценты — Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак…

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

Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…

Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…

5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…

Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…

Источник

Как отделить одну цифру числа от других

Разделить текст в ячейке Excel по столбцам.

Разделить текст Excel на несколько ячеек.

Форматирование чисел в виде текста

как разделить столбец на цифры и текст

Как в Excel отделить текст от цифры в одной ячейке и перенести из по другим. (в пояснении скрины)

Отделить в ячейки буквы от цифр (Формулы/Formulas)

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

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

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

Нужен макрос для Excel, отделяющий текст от цифр

Отделить формулой текст от чисел

Разбейте или разделите номер на отдельные цифры с помощью формулы

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Применение деления в программе Microsoft Excel

Способ 1: деление числа на число

Способ 2: деление содержимого ячеек

Способ 3: деление столбца на столбец

Способ 4: деление столбца на константу

Способ 5: деление столбца на ячейку

Способ 6: функция ЧАСТНОЕ

Как разбить число на отдельные цифры в Excel?

Разбиение или разделение числа на отдельные цифры с помощью формулы

Разбейте или разделите число на отдельные цифры с помощью Kutools for Excel

Как извлечь число из конца текстовой строки.

Как извлечь число из начала текстовой строки

Как получить число из любой позиции в тексте

Как выделить число из текста с помощью Ultimate Suite

А вот еще интересные статьи: