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

Question

Как вы можете разделить текст на столбцы справа налево? У моих данных есть несовместимое количество пробелов в строке, например:

Hernandez Landscaping Gus Hernandez 1 Neurys Lane Newark, DE 19702
Hershey's Mill Golf Club Steve Friedell 1000 Mill Road West Chester, PA 19380
Hershey Country Club 1000 E.Derry Rd Hershey, PA 17033
Hershey Gardens 170 Hotel Road Hershey PA 17033

Answer 1

Если ваши данные начинаются в A2 -

В B2 используйте - =LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-1) чтобы получить клиента

в C2 используйте - =REPLACE(A2,1,LEN(B2),"") чтобы получить полный адрес

Answer 2

Попробуй это... В этом примере я предполагаю, что вы вставляете свои данные в ячейку A2, поэтому у вас есть строка 1 для заголовков. Когда вы разделяете столбцы, данные заполняют столбцы от A до M; где N является первым пустым столбцом; настроить ссылки на столбцы в соответствии с вашими данными.

• Используйте Data, Text to Columns для разделения по разделителю, выбрав пробел (как правило, слева направо)
• В столбце после вашего последнего (т. Е. N) поместите =countblanks(A2:M2) в строке 2; затем скопируйте эту формулу для всех строк (Excel будет автоматически увеличивать номер строки по мере необходимости).
• В О1 тип 2
• В P1 положить формулу =O2+2
• в типе O2 =IF($N2+O$1>COLUMN(N2),"",INDIRECT(CONCATENATE("C[-",$N2+O$1,"]"),FALSE))
• Скопируйте N2 и O2 для всех столбцов (номера строк должны обновляться автоматически)
• Скопируйте столбец O справа, чтобы повторить ту же формулу (с обновленными ссылками) в столбце AA (или за его пределами, если это требуется для ваших данных).
• Теперь вы должны увидеть, что столбцы O-AA содержат ваши данные, как если бы они были обратно разделены
• Выделите столбцы от O до AA, щелкните правой кнопкой мыши и выберите «Копировать» (или нажмите Ctrl + C).
• На новом листе щелкните ячейку A2, щелкните правой кнопкой мыши и выберите «Вставить значения» (также находится в разделе «Специальная вставка»). Теперь у вас есть лист только с теми результатами, которые вы хотите, и без формул, которые вы использовали для его создания.

Результаты

    19702   DE  Newark, Lane    Neurys  1   Hernandez   Gus Landscaping Hernandez           
    19380   PA  Chester,    West    Road    Mill    1000    Friedell    Steve   Club    Golf    Mill    Hershey's
    17033   PA  Hershey,    Rd  E.Derry 1000    Club    Country Hershey             
    17033   PA  Hershey Road    Hotel   170 Gardens Hershey                 

объяснение

• Разделенные столбцы разбивают данные на части, как вы уже знакомы.
• CountBlanks сообщает нам, сколько столбцов пустое; то есть, сколько столбцов (+1) нам нужно переместить влево, прежде чем мы встретим ячейку с данными; наша первая клетка в обратном расколе.
• 2 в O1 говорит, что из столбца O количество строк, которые нужно переместить влево к первым данным, является значением, определенным в столбце N для этой строки, плюс 2.
• Каждый раз, когда мы идем на 1 столбец справа от O, нам нужно идти еще на 2 влево, чтобы встретить следующий столбец в нашем обратном разбиении (P1 равно O1+ 2, потому что P равно 1 справа от O, и данные, которые мы ' re after - это 1 слева от данных, которые мы искали в O, поэтому 1+ 1 => 2).
• О2 имеет сложный бит.
  • Убедитесь, что мы не вышли за пределы первого ряда; если мы только что вернули бланк.
  • Создать относительную формулу; т.е. вместо определения именованной ячейки (такой как "A1") мы говорим, сколько строк / столбцов нужно переместить. R [-1] означает перемещение на 1 строку вверх, C [5] означает перемещение на 5 столбцов вправо, R [2] C [-3] означает перемещение на 2 строки вниз, 3 столбца влево. В нашем случае мы используем только столбцы, возвращая количество столбцов, определяемое N2 + O1 (или в зависимости от того, в какой ячейке мы находимся).
  • Ложь просто говорит, что мы используем эту относительную функциональность, а не обычный абсолютный метод ссылки на ячейку.
• Копирование формул для всех строк / столбцов просто заполняет остальные данные (то есть, в отличие от просто O2).
• Копирование и вставка значений данных означает, что вы можете избавиться от формул и просто иметь хорошие чистые данные для работы в будущем.