Загальна формула
=MID(A1,1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)
Резюме
Щоб видалити останнє слово з текстового рядка, ви можете використовувати формулу, засновану на функції MID, за допомогою SUBSTITUTE, LEN та FIND. У наведеному прикладі формула в комірці B5 має вигляд:
=MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)
Пояснення
Ця формула використовує функцію MID для видалення останнього слова з текстового рядка. Основна проблема - з’ясувати, з чого починається останнє слово.
Формула трохи заплутана, але кроки прості. Спочатку ми підраховуємо, скільки пробілів існує в тексті, використовуючи LEN і SUBSTITUTE. Далі, ми використовуємо дещо незрозумілий аргумент "екземпляра" у функції ЗАМІННИЦЯ, щоб замінити останній пробіл тильдою (~). Нарешті, ми використовуємо FIND, щоб з’ясувати, де знаходиться тильда, і функцію MID, щоб відкинути все після тильди.
Працюючи зсередини назовні, ми використовуємо функції LEN і SUBSTITUTE для підрахунку кількості пробілів у наступному:
LEN(B5)-LEN(SUBSTITUTE(B5," ","")) // returns 6
Для тексту в B5 це повертає 6. Ця сторінка детальніше пояснює цю частину формули. Це говорить нам про те, що ми хочемо скоротити текст із 6-го пробілу.
Примітка: якщо слова розділені кількома пробілами, спочатку потрібно нормалізувати пробіли за допомогою функції TRIM.
Потім число 6 підключається до іншого ЗАМІННИКА як "номер екземпляра":
SUBSTITUTE(B5," ","~",6) // insert tilde
Тут ми замінюємо 6-й екземпляр пробілу ("") на тильду (~). Після запуску SUBSTITUTE він повертає такий рядок:
"It's been seven hours and fifteen~days"
Примітка: ми використовуємо тильду (~) лише тому, що це рідкісний символ. Ви можете використовувати будь-який вподобаний символ, якщо він не відображається у вихідному тексті.
Далі ми використовуємо функцію FIND для пошуку тильди:
FIND("~","It's been seven hours and fifteen~days")-1
FIND повертає 34, оскільки тильда є 34-м символом. Ми віднімаємо від цього числа 1, оскільки ми не хочемо включати останній пробіл у кінцевий результат. Тепер ми можемо спростити формулу, щоб:
=MID(B5,1,33) // extract final text
Потім функція MID повертає символи 1-33:
"It's been seven hours and fifteen"
З користувацьким роздільником
Цю саму формулу можна використовувати з різним роздільником. Наприклад, щоб видалити весь текст після останньої косої риски "/", ви можете використовувати:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)
Вилучити останні n слів
Ви можете адаптувати формулу, щоб видалити останні 2 слова, останні 3 слова тощо. Загальна форма:
=MID(A1,1,FIND("~",SUBSTITUTE(A1,d,"~",LEN(A1)-LEN(SUBSTITUTE(A1,d,""))-(n-1)))-1)
де d - роздільник, а n - кількість слів для видалення.
