Функция Excel / макрос для извлечения чисел из списка через запятую

Question

Я пытаюсь извлечь числа из списка через запятую, используя функцию Excel

Данные выглядят так:

1,58,, Частная информация # 1, Через 59 секунд, ваша личная оценка, что окончательное значение будет $ 44,17

Если вышеуказанные данные находятся в ячейке A1, я хочу получить 59 в ячейке B1 и 44,17 в ячейке C1. Количество цифр номера может отличаться. Нужны только цифры в 5-м и 6-м полях, разделенных запятыми, и в каждом из этих полей будет только один номер. Для 5-го сегмента знак # всегда будет заключен в один и тот же текст (т. Е. Через xxx секунд). Для 6-го сегмента это всегда будут последние символы, и они всегда следуют за одним $

Я не знаком с Excel и буду признателен за любую помощь! Заранее спасибо.

Answer 1

Формула для С1:

=VALUE(MID(A1,SEARCH("$",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+1,LEN(A1)))

Предполагается, что в шестом сегменте есть только один знак доллара.
Символ бара может быть изменен на любой символ, гарантированно отсутствующий в тексте.
MID позволяет возвращать больше символов, чем существует (просто возвращает остальную часть текста; ленивое ПРАВО вместо вычисления количества возвращаемых символов используется начальная позиция).

Формула для B1:

=VALUE(MID(A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5,SEARCH("second",A1,SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)-(SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5)))

B1 упрощен с помощью вспомогательной ячейки (D1):

D1 =SEARCH("after",A1,FIND("|",SUBSTITUTE(A1,",","|",4)))+5 B1 =VALUE(MID(A1,D1,SEARCH("second",A1,D1)-D1)) , -D1))

Answer 2

Чтобы получить 59 и 44,17, я хотел бы предложить комбинацию UDF (VBA Macro) и формулы.

Как это устроено:

• Напишите текст в A1 .

• Скопируйте и вставьте этот код VBA как модуль с листом.

  Function INDEXLIST(strList As String, strSeparator As String, lngIndex As Long) As String
  
  Dim ListArray() As String
  ListArray() = Split(strList, strSeparator)
  INDEXLIST = ListArray(lngIndex - 1)
  
  End Function
  

• В B1 и B2 напишите поисковую позицию для 59 и 44.17 5th и 6th .

• Запишите эту формулу в C1 и заполните ее.

=INDEXLIST(A1,",",B1)

• Наконец, введите эту формулу в D1 и заполните ее.

  =LOOKUP(9.9E+307,--LEFT(MID(C1,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $C1&"1023456789")),999),ROW(INDIRECT("1:999"))))
  

• Вы получаете 59 и 44,17.

NB

• 9.9E+307 - это наибольшее число, которое Excel может хранить в ячейке. Часто упоминается как Biggest Number .

• LOOKUP пытается найти наибольшее значение, которое меньше или равно значению Lookup. Если значение поиска равно 9.9E+307 , то в диапазоне не может быть числа больше 9.9E+307 , поэтому при поиске будет найдено наибольшее значение в диапазоне.

  FIND({1,2,3,4,5,6,7,8,9,0}

• Функция FIND возвращает позицию данных, когда они находятся в диапазоне, который соответствует массиву значений, возвращаемых функцией ROW {1,2,3,4,5,6,7,8,9,0} .

• 999 установить максимум 3 цифры и Row(INDIRECT("1:999")`относится к ячейке данных.

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