Как разделить текст и цифры в эксель в разные ячейки

В 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.00​Guest​ текст после кода​ десять кода продукта,​
​ более, необходимо использовать​ вставить столбцы. Как​ сгрупприровать данные в​. В Excel есть​ ПСТР(A1;СТРОКА($1:$30);1) добавив формулу​ ексель так что​ если UDF в​ исправить, а весь​sulfur​ для любых условий​ ячейку. Необходимо из​ текстом, а числами​

Разделить текст 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 на​ вооружение.​: Если у Вас​ в копилке должны​ помог.​ Есть и такие,​ и указать их​ которой можно определить,​

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

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

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

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

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

​: Пишет ИМЯ?​​: А кажись понял.​: на более сложных​ может отформатирован и​ определить, поскольку число​ «Формат данных столбца»​как разделить текст в​

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

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

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

​ не только с​ вас попадается.​ это могут быть​ одной и той​ нашёл ещё вариант​после копируйте ячейки​

​: Вот пример​sergejkov​ 🙁 — надо​ как текст, который​ ячейке по левому​Нажимаем кнопку «Готово».​ несколько ячеек.​.​: Чего обижаться. Я​ сохранения макроса выбираете​ Integer, l As​ латинскими буквами но​Я бы на​ пробелы если нужно​ же строке. В​ (я просто разбираюсь​ и обратно вставляете​vikttur​: вот теперь так​ отделить именно цифры(артикул)​ позже может привести​ краю, а не​ Получилось так.​​Например, предложение из​​У нас такая​ б такую формулу​ Personal.xls, записываете простой​ String​ и с русскими,​ вашем месте раскошелился​ разделить строку на​ нашем случае нас​ с новым документом​ но уже через​: Вас давно просят​ как нужно :)​

​ в начале строки,​ к проблемам с​ по правому.​Так можно разделить текст​ первой ячейки разнести​ таблица с данными.​ не в жисть​ макрос (например, пишите​For j =​ и как можно​ на услуги фрилансера,​ слова и т.п.​ интересуют только числа,​ поэтому не все​ спец. вставку только​ четкие критерии кода.​ спасибо!​ а не просто​ вычислениями или Фрукты​Выделите ячейку или диапазон​

​ из одной ячейки​ по словам в​В столбце А написан​ не сочинил :)​ в А1 что-нибудь).​ 1 To Len(t)​ убрать лишние перенесенные​ т.к. это задача​В данном примере функция​ которые находятся вне​ варианты сразу выложил)​

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

​ значения. возможно потребуется​​Из «Подшипник 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, *24​sergejkov​ обратно в числовой.​

​ Такие числа будут​​ итоговые) выделить другим​ другом столбце. Например,​ мы написали «-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-Z\d]<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))​​Sm​vikttur​
​вот :(​ 300гр*6​ числа в текст​щелкните стрелку рядом​Можно разделить ячейку​
​ имени определить пол».​Копируем формулу по​ обратного, тогда без​

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

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

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

Как в 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-Z\d]<2,>» ‘Номер -​
​ количество символов в​ ​Первый аргумент – это​

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

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

​GIG_ant​​ExtractNumber = str​ = Cells(1) ‘Начинаем​

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

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

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

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

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

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

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

​Guest​​kim​sergejkov​не виден, прокрутите​

Как разделить текст по ячейкам формула в 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. ​ указываем – «С​ «+1» — это​Ivian​
2. ​ таки жалко что​For i =​ ‘Если ячейка -​ процесса. Set cell​
3. ​deniking​ количество символов, которое​buchlotnik​Саша русанов​

​ Wall Hover» -​Оромнейшая Вам благодарность!​kim​020543 Кольцо резиновое​ в 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» — это​ форум нашел эту​ будущем разработчики включат​

Нужен макрос для 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 =​ в ней есть​ одной ячейке, номер​ очередной номер символа​ целую строку как​ таблицы, русские буквы​ деле есть и​ у всех ячеек​sergejkov​psкакто я виде​ начинается с нуля​ содержать неточности и​ имя и фамилия​ дате (после слеша).​Надо изъять цифры​Михаил С.​Function NumbersOnly(srcStr As​
​ True ‘Включаем обновление​ номер согласно шаблону​ состоит из цифр​ первой закрывающейся квадратной​

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

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

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

​ располагает разными поисковыми​​ помощи ещё раз.​
​ чтоб уюрать эти​ правый 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_ant​End With​ применяя макросов отделить​ примера выбрали лишь​и надо​ разделенная строка после​
​ это также распространенная​китин​KH 8625—8шт.​ из чего состоит​ стлбец артикулов :(​ в ячейке А1,​

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

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

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

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

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

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

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

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

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

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

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

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

​подскажите пожалуйста как​​

30 строками в​ конце строки типа​

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

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

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

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

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

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

Разделить текст с числом на два столбца (текст и число отдельно)

Как в exel можно разделить текст с числами в одном столбце на 2 столбца: отдельно текст и отдельно числа?

Пример разделенного текста

• Число в тексте справа.

Извлекаем текст до первого числового символа:

Правую часть (числовое значение) можно извлечь подобным образом:

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

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

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

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

Чтобы разбить текст в Excel, вы обычно используете функции ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) или ПСТР (MID) в сочетании с НАЙТИ (FIND) или ПОИСК (SEARCH). На первый взгляд, некоторые рассмотренные ниже приёмы могут показаться сложными. Но на самом деле логика довольно проста, и следующие примеры помогут вам разобраться.

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

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

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

Для лучшего понимания пошагово рассмотрим несколько примеров.

Делим текст вида ФИО по столбцам.

Далее в качестве разделителя выбираем пробел.

Обращаем внимание на то, как разделены наши данные в окне образца.

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

В итоге имеем следующую картину:

=ЛЕВСИМВ(A2; ПОИСК(» «;A2;1)-1)

В качестве разделителя мы используем пробел. Функция ПОИСК указывает нам, в какой позиции находится первый пробел. А затем именно это количество букв (за минусом 1, чтобы не извлекать сам пробел) мы «отрезаем» слева от нашего ФИО при помощи ЛЕВСИМВ.

Далее будет чуть сложнее.

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

Как вы, наверное, знаете, функция Excel ПСТР имеет следующий синтаксис:

ПСТР (текст; начальная_позиция; количество_знаков)

Текст извлекается из ячейки A2, а два других аргумента вычисляются с использованием 4 различных функций ПОИСК:

• Начальная позиция — это позиция первого пробела плюс 1:

• Количество знаков для извлечения: разница между положением 2- го и 1- го пробелов, минус 1:

ПОИСК(» «;A2;ПОИСК(» «;A2)+1) — ПОИСК(» «;A2) – 1

В итоге имя у нас теперь находится в C.

Осталось отчество. Для него используем выражение:

В этой формуле функция ДЛСТР (LEN) возвращает общую длину строки, из которой вы вычитаете позицию 2- го пробела. Получаем количество символов после 2- го пробела, и функция ПРАВСИМВ их и извлекает.

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

Распределение текста с разделителями на 3 столбца.

Предположим, у вас есть список одежды вида Наименование-Цвет-Размер, и вы хотите разделить его на 3 отдельных части. Здесь разделитель слов – дефис. С ним и будем работать.

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

Здесь функция мы сначала определяем позицию первого дефиса («-«) в строке, а ЛЕВСИМВ извлекает все нужные символы начиная с этой позиции. Вы вычитаете 1 из позиции дефиса, потому что вы не хотите извлекать сам дефис.

1. Чтобы извлечь цвет (это все буквы между 1-м и 2-м дефисами), запишите в C2, а затем скопируйте ниже:

Логику работы ПСТР мы рассмотрели чуть выше.

1. Чтобы извлечь размер (все символы после 3-го дефиса), введите следующее выражение в D2:

Аналогичным образом вы можете в Excel разделить содержимое ячейки в разные ячейки любым другим разделителем. Все, что вам нужно сделать, это заменить «-» на требуемый символ, например пробел (« »), косую черту («/»), двоеточие («:»), точку с запятой («;») и т. д.

Примечание. В приведенных выше формулах +1 и -1 соответствуют количеству знаков в разделителе. В нашем примере это дефис (то есть, 1 знак). Если ваш разделитель состоит из двух знаков, например, запятой и пробела, тогда укажите только запятую («,») в ваших выражениях и используйте +2 и -2 вместо +1 и -1.

Как разбить текст по переносам строки.

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

Предположим, ячейки, которые вы хотите разделить, выглядят примерно так:

Напомню, что перенести таким вот образом текст внутри ячейки можно при помощи комбинации клавиш ALT + ENTER.

Возьмите инструкции из предыдущего примера и замените дефис («-») на СИМВОЛ(10), где 10 — это код ASCII для перевода строки.

Чтобы извлечь наименование товара:

=ПСТР(A2; ПОИСК(СИМВОЛ(10);A2) + 1; ПОИСК(СИМВОЛ(10);A2; ПОИСК(СИМВОЛ(10);A2)+1) — ПОИСК(СИМВОЛ(10);A2) — 1)

=ПРАВСИМВ(A2;ДЛСТР(A2) — ПОИСК(СИМВОЛ(10); A2; ПОИСК(СИМВОЛ(10); A2) + 1))

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

Таким же образом можно работать и с любым другим символом-разделителем. Достаточно знать его код.

Как распределить текст с разделителями на множество столбцов.

Изучив представленные выше примеры, у многих из вас, думаю, возник вопрос: «А что, если у меня не 3 слова, а больше? Если нужно разбить текст в ячейке на 5 столбцов?»

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

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

Для первого слова (наименования одежды) используем:

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

Для второго столбца и далее понадобится более сложное выражение:

Замысел здесь состоит в том, что при помощи функции ПОДСТАВИТЬ мы удаляем из исходного содержимого наименование, которое уже ранее извлекли (то есть, «Юбка»). Вместо него подставляем пустое значение «» и в результате имеем «Синий-M-39-42-50». В нём мы снова ищем позицию первого дефиса, как это делали ранее. И при помощи ЛЕВСИМВ вновь выделяем первое слово (то есть, «Синий»).

А далее можно просто «протянуть» формулу из C2 по строке, то есть скопировать ее в остальные ячейки. В результате в D2 получим

=ЕСЛИОШИБКА(ЛЕВСИМВ(ПОДСТАВИТЬ($A2&»-«; ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1); ПОИСК(«-«;ПОДСТАВИТЬ($A2&»-«;ОБЪЕДИНИТЬ(«-«;ИСТИНА;$B2:C2)&»-«;»»;1);1)-1);»»)

Обратите внимание, жирным шрифтом выделены произошедшие при копировании изменения. То есть, теперь из исходного текста мы удаляем все, что было уже ранее найдено и извлечено – содержимое B2 и C2. И вновь в получившейся фразе берём первое слово — до дефиса.

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

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

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

Как разделить ячейку вида ‘текст + число’.

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

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

Метод 1. Подсчитайте цифры и извлеките это количество символов

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

Чтобы извлечь числа, вы ищите в строке все возможные числа от 0 до 9, получаете общее их количество и отсекаете такое же количество символов от конца строки.

Если мы работаем с ячейкой ​​A2:

Чтобы извлечь буквы, вы вычисляете, сколько их у нас имеется. Для этого вычитаем количество извлеченных цифр (C2) из ​​общей длины исходной ячейки A2. После этого при помощи ЛЕВСИМВ отрезаем это количество символов от начала ячейки.

здесь A2 – исходная ячейка, а C2 — извлеченное число, как показано на скриншоте:

Метод 2: узнать позицию 1- й цифры в строке

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

Как видите, мы последовательно ищем каждое число из массива <0,1,2,3,4,5,6,7,8,9>. Чтобы избежать появления ошибки если цифра не найдена, мы после содержимого ячейки A2 добавляем эти 10 цифр. Excel последовательно перебирает все символы в поисках этих десяти цифр. В итоге получаем опять же массив из 10 цифр — номеров позиций, в которых они нашлись. И из них функция МИН выбирает наименьшее число. Это и будет та позиция, с которой начинается группа чисел, которую нужно отделить от основного содержимого.

Также обратите внимание, что это формула массива и ввод её нужно заканчивать не как обычно, а комбинацией клавиш CTRL + SHIFT + ENTER .

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

Чтобы получить текст:

Чтобы получить числа:

Где A2 — исходная строка, а B2 — позиция первого числа.

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

Для вытаскивания текста:

Для чисел:

Этого же результата можно достичь и чуть иначе.

Сначала мы извлекаем из ячейки числа при помощи вот такого выражения:

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

А затем уже берём оставшееся:

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

Как разделить ячейку вида ‘число + текст’.

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

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

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

Где A2 — исходная строка, а B2 — искомое число, как показано на снимке экрана ниже:

Как разбить текст по ячейкам по маске (шаблону).

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

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

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

Решением является разбиение строки по следующей маске: * ERROR: * Exception: *

Здесь звездочка (*) представляет любое количество символов.

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

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

Итак, в начале ищем позицию первого разделителя.

Затем аналогичным образом находим позицию, в которой начинается второй разделитель:

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

С 1 по 20 символ – дата и время. С 21 по 26 символ – разделитель “ERROR:”. Далее – код ошибки. С 31 по 40 символ – второй разделитель “Exception:”. Затем следует описание ошибки.

Таким образом, в первый столбец мы поместим первые 20 знаков:

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

Далее нужно получить код:

Думаю, вы понимаете, что 6 – это количество знаков в нашем слове-разделителе «ERROR:».

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

Аналогично добавляем 10 к найденной позиции второго разделителя «Exception:», чтобы выйти на координаты первого символа сразу после разделителя. Ведь функция говорит нам только то, где разделитель начинается, а не заканчивается.

Таким образом, ячейку мы распределили по 3 столбцам, исключив при этом слова-разделители.

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

Как разделить ячейки в Excel с помощью функции разделения текста Split Text.

Альтернативный способ разбить столбец в Excel — использовать функцию разделения текста, включенную в надстройку Ultimate Suite for Excel. Она предоставляет следующие возможности:

• Разделить ячейку по символу-разделителю.
• Разделить ячейку по нескольким разделителям.
• Разделить ячейку по маске (шаблону).

Чтобы было понятнее, давайте более подробно рассмотрим каждый вариант по очереди.

Разделить ячейку по символу-разделителю.

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

Для этого примера возьмем строки шаблона Товар-Цвет-Размер , который мы использовали в первой части этого руководства. Как вы помните, мы разделили их на 3 разных столбца, используя 3 разные формулы . А вот как добиться того же результата за 2 быстрых шага:

1. Предполагая, что у вас установлен Ultimate Suite , выберите ячейки, которые нужно разделить, и щелкните значок «Разделить текст (SplitText)» на вкладке «AblebitsData».

1. Панель Разделить текст откроется в правой части окна Excel, и вы выполните следующие действия:
   • Разверните группу «Разбить по символам (SplitbyCharacters)» и выберите один из предопределенных разделителей или введите любой другой символ в поле «Пользовательский (Custom)» .
   • Выберите, как именно разбивать ячейки: по столбцам или строкам.
   • Нажмите кнопку «Разделить (Split)» .

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

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

Разделить ячейку по нескольким разделителям.

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

Например, чтобы разделить предложение на части, используя запятые и союзы, активируйте инструмент «Разбить по строкам (Split by Strings)» и введите разделители, по одному в каждой строке:

В данном случае в качестве разделителей мы используем запятую и союз “или”.

В результате исходная фраза разделяется при появлении любого разделителя:

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

А вот еще один пример. Предположим, вы импортировали столбец дат из внешнего источника, и выглядит он следующим образом:

Этот формат не является обычным для Excel, и поэтому ни одна из функций даты не распознает здесь какие-либо элементы даты или времени. Чтобы разделить день, месяц, год, часы и минуты на отдельные ячейки, введите следующие символы в поле Spilt by strings:

• Точка (.) Для разделения дня, месяца и года
• Двоеточие (:) для разделения часов и минут
• Пробел для разграничения даты и времени

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

Разделить ячейки по маске (шаблону).

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

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

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

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

Решением является разбиение строки по следующей маске:

Где звездочка (*) представляет любое количество символов.

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

А теперь нажмите кнопку «Разбить по маске (Split by Mask)» на панели «Split Text» , введите маску в соответствующее поле и нажмите «Split».
Результат будет примерно таким:

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

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

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

• Все символы перед 1-м пробелом в строке (дата)
• Символы между 1-м пробелом и словом ERROR: (время)
• Текст между ERROR: и Exception: (код ошибки)
• Все, что идет после Exception: (текст описания)

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

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

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

Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft…
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью…
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы…
Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…
Относительные и абсолютные ссылки – как создать и изменить — В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,…

Как разделить текст в Excel 5 способами

В этом руководстве мы собираемся показать вам, как разделить текст в Excel с помощью разделителя.

У нас есть образец данных, который содержит объединенные значения, разделенные символами “|”. Важно, чтобы данные включали определенный символ-разделитель между каждым фрагментом данных, чтобы упростить разделение текста.

Функция преобразования текста в столбцы для разделения текста

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

1. Начните с выбора ваших данных. Вы можете использовать более одной ячейки в столбце.
2. Нажмите Данные> Текст в столбцы на ленте.
3. На первом шаге мастера у вас есть 2 варианта на выбор — это методы нарезки. Поскольку наши данные в этом примере разделены разделителями, наш выбор будет разделен разделителями.
4. Нажмите Далее , чтобы продолжить
5. Выберите разделители, подходящие для ваших данных, или выберите длину символа и нажмите Далее.
6. Выберите соответствующие типы данных для столбцов и целевой ячейки. Пожалуйста, обратите внимание, что если целевая ячейка совпадает с ячейкой, в которой находятся ваши данные, исходные данные будут перезаписаны.
7. Нажмите Готово , чтобы увидеть результат.

Использование формул для разделения текста

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

Формула, используемая в этом примере, использует функцию динамического массива Microsoft 365, которая позволяет вам заполнить несколько ячеек без использования формул массива. Если вы видите формулу ПОСЛЕДОВАТЕЛЬНОСТИ, вы можете использовать этот метод.

Синтаксис

= ОБРЕЗАТЬ (СЕРЕДИНА(ЗАМЕНИТЬ( текст, разделитель, REPT( “ “, LEN( текст))),(ПОСЛЕДОВАТЕЛЬНОСТЬ (1, количество столбцов) — 1) * LEN (текст) + 1,LEN(текст)))

Как это работает

Формула сначала заменяет каждый символ-разделитель пробелами. (см. SUBSTITUTE) Количество пробелов будет равно количеству символов в исходной строке, что является достаточным количеством пробелов для разделения каждого блока строки (см. REPT и LEN).

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

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

Flash Fill

Flash Fill — это инструмент Excel, который может определять шаблон при вводе данных. Распространенным примером является разделение или слияние имени и фамилии. Допустим, столбец A содержит комбинации имени и фамилии. Если вы введете соответствующее имя в той же строке для столбца B, Excel покажет вам предварительный просмотр для остальной части столбца.

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

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

Мощный запрос

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

Если вы используете Excel 2016 или новее, включая Microsoft 365, вы можете найти параметры Power Query в разделе «Получение и преобразование» вкладки «Данные «. Пользователям Excel 2010 и 2013 следует загрузить и установить Power Query в качестве надстройки.

1. Выделите ячейки, содержащие текст.
2. Выберите Данные > из листа. Если данных нет в таблице Excel, Excel сначала преобразует их в таблицу Excel.
3. Как только откроется окно Power Query, найдите разделенный столбец на вкладке Преобразование и щелкните, чтобы просмотреть параметры.
4. Выберите подход, который соответствует вашему расположению данных. В данных в нашем примере используется разделитель, поскольку данные разделяются символом “|”.
5. Power Query покажет символ-разделитель. Если вы не видите ожидаемый разделитель, выберите из списка или введите его самостоятельно.
6. Нажмите OK , чтобы разделить текст.
7. Если ваши данные содержат заголовки в первой строке, как в нашем примере, нажмите Преобразовать > Использовать первую строку в качестве заголовков, чтобы сохранить их в качестве заголовков.
8. Если вы удовлетворены результатом, нажмите кнопку Главная> Закрыть и загрузить, чтобы переместить разделенные данные в вашу книгу.

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

1. Вызвав функцию преобразования текста в столбцы с помощью VBA,
2. Использование функции Split в VBA для создания массива вложенных строк и использование цикла для распределения вложенных строк по соседним ячейкам в одной строке.

Текст в столбцы с помощью VBA

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

Простой способ сгенерировать код VBA для разделения текста — записать макрос. Запустите раздел записи с помощью кнопки «Записать макрос» на вкладке «Разработчик » и используйте функцию «Разбиение текста на столбцы»…………. Как только запись будет остановлена, Excel сохранит код того, что вы сделали во время записи.

Вложите VBATextToColumns_Multiple()
Затемните MyRange как диапазон
Установите MyRange = Selection ‘Для работы со статическим диапазоном, замените выделение с помощью объекта Range, например, Range(“B6:B12”)

MyRange.TextToColumns _
Назначение:=MyRange(1, 1).Смещение(, 1), _
Тип данных:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Последовательный предел:=False, _
Табуляция:=True, _
Точка с запятой:=False, _
Запятая:=False, _
Пробел:=False, _
Другое:=True, _
OtherChar:=»|»
Конец Субмарина

Функция разделения

Функция разделения может быть полезна, если вы хотите сохранить разделенные блоки в массиве. Вы можете использовать этот метод только для разделения из-за ограничения с одним разделителем. Следующий код перебирает каждую ячейку в выбранном столбце, разделяет и сохраняет текст с помощью разделителя “|” и использует другой цикл для заполнения значений массива в ячейках. Конечный столбец целиком.Команда автозаполнения регулирует ширину столбца.

Sub SplitText()
Затемнить stringArray() как строку, ячейку как диапазон, i как целое число
для каждой ячейки в выделении ‘Для работы со статическим диапазоном замените выделение с помощью объекта Range, например, Range(“B6:B12”)
stringArray = Split(Cell, «|») ‘Измените разделитель символом, соответствующим вашим данным
, для i = 0 на UBound(stringArray)
ячейка.Смещение (, i + 1) = stringArray(i)
ячейка.Смещение (, i + 1).Целая колонка.Автозаполнение ‘Это для ширины столбца и необязательно.
Далее я
заканчиваю подраздел Next