✅ 7 невероятни неща, които Excel текстът до колони може да направи за вас

Съдържание

Къде да намерите текст в колони в Excel

Text to Columns е невероятна функция в Excel, която заслужава много повече кредит, отколкото обикновено получава.

Както подсказва името му, той се използва за разделяне на текста на няколко колони. Например, ако имате име и фамилия в една и съща клетка, можете да използвате това, за да ги разделите бързо на две различни клетки.

Това може да бъде наистина полезно, когато получавате данните си от бази данни или ги импортирате от други файлови формати като текст или CSV.

В този урок ще научите за много полезни неща, които могат да бъдат направени с помощта на Text to Columns в Excel.

Къде да намерите текст в колони в Excel

За достъп до Text to Columns, изберете набора от данни и отидете на Data → Data Tools → Text to Columns.

Това ще отвори съветника за преобразуване на текст в колони.

Този съветник има три стъпки и предприема някои потребителски въвеждане, преди да раздели текста на колони (ще видите как тези различни опции могат да се използват в примерите по -долу).

За достъп до текст в колони можете да използвате и клавишната комбинация - ALT + A + E.

Сега нека се потопим и да видим някои невероятни неща, които можете да направите с Text to Columns в Excel.

Пример 1 - Разделяне на имена на име и фамилия

Да предположим, че имате набор от данни, както е показано по -долу:

За да разделите бързо името и фамилията и да ги получите в отделни клетки, следвайте стъпките по -долу:

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрано Delimited (което също е избор по подразбиране). Щракнете върху Напред.
В стъпка 2 изберете „Разделител“ като разделител. Ако подозирате, че между имената може да има двойни/тройни последователни интервали, също изберете опцията „Третирайте последователните разделители като едно“. Щракнете върху Напред.
В стъпка 3 изберете клетката дестинация. Ако не изберете клетка дестинация, тя ще замени съществуващия ви набор от данни с първото име в първата колона и фамилията в съседната колона. Ако искате да запазите оригиналните данни непокътнати, създайте копие или изберете друга клетка за местоназначение.
Щракнете върху Край.

Това незабавно ще ви даде резултатите с първото име в една колона и фамилията в друга колона.

Забележка:

Тази техника работи добре, когато името се състои само от име и фамилия. В случай, че има инициали или средни имена, това може да не работи. Щракнете тук за подробно ръководство за това как да се справяте със случаи с различни комбинации от имена.
Резултатът, който получавате от използването на функцията Текст в колони, е статичен. Това означава, че ако има промени в първоначалните данни, ще трябва да повторите процеса, за да получите актуализирани резултати.

Прочетете също: Как да сортирате по фамилия в Excel

Пример 2 - Разделяне на имейл имена на потребителско име и име на домейн

Текст в колони ви позволява да изберете свой собствен разделител за разделяне на текст.

Това може да се използва за разделяне на имейл адресите на потребителски имена и имена на домейни, тъй като те са разделени от знака @.

Да предположим, че имате набор от данни, както е показано по -долу:

Това са някои измислени имейл идентификатори на някои готини супергерои (с изключение на себе си, аз съм просто обикновен човек, който губи време в Netflix).

Ето стъпките за разделяне на тези потребителски имена и имена на домейни с помощта на функцията Текст в колони.

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрано Delimited (което е и селекцията по подразбиране). Щракнете върху Напред.
В стъпка 2 изберете Други и въведете @ в полето вдясно от него. Не забравяйте да отмените избора на всяка друга опция (ако е отметнато). Щракнете върху Напред.
Променете целевата клетка на тази, в която искате резултата.
Щракнете върху Край.

Това ще раздели имейл адреса и ще ви даде името и фамилията в отделни клетки.

Пример 3 - Вземете основния домейн от URL

Ако работите с уеб URL адреси, понякога може да се наложи да знаете общия брой уникални root домейни.

Например, в случай на http://www.google.com/example1 и http://google.com/example2, основният домейн е същият, който е www.google.com

Да предположим, че имате набор от данни, както е показано по -долу:

Ето стъпките за получаване на основния домейн от тези URL адреси:

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрано Delimited (което е и селекцията по подразбиране). Щракнете върху Напред.
В стъпка 2 изберете Други и въведете / (наклонена черта напред) в полето вдясно от него. Не забравяйте да отмените избора на всяка друга опция (ако е отметнато). Щракнете върху Напред.
Променете целевата клетка на тази, в която искате резултата.
Щракнете върху Край.

Това ще раздели URL адреса и ще ви даде основния домейн (в третата колона, тъй като преди него имаше две наклонени черти напред).

Сега, ако искате да намерите броя уникални домейни, просто премахнете дубликатите.

Забележка: Това работи добре, когато имате всички URL адреси, които имат http: // в началото. Ако това не стане, тогава ще получите основния домейн в самата първа колона. Добра практика е тези URL адреси да бъдат последователни, преди да използвате текст в колони.

Пример 4 - Конвертиране на невалидни формати на дати във валидни формати на дата

Ако получавате данните си от бази данни като SAP/Oracle/Capital IQ или ги импортирате от текстов файл, има вероятност форматът на датата да е неправилен (т.е. формат, който Excel не счита за дата).

Има само няколко формата, които Excel може да разбере, и всеки друг формат трябва да бъде преобразуван във валиден формат, за да се използва в Excel.

Да предположим, че имате дати в долния формат (които не са във валидния формат за дата на Excel).

Ето стъпките за преобразуването им във валидни формати за дата:

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрано Delimited (което е и селекцията по подразбиране). Щракнете върху Напред.
В стъпка 2 се уверете, че е избрана опция НЕ разделител. Щракнете върху Напред.
Във формата на данни в колона изберете Дата и изберете желания формат (DMY ще означава дата месец и година). Също така променете целевата клетка на тази, в която искате резултата.
Щракнете върху Край.

Това незабавно би преобразувало тези невалидни формати за дата във валидни формати за дата, които можете да използвате в Excel.

Пример 5 - Конвертиране на текст в числа

Понякога, когато импортирате данни от бази данни или други файлови формати, числата се преобразуват в текстов формат.

Има няколко начина това да се случи:

Имайки апостроф преди числото. Това води до това, че номерът се третира като текст.
Получаване на числа в резултат на текстови функции като НАЛЯВО, НАДЯСНО или MID.

Проблемът с това е, че тези числа (които са в текстов формат) се игнорират от функциите на Excel като SUM и AVERAGE.

Да предположим, че имате набор от данни, както е показано по -долу, където числата са в текстов формат (имайте предвид, че те са подравнени вляво).

Ето стъпките за използване на текст в колони за преобразуване на текст в числа

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрано Delimited (което е и селекцията по подразбиране). Щракнете върху Напред.
В стъпка 2 се уверете, че е избрана опция НЕ разделител. Щракнете върху Напред.
Във формата на данните в колоната изберете Общи. Също така променете целевата клетка на тази, в която искате резултата.
Щракнете върху Край.

Това би преобразувало тези числа обратно в общ формат, който сега може да се използва във формули.

Пример 6 - Извличане на първите пет знака от низ

Понякога може да се наложи да извлечете първите няколко знака от низ. Това може да е случаят, когато имате данни за транзакции и първите пет знака (или всеки друг брой знаци) представляват уникален идентификатор.

Например в показания по -долу набор от данни първите пет знака са уникални за продуктовата линия.

Ето стъпките за бързо извличане на първите пет знака от тези данни с помощта на текст в колони:

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 се уверете, че е избрана Фиксирана ширина. Щракнете върху Напред.
В стъпка 2, в секцията Преглед на данни, плъзнете вертикалната линия и я поставете след 5 знака в текста. Щракнете върху Напред.
Променете целевата клетка на тази, в която искате резултата.
Щракнете върху Край.

Това ще раздели вашия набор от данни и ще ви даде първите пет знака от всеки идентификатор на транзакция в една колона и ще остави всички във втората колона.

Забележка: Можете също да зададете повече от една вертикална линия, за да разделите данните в повече от 2 колони. Просто щракнете навсякъде в областта за визуализация на данни и плъзнете курсора, за да зададете разделителя.

Пример 7 - Преобразуване на числа със знак за завъртане на минус в отрицателни числа

Въпреки че това не е нещо, което може да срещнете често, но понякога може да се окажете, че поправяте числата със заден знак минус и правите тези числа отрицателни.

Текстът в колони е идеалният начин да подредите това.

Да предположим, че имате набор от данни, както е показано по -долу:

Ето стъпките за преобразуване на тези последващи минуси в отрицателни числа:

Изберете набора от данни.
Отидете на Данни → Инструменти за данни → Текст в колони. Това ще отвори съветника за преобразуване на текст в колони.
В стъпка 1 кликнете върху „Напред“.
В стъпка 2 кликнете върху „Напред“.
В стъпка 3 щракнете върху бутона Разширени.
В диалоговия прозорец Разширени настройки за импортиране на текст изберете опцията „Заключващ минус за отрицателно число“. Щракнете върху OK.
Задайте клетката дестинация.
Щракнете върху Край.