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

У меня есть строки в ячейках, которые выглядят так (их могут быть сотни)

713.00;482.00;337.00;530.00;
71.00;32.54;37.00;5300.30;
  • Всего 4 числа, после каждого числа стоит точка с запятой.
  • Длина этих чисел будет разной.

Идеальный результат

У меня есть формула для первой ячейки (Split 1):

=LEFT(B2,(FIND(";",B2,1)-1))

Я просто не могу понять формулы для следующих 3 ячеек (разделить 2, 3 и 4)

ПРИМЕЧАНИЕ. Это должна быть формула, а НЕ функция преобразования текста в столбцы.

Любые предложения будут оценены, и если требуется дополнительная информация, пожалуйста, спросите! :)


excel excel-2010 split multiple-columns cells
person JHair    schedule 03.12.2015    source источник
comment
Данные ► Инструменты данных ► Текст в столбцы   -  person    schedule 03.12.2015
comment
Спасибо. Но, как следует из моего названия, мне нужна функция/формула. Я исправил свой пост, чтобы сделать его более понятным :)   -  person JHair    schedule 03.12.2015

Ответы (1)


arrow_upward
3
arrow_downward

В B1 введите:

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

и копируем вдоль и поперек:

введите здесь описание изображения

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

=--TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

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

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

=--(TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",LEN($A1))),(COLUMNS($A:A)-1)*LEN($A1)+1,LEN($A1))))
person Gary's Student    schedule 03.12.2015
comment
Умная! Одним из недостатков является то, что число 999 является произвольным, поэтому эта формула не работает, если длина исходной строки больше этого числа. Вместо 999 просто используйте LEN($A1)! - person Jean-François Corbett; 03.12.2015
comment
@ Жан-Франсуа Корбетт Отличная идея!! - person Gary's Student; 03.12.2015
comment
Также я почти уверен, что существует ограничение на длину строк, которые могут обрабатывать функции Excel... Я думаю, что это 2 ^ 15-1. Таким образом, замена разделителей большим количеством пробелов приведет к падению этого предела, если во входном списке достаточно элементов. Если вы используете 999 пробелов, 32 элементов достаточно, чтобы он не работал. Если вы используете LEN($A1), это больше, но все же довольно конечно. - person Jean-François Corbett; 03.12.2015
comment
Мне кажется, что вы могли бы использовать этот шаблон и для ответа на этот вопрос: Получить путь к родительской папке из пути к файлу, используя формулу ячейки - person Jean-François Corbett; 03.12.2015