Импортирали ли сте някога или въвеждате данни в Excel, които съдържат водещи нули, например 00123, или големи числа, като например 1234 5678 9087 6543? Примери за това са номера на социални осигуровки, телефонни номера, номера на кредитни карти, кодове на продукти, номера на сметки или пощенски кодове. Excel автоматично премахва водещите нули и преобразува големи числа в научна нотация, например 1,23E+15, за да позволи на формулите и математическите операции да работят върху тях. Тази статия разглежда как да запазите данните в първоначалния им формат, който Excel третира като текст.
Преобразуване на числа в текст, когато импортирате текстови данни
Използвайте средата Get & Transform (Power Query) на Excel, за да форматирате отделни колони като текст, когато импортирате данни. В този случай импортираме текстов файл, но стъпките за трансформация на данни са същите за данните, импортирани от други източници, като например XML, уеб, JSON и др.
-
Щракнете върху раздела Данни и след това върху От текст/CSV до бутона Получаване на данни . Ако не виждате бутона Получаване на данни , отидете на Нова заявка > От файл > От текст и намерете своя текстов файл, след което натиснете Импортирай.
-
Excel ще зареди вашите данни в екран за визуализация. Натиснете Редактиране в екрана за визуализация, за да заредите Редактор на Power Query.
-
Ако някои колони трябва да бъдат конвертирани в текст, изберете колоната, която искате да конвертирате, като щракнете върху заглавката на колоната, след което отидете на Начало > Трансформация > тип данни , > изберете Текст.
Съвет: Можете да изберете няколко колони с Ctrl+щракване с левия бутон.
-
След това щракнете върху Замести текущия в диалоговия прозорец Промяна на типа колона и Excel ще преобразува избраните колони в текст.
-
Когато сте готови, Щракнете върху Затвори & Зареждане и Excel ще върне данните от заявката в работния лист.
Ако данните ви се променят в бъдеще, можете да отидете в Данни > Обновяване и Excel автоматично ще актуализира данните ви и ще приложи трансформациите вместо вас.
В Excel 2010 и 2013 има два метода за импортиране на текстови файлове и конвертиране на числа в текст. Препоръчителният метод е да използвате Power Query, който е наличен, ако изтеглите добавката Power Query. Ако не можете да изтеглите добавката Power Query, можете да използвате съветника за импортиране на текст. В този случай импортираме текстов файл, но стъпките за трансформация на данни са същите за данните, импортирани от други източници, като например XML, уеб, JSON и др.
-
Щракнете върху раздела Power Query на лентата, след което изберете Получаване на външни данни > От текст.
-
Excel ще зареди вашите данни в екран за визуализация. Натиснете Редактиране в екрана за визуализация, за да заредите Редактор на Power Query.
-
Ако някои колони трябва да бъдат конвертирани в текст, изберете колоната, която искате да конвертирате, като щракнете върху заглавката на колоната, след което отидете на Начало > Трансформация > тип данни , > изберете Текст.
Съвет: Можете да изберете няколко колони с Ctrl+щракване с левия бутон.
-
След това щракнете върху Замести текущия в диалоговия прозорец Промяна на типа колона и Excel ще преобразува избраните колони в текст.
-
Когато сте готови, Щракнете върху Затвори & Зареждане и Excel ще върне данните от заявката в работния лист.
Ако данните ви се променят в бъдеще, можете да отидете в Данни > Обновяване и Excel автоматично ще актуализира данните ви и ще приложи трансформациите вместо вас.
Използване на формат по избор за запазване на водещите нули
Ако искате да решите проблема в работната книга, тъй като тя не се използва от други програми като източник на данни, можете да използвате потребителски или специален формат, за да запазите водещите нули. Това работи за числовите кодове, които съдържат по-малко от 16 цифри. Освен това можете да форматирате вашите числови кодове с тирета или други препинателни знаци. Например за да направите даден телефонен номер по-четлив, можете да добавите тире между международния код, кода на страната/региона, регионалния код, префикса и последните няколко номера.
|
Код на номера |
Пример |
Потребителски числов формат |
|---|---|---|
|
Социални |
012345678 |
000—00—0000 |
|
Телефон |
0012345556789 |
00-0-000-000-0000 |
|
Пощенски |
00123 |
00000 000 000 000 |
Стъпки
-
Изберете клетката или диапазона от клетки, които искате да форматирате.
-
Натиснете Ctrl+1 , за да заредите диалоговия прозорец Форматиране на клетки .
-
Изберете раздела Число , след което в списъка Категория щракнете върху По избор и след това в полето Тип въведете числовия формат, например 000-00-0000 за код на социално-осигурителен номер, или 00 0000 за петцифрен пощенски код.
Съвет: Можете също да щракнете върху Специални и след това да изберете Пощенски код, Пощенски код + 4, Телефонен номер или Социално-осигурителен номер.
Намерете повече информация за кодовете по избор, вижте Създаване или изтриване на числов формат по избор.
Забележка: Това няма да възстанови водещите нули, които са премахнати преди форматирането. Това ще засегне само числата, които са въведени след прилагането на формата.
Използване на функцията TEXT за прилагане на формат
Можете да използвате празна колона до вашите данни и да използвате функцията TEXT , за да я конвертирате в желания формат.
|
Код на номера |
Пример (В клетка A1) |
TEXT функция и нов формат |
|---|---|---|
|
Социални |
012345678 |
=TEXT(A1;"000-00-0000") |
|
Телефон |
0012345556789 |
=TEXT(A1;"00-0-000-000-0000") |
|
Пощенски |
00123 |
=TEXT(A1;"00000") |
Номерата на кредитните карти се закръгляват надолу
Excel има максимална точност от 15 значещи цифри, което означава, че за всяко число, съдържащо 16 или повече цифри, като например номер на кредитна карта, всички числа след 15-тата цифра се закръгляват надолу до нула. В случай на кодове на числа, които са с 16 цифри или по-големи, трябва да използвате текстов формат. За да направите това, можете да направите едно от следните две неща:
-
Форматиране на колоната като текст
Изберете диапазона от данни и натиснете Ctrl+1 , за да стартирате диалоговия прозорец Форматиране на > клетки . В раздела Число щракнете върху Текст.
Забележка: Това няма да промени числата, които вече са въведени. Това ще засегне само числата, които са въведени след прилагането на формата.
-
Използване на знака апостроф
Можете да въведете апостроф (') пред числото и Excel ще го третира като текст.
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.
