Мне нужно извлечь последние 7 числовых значений (не символов) из строки в ячейке Excel. Строка содержит имя файла (которое может быть смесью букв, цифр и тире), а затем 7 чисел, которые нужно переместить в другой столбец.
напримерJohnson Set1- 0 0 1 14 9 54 0
Числовые значения варьируются от 0 до 999, поэтому невозможно извлечь только последние 7 символов.
решение1
Итак, есть несколько трюков, которые вы можете использовать, чтобы получить то, что вы хотите. Я постараюсь объяснить их здесь, а затем дам вам формулу, которая использует эти трюки, чтобы дать вам то, что вы хотите.
Ключ в том, чтобы найти пробел перед началом списка чисел. Если ваш пример достаточно общий, этот пробел всегда будет 7-м пробелом справа. Проблема в том, что 7-м справа может быть любой пробел слева, потому что похоже, что нет правил о том, что имя файла должно иметь определенное количество пробелов. И функция SUBSTITUTEпозволяет вам указать, какой экземпляр (слева) строки следует заменить.
Чтобы получить 7-й пробел справа, нам нужно перевести его в пробелы слева. Для этого нам нужно знать количество пробелов во всей строке. Как только мы это получим, останется только вычитание.
Чтобы найти общее количество пробелов в строке, есть трюк с использованием функций SUBSTITUTEи LEN. Идея состоит в том, чтобы заменить все пробелы в строке ничем, а затем сравнить длину вывода с длиной исходной строки. Разница, конечно, заключается в количестве пробелов в исходной строке. Так, для строки в A1, следующее вернет количество пробелов:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Таким образом, нужный вам номер пробела слева всегда будет иметь еще 6 пробелов слева.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6
Теперь следующая проблема заключается в том, что SUBSTITUTEпозволяет вам указать только номер экземпляра, который вы хотите изменить. FIND, который был бы наиболее удобен для извлечения чисел, не имеет этой функции. Хитрость тогда заключается в том, чтобы использовать SUBSTITUTEдля генерации строки, которая позволяет вам использовать ее FINDтак, как вы хотите. Если вы замените 7-й пробел справа строкой, которая больше нигде не встречается в строке, у вас будет уникальная подстрока для поиска. Так, например, вы можете использовать следующее, чтобы заменить нужный пробел на "REPLACE!ME":
=SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6)
Теперь вам просто нужно найти индекс "REPLACE!ME" в строке и вернуть все вправо. Это можно сделать с помощью функций RIGHT, LEN, и FIND. Последняя функция немного повторяется, но она выполняет свою работу.
=RIGHT(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6),LEN(SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-FIND("REPLACE!ME",SUBSTITUTE(A1," ","REPLACE!ME",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-6))-LEN("REPLACE!ME")+1)