Научихме как да премахнем числови стойности от клетка в Excel 2016 и по -стари. Формулите, които използвахме, бяха малко сложни, но сега Excel 2019 и 365 са в играта.
Excel 2019 и 365 въвеждат някои нови функции, които улесняват задачата за премахване на нечислови знаци и извличане само на числови стойности в нова клетка. Ще използваме формули, които могат да ни помогнат в това, по -удобно.
Обща формула
| =TEXTJOIN("",ВЯРНО,ГРЕШКА(MID(jumble_text,ПОСЛЕДОВАТЕЛНОСТ(NumChars), 1) +0, "")) |
Jumbled_text: Това е изходният текст, от който искате да извлечете всички числови стойности.
NumChars: Това е общият брой знаци, които искате да обработите. Текстът jumble_text не трябва да има повече знаци от това число (символи и цифри комбинирани).
Нека видим пример, за да изясним нещата.
Пример: Премахване на нечислови знаци и извличане на всички числа
Така че тук имаме някакъв объркан текст. Този текст съдържа някои числа и някои нецифрени знаци. Трябва да се отърва от нечисловите знаци и да получа числови стойности само в колоната D.
Не очаквам общият брой знаци в объркания текст да е повече от 20. Така че стойността на NumChars е 20 тук. Ако е необходимо, можете да увеличите този брой.
Приложете горната обща формула тук, за да изтриете нечисловите знаци.
| =TEXTJOIN("",ВЯРНО,ГРЕШКА(MID(C3,ПОСЛЕДОВАТЕЛНОСТ(20),1)+0,"")) |
И когато натиснете бутона за въвеждане. Получавате всички нечислови знаци премахнати. Плъзнете надолу тази формула, за да премахнете знаци от низ от всички клетки в колона C3.
Как работи?
Първо нека видим как тази формула се решава стъпка по стъпка.
| 1-> TEXTJOIN("",ВЯРНО,ГРЕШКА(MID(C3,ПОСЛЕДОВАТЕЛНОСТ(20),1)+0,"")) |
| 2-> TEXTJOIN("",ВЯРНО,ГРЕШКА(MID("12asw12w123",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20},1)+0,"")) |
| 3-> TEXTJOIN("",ВЯРНО,ГРЕШКА({"1"; "2"; "a"; "s"; "w"; "1"; "2"; "w"; "1"; "2"; "3"; ""; "" ; ""; ""; ""; ""; ""; ""; ""}}+0,"")) |
| 4-> TEXTJOIN("",ВЯРНО,ГРЕШКА({1; 2; #VALUE!;#VALUE!;#VALUE!; 1; 2; #VALUE!; 1; 2; 3; #VALUE!;#VALUE!;…; #VALUE!}+0,"")) |
| 5-> TEXTJOIN("",ВЯРНО,{1;2;"";"";"";1;2;"";1;2;3;"";"";"";"";"";"";"";"";""}) |
| 6-> "1212123" |
Както можете да видите, формулата започва да се решава отвътре. Първо се решава функцията SEQUENCE. Тъй като сме преминали 20. Връща масив от числа, започващи от 1 до 20.
Този масив се сервира на MID функцията като начален номер. Функцията mid отива до всеки индекс в низ и разделя всеки знак. Можете да видите това в стъпка 3.
След това добавяме 0 към всеки знак. В Excel, ако се опитате да добавите номер към нечислови знаци, резултатът е #VALUE! Грешка. Така получаваме масив от числа и #VALUE! Грешки. Нецифрените символи вече са изчезнали.
Следващата функция IFERROR заменя всички грешки #VALUE с "" (празно). Сега оставаме с числови стойности и интервали.
И накрая, този масив се сервира на функцията TEXTJOIN. Функцията TEXTJOIN ги свързва и получаваме низ, който съдържа само числа в него.
Подобряване на формулата
Горната формула използва твърдо кодирано число за обработка на броя знаци (взехме двадесет). Но може да искате тя да бъде динамична. В такъв случай, правилно сте се досетили, ще използваме функцията LEN. За обработка ще са необходими точен брой знаци. Така формулата ще бъде.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jubled_text, SEQUENCE (LEN(jumble_text)),1)+0,"")) |
Тук функцията LEN автоматично ще открие точните цифри в буквено -цифровия низ. Това ще облекчи тежестта при определяне на максималния брой знаци.
Алтернатива на функцията SEQUENCE
Ако не искате да използвате функцията SEQUENCE, тогава можете да използвате комбинация от ROW и INDIRECT функция за генериране на последователни числа.
| = TEXTJOIN ("", TRUE, IFERROR (MID (jubled_text,ROW(НЕПРЯК("1:"&LEN(объркан_текст))),1)+0,"")) |
INDIRECT ще преобразува текста ("1:20") в действителен диапазон и след това функцията ROW ще изброи всички номера на редове от 1 до 20. (20 е само например. Може да бъде произволно число).
Така че да, момчета, ето как можете да изтръгнете нечисловите знаци от буквено -цифров низ в Excel. Надявам се, че бях достатъчно обяснителен и тази статия ви помогна. Ако имате въпроси относно тази тема или друга тема за Excel/VBA. Дотогава продължавай да се отличаваш.
Разделяне на числа и текст от низ в 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 | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.