Когато работите с текстови данни в Excel, може да се наложи да премахнете текста преди или след определен символ или текстов низ.
Например, ако имате име и данни за обозначаване на хора, може да искате да премахнете обозначението след запетаята и да запазите само името (или обратно, където запазвате обозначението и премахвате името).
Понякога това може да стане с проста формула или бързо намиране и замяна, а понякога се нуждае от по -сложни формули или решения.
В този урок ще ви покажа как да премахнете текста преди или след определен символ в Excel (използвайки различни примери).
Така че нека започнем с няколко прости примера.
Премахване на текст след знак с помощта на Find and Replace
Ако искате бързо да премахнете целия текст след конкретен текстов низ (или преди текстов низ), можете да направите това с помощта на Find and Replace и символи с заместваща карта.
Да предположим, че имате набор от данни, както е показано по -долу и искате да премахнете обозначението след запетаята и да запазите текста преди запетаята.
По -долу са описани стъпките за това:
- Копирайте и поставете данните от колона А в колона В (това е и за запазване на оригиналните данни)
- Когато клетките в колона B са избрани, щракнете върху раздела Начало
- В групата Редактиране щракнете върху опцията Намиране и избор
- В опциите, които се появяват в падащото меню, щракнете върху опцията Замяна. Това ще отвори диалоговия прозорец Намиране и замяна
- В полето „Намерете какво“ въведете ,* (т.е. запетая, последвана от звездичка)
- Оставете полето „Замяна с“ празно
- Кликнете върху бутона Замяна на всички
Горните стъпки ще намерят запетаята в набора от данни и ще премахнат целия текст след запетаята (включително запетаята).
Тъй като това замества текста от избраните клетки, добра идея е да копирате текста в друга колона и след това да извършите тази операция за намиране и замяна, или да създадете резервно копие на вашите данни, така че оригиналните данни да са непокътнати
Как работи това?
* (звездичка) е заместващ знак, който може да представлява произволен брой знаци.
Когато го използвам след запетаята (в полето „Намери какво“) и след това кликвам върху бутона Замени всички, той намира първата запетая в клетката и я счита за съвпадение.
Това е така, защото знакът звездичка (*) се счита за съвпадение с целия текстов низ, който следва запетаята.
Така че, когато натиснете бутона за смяна на всички, той замества запетаята и целия текст, който следва.
Забележка: Този метод работи добре, тогава имате само една запетая във всяка клетка (както в нашия пример). В случай, че имате няколко запетаи, този метод винаги ще намери първата запетая и след това ще премахне всичко след нея. Така че не можете да използвате този метод, ако искате да замените целия текст след втората запетая и да запазите първата такава, каквато е.В случай, че искате да премахнете всички знаци преди запетаята, променете записа на полето find what, като поставите знак със звездичка пред запетаята (*, вместо,*)
Премахване на текст с помощта на формули
Ако имате нужда от по -голям контрол върху това как да намерите и замените текст преди или след определен символ, по -добре е да използвате вградените текстови формули в Excel.
Да предположим, че имате следния набор от данни, където искате да премахнете целия текст след запетаята.
По -долу е формулата за това:
= НАЛЯВО (A2, FIND (",", A2) -1)
Горната формула използва функцията FIND, за да намери позицията на запетаята в клетката.
След това този номер на позиция се използва от функцията НАЛЯВО за извличане на всички знаци преди запетаята. Тъй като не искам запетая като част от резултата, извадих 1 от получената стойност на формулата Find.
Това беше прост сценарий.
Да вземем една малко сложна.
Да предположим, че имам този набор от данни по -долу, където искам да премахна целия текст след втората запетая.
Ето формулата, която ще направи това:
= НАЛЯВО (A2, НАМЕРИ ("!", ЗАМЕСТИТЕЛ (A2, ",", "!", 2))-1)
Тъй като този набор от данни има множество запетаи, не мога да използвам функцията FIND, за да получа позицията на първата запетая и да извлека всичко вляво от нея.
Трябва по някакъв начин да разбера позицията на втората запетая и след това да извлека всичко, което е вляво от втората запетая.
За да направя това, използвах функцията SUBSTITUTE, за да променя втората запетая на удивителен знак. Сега това ми дава уникален характер в клетката. Вече мога да използвам позицията на удивителен знак, за да извлека всичко вляво от втората запетая.
Тази позиция на удивителен знак се използва във функцията НАЛЯВО, за да извлече всичко преди втората запетая.
Дотук добре!
Но какво ще стане, ако в набора от данни има несъвместим брой запетаи.
Например в набора от данни по -долу някои клетки имат две запетаи, а някои клетки имат три запетаи и трябва да извлека целия текст преди последната запетая.
В този случай трябва по някакъв начин да разбера позицията на последното появяване на запетаята и след това да извлека всичко вляво от нея.
По -долу е формулата, която ще направи това
= НАЛЯВО (A2, НАМЕРИ ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Горната формула използва функцията LEN, за да намери общата дължина на текста в клетката, както и дължината на текста без запетая.
Когато извадя тези две стойности, това ми дава общия брой запетаи в клетката.
Така че, това ще ми даде 3 за клетка А2 и 2 за клетка А4.
След това тази стойност се използва във формулата SUBSTITUTE, за да замени последната запетая с удивителен знак. И тогава можете да използвате функцията ляво извлечете всичко, което е вляво от удивителен знак (където е била последната запетая)
Както можете да видите в примерите, използването на комбинация от текстови формули ви позволява да се справите с много различни ситуации.
Освен това, тъй като резултатът е свързан с оригиналните данни, когато промените първоначалните данни, резултатът автоматично ще се актуализира.
Премахване на текст с помощта на Flash Fill
Flash Fill е инструмент, който е представен в Excel 2013 и е достъпен във всички версии след това.
Той работи чрез идентифициране на модели, когато ръчно въвеждате данните и след това екстраполирате, за да ви даде данните за цялата колона.
Така че, ако искате да премахнете текста преди или след конкретен знак, просто трябва да покажете на флаш фея как би изглеждал резултатът (като го въведете ръчно няколко пъти), а запълването с флаш автоматично ще разбере модела и ще ви даде резултатите.
Нека ви покажа това с пример.
По -долу имам набор от данни, където искам да премахна целия текст след запетаята.
Ето стъпките за това с Flash Fill:
- В клетка B2, която е съседната колона на нашите данни, въведете ръчно „Jeffery Haggins“ (което е очакваният резултат)
- В клетка В3 въведете „Тим Скот“ (което е очакваният резултат за втората клетка)
- Изберете диапазона B2: B10
- Щракнете върху раздела Начало
- В групата Редактиране щракнете върху падащото меню Запълване
- Щракнете върху Flash Fill
Горните стъпки ще ви дадат резултат, както е показано по -долу:
Можете също да използвате клавишната комбинация Flash Fill Control + E след като изберете клетките в колоната с резултати (колона В в нашия пример)
Flash Fill е прекрасен инструмент и в повечето случаи е в състояние да разпознае модела и да ви даде правилния резултат. но в някои случаи може да не е в състояние да разпознае модела правилно и може да ви даде грешни резултати.
Затова не забравяйте да проверите отново резултатите от Flash Fill
И точно както сме премахнали целия текст след конкретен знак, използвайки флаш запълване, можете да използвате същите стъпки, за да премахнете текста преди конкретен знак. просто покажете ръчно запълване как резултатът трябва да изглежда като моя интернет ръчно в съседната колона и това ще направи останалото.
Премахване на текст с помощта на VBA (персонализирана функция)
Цялата концепция за премахване на текста преди или след определен символ зависи от намирането на позицията на този знак.
Както се вижда по -горе, намирането на последното появяване на този знак означава добро използване на смес от формули.
Ако това е нещо, което трябва да правите доста често, можете да опростите този процес, като създадете персонализирана функция с помощта на VBA (наречена User Defined Functions).
След като бъде създаден, можете да използвате тази функция отново и отново. Също така е много по -опростен и по -лесен за използване (тъй като по -голямата част от тежкото повдигане се извършва от VBA кода в задната част).
Под VBA кода, който можете да използвате за създаване на персонализирана функция в Excel:
Функция LastPosition (rCell As Range, rChar As String) 'Тази функция дава последната позиция на посочения символ' Този код е разработен от Sumit Bansal (https://trumpexcel.com) Dim rLen As Integer rLen = Len (rCell) За i = rLen To 1 Step -1 If Mid (rCell, i - 1, 1) = rChar Тогава LastPosition = i - 1 Изход от функцията End If Next i End Function
Трябва да поставите VBA кода в обикновен модул в VB Editor или в личната работна книга за макроси. След като го имате там, можете да го използвате като всяка друга обикновена функция на работен лист в работната книга.
Тази функция приема 2 аргумента:
- Препратката към клетката, за която искате да намерите последното появяване на конкретен знак или текстов низ
- Символ или текстов низ, чиято позиция трябва да намерите
Да предположим, че сега имате следния набор от данни и искате да премахнете целия текст след последната запетая и да имате само текста преди последната запетая.
По -долу е формулата, която ще направи това:
= НАЛЯВО (A2, LastPosition (A2, ",")-1)
В горната формула съм посочил да намеря позицията на последната запетая. В случай, че искате да намерите позицията на друг символ или текстов низ, трябва да го използвате като втори аргумент във функцията.
Както можете да видите, това е много по -кратко и по -лесно за използване, в сравнение с формулата за дълъг текст, която използвахме в предишния раздел
Ако поставите кода VBA в модул в работна книга, ще можете да използвате само тази персонализирана функция в тази конкретна работна книга. Ако искате да използвате това във всички работни книги във вашата система, трябва да копирате и поставите този код в личната работна книга за макроси.
Това са някои от имейлите, които можете да използвате за бързо премахване на текста преди или след определен символ в Excel.
Ако това е просто еднократно нещо, тогава можете да направите това, като използвате функцията „намери и замени“. какво ще стане, ако е малко по -сложно, тогава трябва да използвате комбинация от вградени формули на Excel или дори да създадете своя собствена формула с помощта на VBA.
Надявам се, че сте намерили този урок за полезен.
