Научихме как да премахнем числови стойности от клетка в Excel 2016 и по -стари. Формулите, които използвахме, бяха малко сложни, но сега Excel 2019 и 365 са в играта с нови играчки, имам предвид функции.
Excel 2019 и 365 въвеждат някои нови функции (TEXTJOIN и SEQUENCE), които могат да улеснят задачата за премахване на цифрови знаци и извличане само на нецифрени стойности в нова клетка. Ще използваме формули, които могат да ни помогнат в това, по -удобно.
Обща формула
| =TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА(MID(jumble_text,ПОСЛЕДОВАТЕЛНОСТ(NumChars), 1) +0),MID(jumble_text,ПОСЛЕДОВАТЕЛНОСТ(NumChars), 1), ""))) |
Jumbled_text: Това е изходният текст, от който искате да извлечете всички числови стойности.
NumChars: Това е общият брой знаци, които искате да обработите. Текстът jumble_text не трябва да има повече знаци от това число (символи и цифри комбинирани).
Нека видим пример, за да изясним нещата.
Пример: Премахване на числови знаци и извличане на всички азбуки и нечислови знаци.
Тук имаме буквено -цифров текст. Този текст съдържа някои числа и някои нецифрени знаци. Трябва да се отърва от числовите знаци и да получа само азбуки и други стойности на знаци, в колоната D.
Не очаквам общият брой знаци в объркания текст да е повече от 100. Така че стойността на NumChars е 100 тук. Можете да увеличите или намалите този брой, ако е необходимо.
Приложете горната обща формула тук, за да изтриете числовите знаци.
| =TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА(MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1)+0),MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
Когато натиснете бутона за въвеждане, всички цифрови знаци се премахват и остават само азбучни стойности. Плъзнете надолу тази формула, за да премахнете числа от низ от всички клетки в колона B.
Как работи?
Първо нека видим как тази формула се решава стъпка по стъпка.
| 1-> TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА(MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1)+0),MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
| 2-> TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА(MID(„Това 1 е… сайт“,{1,2,3,… 100},1)+0),MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
| 3-> TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА({"T"; "h"; "i"; "s"; "", "1"… ""; ""}+0),MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
| 4-> TEXTJOIN("",ВЯРНО,АКО(ГРЕШКА({#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}),MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
| 5-> TEXTJOIN("",ВЯРНО,АКО({TRUE; TRUE; TRUE; TRUE; FALSE …; TRUE},MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1),""))) |
| 6-> TEXTJOIN("",ВЯРНО,{"Това";""… .;""}) |
| 7-> "Това е уебсайт номер едно" |
За по -лесно четене не съм написал целия масив. Използвах точки (…), за да посоча дълги масиви.
Както можете да видите, формулата започва да се решава отвътре. Първо се решава функцията SEQUENCE. Тъй като сме прехвърлили 100 като брой знаци. Той връща масив от числа, започващи от 1 до 100.
Този масив се сервира на MID функцията като начален номер. Функцията mid отива до всеки индекс в низ и разделя всеки знак в масив. Можете да видите това в стъпка 3. Не съм показал целия масив, тъй като той ще заема твърде много място. {"T"; "h"; "i"; "s"; "", "1"… ""; ""}
След това добавяме 0 към всеки знак. В Excel, ако се опитате да добавите номер към нечислови знаци, резултатът е #VALUE! Грешка. Така получаваме масив от числа и #VALUE! Грешки. {#VALUE!;#VALUE!;#VALUE!;#VALUE!; 1…; #VALUE!}
Този масив се обслужва от ГРЕШКА функция. Както знаете, функцията ISERROR връща TRUE за грешки и FALSE за стойности без грешки. Следователно получаваме масив от TRUE и FALSE. TRUE за нечислови знаци и FALSE на числа. {TRUE; TRUE; TRUE; TRUE; FALSE …; TRUE}.
Същото се случва и с изявленията на TRUE раздел на IF (MID(В3,ПОСЛЕДОВАТЕЛНОСТ(100),1)). Той връща масив от всички знаци от буквено -цифров низ в B3.
Сега за всеки израз TRUE функцията IF връща съответния знак от масива на истинската секция. За FALSE, IF връща празно (""). Сега ще имате масив, който не съдържа никакъв цифров знак. {"Това";""… .;""}.
И накрая, този масив се обслужва от TEXTJOIN функция. Тази функция свързва дадени текстове помежду си, игнорирайки празни стойности, с даден разделител. Следователно получаваме низ, който не съдържа никакви цифрови знаци. Това е уебсайт номер едно.
Подобряване на формулата
Горната формула използва твърдо кодирано число за обработка на броя знаци (взехме 100). Но може да искате тя да бъде динамична. В такъв случай, познахте правилно, можете да използвате функцията LEN. За обработка ще отнеме точния брой знаци. Така формулата ще бъде.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jubled_text, SEQUENCE)LEN(jumble_text), 1) +0), MID (объркан_текст, SEQUENCE (LEN(jumble_text),1),""))) |
Тук функцията LEN автоматично ще открие точните цифри в буквено -цифровия низ. Това ще освободи от тежестта за определяне на максималния брой знаци.
Алтернатива на функцията SEQUENCE
Ако не искате да използвате функцията SEQUENCE, тогава можете да използвате комбинация от ROW и INDIRECT функция за генериране на последователни числа.
| = TEXTJOIN ("", TRUE, IF (ISERROR (MID (jubled_text,ROW(НЕПРЯК("1:"&LEN(NumChars))), 1) +0), MID (объркан_текст,ROW(НЕПРЯК("1:"&LEN(NumChars))),1),""))) |
INDIRECT ще преобразува текста ("1: 100") в действителен диапазон и след това функцията ROW ще изброи всички номера на редове от 1 до 100. (100 е само пример. Може да бъде произволно число).
Така че да, момчета, ето как можете да изтръгнете нечисловите знаци от буквено -цифров низ в Excel. Надявам се, че бях достатъчно обяснителен и тази статия ви помогна. Ако имате въпроси относно тази тема или друга тема за Excel/VBA. Дотогава продължавай да се отличаваш.
Как да премахнете нечислови знаци от клетки в Excel 2019: За да премахнем нецифрени знаци от буквено -цифров низ в Excel, използваме новата функция TEXTJOIN. Ивица от нецифрени знаци от низ може да ни помогне да изчистим данните си за по -добър анализ на данните. Така че ето как го правите
Разделяне на числа и текст от низ в Excel 2016 и по -стари: Когато нямахме функция TEXTJOIN, използвахме функции НАЛЯВО и НАДЯСНО с някои други функции, за да разделим числови и нечислови знаци от низ.
Извличане на текст от низ в Excel с помощта на лявата и дясната функция на Excel: За да премахнем текст в excel от низ можем да използваме функциите на Excel наляво и надясно. Тези функции ни помагат да изрязваме низове динамично. \
Премахнете водещите и крайните интервали от текста в Excel: Предните и задните пространства трудно се разпознават визуално и могат да объркат данните ви. Премахването на тези знаци от низ е основна и най -важна задача при почистването на данни. Ето как можете да го направите лесно в Excel.
Премахване на знаци отдясно: За да премахнем знаци отдясно на низ в Excel, използваме функцията LEFT. Да, функцията НАЛЯВО. Функцията LEFT запазва дадения брой знаци от LEFT и премахва всичко от дясно.
Премахнете нежеланите знаци в Excel: За да премахнем нежеланите знаци от низ в Excel, използваме функцията SUBSTITUTE. Функцията SUBSTITUTE замества дадените знаци с друг зададен знак и произвежда нов променен низ.
Как да премахнете текст в Excel, започвайки от позиция в Excel: За да премахнем текст от начална позиция в низ, използваме функцията REPLACE на Excel. Тази функция ни помага да определим началната позиция и броя на символите, които да изтрием.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
