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