Как экспортировать текст из ячейки Excel с буквенно-цифровыми значениями в другой столбец?

У меня есть несколько записей ячеек в столбце B. Они выглядят примерно так:

1050670||Target Optical  4226||6132||7132
1051752||Wal-Mart Vision Ctr  305095||6132||7132
1052470||Wal-Mart Vision Ctr  301891||6132||7132
1054354||Naval Ambulatory Care Ctr||6132||7132

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

Target Optical
Wal-Mart Vision Ctr
Wal-Mart Vision Ctr
Naval Ambulatory Care Ctr

Любая помощь ОЧЕНЬ приветствуется.


string excel formulas
person user3509034    schedule 02.01.2015    source источник
comment
Я попытался использовать функцию текста в столбцы, но это не помогло добиться желаемого результата. Я уверен, что это из-за неудачи с моей стороны.   -  person user3509034    schedule 02.01.2015

Ответы (3)


arrow_upward
0
arrow_downward

Если вы хотите игнорировать числа в псевдополе «Текст», вам придется тщательно проанализировать значение разделения на наличие символов в пределах ASCII 48-57.

Разбор только текста

Эта уродливая формула в B1:

=TRIM(LEFT(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0, MIN(INDEX(ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0)))+((CODE(MID((MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))<48)+(CODE(MID(UPPER(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0),ROW(INDIRECT("1:"&LEN(MID(A1, FIND("||", A1)+2, FIND("¶", SUBSTITUTE(A1, "||", "¶", 2))-FIND("||", A1)-2)&0))),1))>57))*1E+99,,))-1))

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

person Community    schedule 02.01.2015
comment
большое спасибо, это тоже работает - я очень ценю помощь с этим - person user3509034; 02.01.2015
comment
@ user3509034 - Мне неясно, хотели ли вы исключить числа в текстовом поле или нет. Возможно, вам следует отредактировать свой вопрос и включить числа в предпочтительный образец вывода. например Целевой оптический 4226 - person ; 02.01.2015
comment
@pnuts - я занят поиском определения ИДЕАЛЬНОГО. Возможно, у него есть альтернативное значение, когда он набирается заглавными буквами. - person ; 02.01.2015
comment
мои извинения. для ясности - обе формулы служат моим потребностям. после более тщательного рассмотрения формула, предоставленная @jeeped, является лучшей. опять же, я очень ценю помощь в этом. - person user3509034; 02.01.2015

arrow_upward
1
arrow_downward

Если одна из ваших записей находится в ячейке B1, используйте формулу

=MID(B1,FIND("||",B1)+2,FIND("||",B1,FIND("||",B1)+2)-FIND("||",B1)-2)

Скопируйте и вставьте по мере необходимости. Будьте осторожны с относительными/абсолютными ссылками.

Конечно, есть и другие варианты с VBA, но вы специально просили формулу.

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

=MID(B1,FIND(CHAR(1),SUBSTITUTE(B1,"|",CHAR(1),(A6-1)*2))+1,FIND(CHAR(1),SUBSTITUTE(B1,"|",CHAR(1),A6*2))-FIND(CHAR(1),SUBSTITUTE(B1,"|",CHAR(1),(A6-1)*2))-2)

Тоже громоздко, но работает для "любого" номера позиции (конечно, есть верхний предел, я его не проверял). Это адаптация из здесь. A6 содержит элемент #, который вы хотите выбрать. В вашем случае это 2. Если вы поставите три, вы получите «6132» для своей первой строки. Эта формула должна быть изменена для подходящего нахождения первого или последнего элемента.

EDIT: эти формулы не удаляют числа в конце целевого поля. Я пропустил этот момент, но оставлю ответ, так как он может быть полезен другим читателям. См. решение от Jeeped.

person sancho.s ReinstateMonicaCellio    schedule 02.01.2015
comment
Большое спасибо, это было ИДЕАЛЬНО. Именно то, что мне было нужно. Я люблю вас, ребята. - person user3509034; 02.01.2015
comment
@pnuts - у меня это сработало (я проверял перед публикацией). И это сработало для ОП. Если вы нашли причину, пожалуйста, напишите комментарий. - person sancho.s ReinstateMonicaCellio; 02.01.2015
comment
@pnuts - Excel 2010. Я пропустил удаление чисел в конце целевого поля, и я думаю, что вы тоже. Я не понимаю вашего вопроса. - person sancho.s ReinstateMonicaCellio; 02.01.2015
comment
Очень полезно. Особенно второе предложение. +1 отсюда. - person ; 02.01.2015

arrow_upward
0
arrow_downward

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

=MID(MID(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ( "||",A1,1)-2),1,ЕСЛИ(ЧИСЛО(ЗНАЧЕНИЕ(СРЕДН(ПОДСТАВИТЬ(СРЕДН(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||", A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("||",A1,1)-2)," ","¶",ДЛСТР(СРЕДН(A1,НАЙТИ("|| ",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("||",A1,1)-2))-ДЛСТР (ПОДСТАВИТЬ(СРЕДНИЙ(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("|| ",A1,1)-2)," ",""))),НАЙТИ("¶",SUBSTITUTE(MID(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("|| ",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("||",A1,1)-2)," ","¶",ДЛСТР(СРЕД(A1,НАЙТИ(" ||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("||",A1,1)-2)) -ДЛСТР(ПОДСТАВИТЬ(СРЕДН(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ(" ||",A1,1)-2)," ",""))))+1,50)))=ИСТИНА,НАЙТИ("¶",ПОДСТАВИТЬ(СРЕДНЕЕ(A1,НАЙТИ("||", A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ("||",A1,1)-2)," "," ¶",ДЛСТР(СРЕДН(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||",A1,1)+2)-НАЙТИ(" ||",A1,1)-2))-ДЛСТР(ПОДСТАВИТЬ(СРЕДН(A1,НАЙТИ("||",A1,1)+2,НАЙТИ("||",A1,НАЙТИ("||" ,A1,1)+2)-НАЙТИ("||",A1,1)-2)," ",""))))-1,50))

person Vijaykumar Shetye    schedule 17.08.2018