Перетворення чисел, що зберігаються у вигляді тексту, у числа в Excel

Числа, які зберігаються у вигляді тексту, можуть призвести до непередбачених результатів, таких як нерозрахована формула, що відображається замість результату.

У більшості випадків Excel розпізнає це, і ви побачите оповіщення поруч із осередком, в якому числа зберігаються у вигляді тексту. Якщо ви бачите оповіщення:

Виберіть комірки, які потрібно перетворити, а потім виберіть повідомлення про помилку.

Для отримання додаткових відомостей про форматування чисел та тексту в Excel див. у статті Форматування чисел та тексту.

Примітки: Якщо кнопка сповіщення недоступна, можна увімкнути сповіщення про помилки, виконаючи такі дії.

1. В Excel виберіть Файл, а потім Параметри.
2. Виберіть Формули, а потім у розділі Перевірка помилок встановіть прапорець Включити перевірку помилок у фоновому режимі режимі перевірки.

Використання формули

За допомогою функції ЗНАЧЕНИЙ можна повертати числове значення тексту.

Вставте стовпець поруч із осередками, що містять текст.

1. Виділіть комірки за допомогою нової формули.
2. Натисніть клавіші CTRL+C. Виділіть перший осередок вихідного стовпця.
3. На вкладці Головна клацніть стрілку під кнопкою Вставити, а потім виберіть Вставити спеціальні >значення або використовуйте клавіші CTRL + SHIFT + V.

Числа, які зберігаються у вигляді тексту, можуть призвести до непередбачених результатів, таких як нерозрахована формула, що відображається замість результату.

Використання формули

За допомогою функції ЗНАЧЕНИЙ можна повертати числове значення тексту.

Вставка нового стовпця

Натисніть і перетягніть вниз, щоб заповнити формулу в інші клітинки.Тепер ви можете використовувати новий стовпець або скопіювати і вставити ці нові значення в початковий стовпець.

Для цього виконайте наведені нижче дії.

1. Виділіть комірки за допомогою нової формули.
2. Натисніть клавіші CTRL+ C. Виділіть перший осередок вихідного стовпця.
3. На вкладці Головна клацніть стрілку під кнопкою Вставити та виберіть Вставити спеціальні >значення.
   або використовуйте клавіші CTRL+ SHIFT + V.

Форматування чисел у вигляді тексту

Якщо ви хочете, щоб у Excel числа певних типів сприймалися як текст, використовуйте замість числового текстового формату. Наприклад, якщо ви використовуєте номери кредитних карток або інші коди, що містять 16 цифр або більше, потрібно використовувати текстовий формат. Це пов'язано з тим, що Excel має не більше 15 цифр точності і округляє всі числа, що йдуть за 15 цифрою, до нуля, що, ймовірно, не те, що ви хочете зробити.

Якщо число має текстовий формат, це легко визначити, оскільки число буде вирівняне в комірці ліворуч, а не праворуч.

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

Порада: Можна також виділити порожні комірки, відформатувати їх як текст, а потім ввести цифри. Такі цифри матимуть текстовий формат.

Примітка: Якщо параметр Текст не відображається, використовуйте смугу прокручування, щоб прокрутити список до кінця.

• Щоб використовувати десяткові знаки у числах, які зберігаються як текст, можливо, доведеться вводити ці числа з десятковими роздільниками.
• При введенні числа, що починається з нуля (наприклад, код продукту), цей нуль за замовчуванням видаляється.Якщо потрібно зберегти нуль, можна створити числовий формат, який не дозволить додатку Excel видаляти початкові нулі в числах. Наприклад, при введенні десятизначного коду продукту Excel за замовчуванням змінює число 0784367998 на 784367998. У цьому випадку можна створити числовий формат з кодом 0000000000, щоб у Excel відображалися всі десять знаків коду продукту, включаючи початковий нуль. Щоб отримати додаткові відомості про цю проблему, див. Створення та видалення числових форматів користувача та Збереження початкових нулів у числових кодах.
• Іноді числа можуть бути відформатовані та збережені в осередках як текст, що згодом може призвести до проблем під час обчислень або безладу при сортуванні. Це іноді трапляється при імпорті чи копіюванні чисел із бази даних або іншого джерела даних. У такій ситуації потрібне зворотне перетворення чисел, збережених у вигляді тексту, у числовий формат. Щоб отримати додаткові відомості, див. Перетворення чисел з текстового формату на цифровий.
• Ви також можете використовувати функцію ТЕКСТ для перетворення числа в текст із певним числовим форматом. Приклади використання цього методу див. у статті Збереження початкових нулів у числових кодах. Відомості про використання функції TEXT див. у розділі Функція TEXT.

Функція тексту

За допомогою функції Текст можна змінити уявлення числа, застосувавши до нього форматування з кодами форматів. Це корисно в ситуації, коли потрібно відобразити числа в легкочитаному вигляді або поєднати їх з текстом або символами.

Примітка: Функція TEXT перетворює числа на текст, що може утруднити посилання в наступних обчисленнях.Краще зберегти вихідне значення в одному осередку, а потім використовувати функцію TEXT в іншому осередку. Потім, якщо потрібно створити інші формули, завжди посилайтеся на вихідне значення, а не результат функції ТЕКСТ.

Текст(значення; формат)

Аргументи функції Текст описані нижче.

Числове значення, яке потрібно перетворити на текст.

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

Загальні відомості

Найпростіша функція ТЕКСТ означає таке:

• =ТЕКСТ(значення, яке потрібно відформатувати; "код формату, який потрібно застосувати")

Нижче наведено популярні приклади, які можна скопіювати прямо в Excel, щоб поекспериментувати самостійно. Зверніть увагу: коди форматів поміщені в лапки.

= ТЕКСТ (1234,567;"# ##0,00 ₽")

Грошовий формат із роздільником груп розрядів та двома розрядами дробової частини, наприклад: 1 234,57 ₽. Зверніть увагу: Excel заокруглює значення до двох розрядів дробової частини.

=ТЕКСТ(СЬОГОДНІ();"ДД.ММ.РР")

Сьогоднішня дата у форматі ДД/ММ/РР, наприклад: 14.03.12

=ТЕКСТ(СЬОГОДНІ();"ДДДД")

Сьогоднішній день тижня, наприклад: понеділок

=ТЕКСТ(ТДАТА();"ЧЧ:ММ")

Поточний час, наприклад: 13:29

Процентний формат, наприклад: 28,5%

Дробний формат, наприклад: 4 1/3

=СЖПРОБІЛИ(ТЕКСТ(0,34;"# ?/?"))

Детальний формат, наприклад: 1/3 Зверніть увагу: функція СЖПРОБЕЛЫ використовується для видалення початкового пробілу перед дробовою частиною.

= ТЕКСТ (12200000;"0,00E+00")

Експонентне подання, наприклад: 1,22E+07

Додатковий формат (номер телефону), наприклад: (123) 456-7898

= ТЕКСТ (1234;"0000000")

Додавання нулів на початку, наприклад: 0001234

=ТЕКСТ(123456;"##0° 00' 00''")

Формат користувача (широта або довгота), наприклад: 12° 34' 56''

Примітка: Функцію ТЕКСТ можна використовуватиме зміни форматування, але це єдиний спосіб. Ви можете змінити формат без формули, натиснувши клавіші CTRL+1 (або

+1 на комп'ютері Mac), а потім виберіть потрібний формат у діалоговому вікні Формат осередків > число .

Скачування зразків

Пропонуємо завантажити книгу, в якій містяться всі приклади застосування функції тексту з цієї статті та кілька інших. Ви можете скористатися ними або створити власні коди форматів для функції тексту.

Інші доступні коди форматів

За допомогою діалогового вікна Формат осередків можна знайти інші доступні коди форматування:

Натисніть клавіші CTRL+1 (

Коди форматів за категоріями

Нижче наведено деякі приклади того, як можна застосувати різні числові формати до значень за допомогою діалогового вікна Формат осередків , а потім за допомогою параметра Custom скопіювати ці коди форматування у функцію TEXT .

Чому Excel видаляє нулі на початку?

Excel сприймає послідовність цифр, введену в комірку як число, а не як цифровий код, наприклад артикул або номер SKU. Щоб зберегти нулі на початку послідовностей цифр, перед вставкою або введенням значень застосуйте до відповідного діапазону комірок текстовий формат. Виділіть стовпець або діапазон, до якого потрібно помістити значення, натисніть клавіші CTRL+1, щоб відкрити діалогове вікно Формат осередків, та виберіть на вкладці Число пункт Текстовий. Тепер програма Excel не видалятиме нулі на початку.

Якщо ви вже ввели дані та Excel видалив початкові нулі, ви можете знову додати їх за допомогою функції Текст. Створіть посилання на верхню комірку зі значеннями та використовуйте формат =ТЕКСТ(значення; "00000"), де число нулів є необхідною кількістю символів. Потім скопіюйте функцію та застосуйте її до решти діапазону.

Якщо з якоїсь причини потрібно перетворити текстові значення назад на числа, можна помножити їх на 1 (наприклад: =D4*1) або скористатися подвійним унарним оператором (--), наприклад: =--D4.

В Excel групи розрядів розділяються пробілом, якщо код формату містить пробіл, оточений знаками номера (#) або нулями. Наприклад, якщо використовується код формату "# ###", число 12200000 відображається як 12200000.

Пробіл після заповнювача цифри визначає розподіл числа на 1000. Наприклад, якщо використовується код формату "# ###,0 ", Число 12200000 відображається в Excel як 12 200,0.

• Роздільник груп розрядів залежить від регіональних параметрів. Для Росії це пробіл, але в інших країнах і регіонах може використовуватися кома або точка.
• Розділювач груп розрядів можна застосовувати у числових, грошових та фінансових форматах.

Нижче наведено приклади стандартних числових (тільки з роздільником груп розрядів та десятковими знаками), грошових та фінансових форматів. У грошовому форматі можна додати потрібне позначення грошової одиниці, і значення вирівняні по ньому. У фінансовому форматі символ рубля розташовується в комірці праворуч від значення (якщо вибрати позначення долара США, ці символи будуть вирівняні по лівому краю осередків, а значення по правому). Зверніть увагу на різницю між кодами грошових та фінансових форматів: у фінансових форматах для відокремлення символу грошової одиниці від значення використовується зірочка (*).

Щоб отримати код формату для певної грошової одиниці, спочатку натисніть клавіші CTRL+1 (на комп'ютері Mac -

+1) і виберіть потрібний формат, а потім у списку, що розкривається Позначення виберіть символ.

Після цього у розділі Числові формати ліворуч виберіть пункт (Всі формати) та скопіюйте код формату разом із позначенням грошової одиниці.

Примітка: Функція TEXT не підтримує форматування за допомогою кольору. Якщо скопіювати у діалоговому вікні "Формат комірок" код формату, у якому використовується колір, наприклад "# ##0,00 ₽;[Червоний]# ##0,00 ₽", то функція ТЕКСТ сприйме його, але колір не відображатиметься.

Спосіб відображення дат можна змінювати, використовуючи поєднання символів "Д" (для дня), "М" (для місяця) та "Г" (для року).

У функції ТЕКСТ коди форматів використовуються без урахування регістру, тому допустимі символи "М" та "м", "Д" та "д", "Г" та "г".

Мигда радить.

Якщо ви надаєте спільний доступ до файлів та звітів Excel користувачам з різних країн, швидше за все, потрібно, щоб вони були різними мовами. Мінда Трісі (Mynda Treacy), Excel MVP, пропонує чудове вирішення цього завдання у своїй статті Відображення дат Excel різними мовами (англійською). У ній є приклад книги, який ви можете завантажити.

Спосіб відображення часу можна змінити за допомогою поєднань символів "Ч" (для годинника), "М" (для хвилин) і "С" (для секунд). Також можна використовувати символи "AM/PM" для відображення часу у 12-годинному форматі.

Якщо не вказувати символи "AM/PM", час відображатиметься у 24-годинному форматі.

У функції ТЕКСТ коди форматів використовуються без урахування регістру, тому допустимі символи "Ч" та "ч", "М" і "м", "С" та "с", "AM/PM" та "am/pm".

Для відображення десяткових значень можна використовувати відсоткові формати.

Десяткові числа можна відображати у вигляді дробів, використовуючи коди форматів "?/?".

Експоненційне уявлення - це спосіб відображення значення у вигляді десяткового числа від 1 до 10, помноженого на 10 певною мірою. Цей формат часто використовується для короткого відображення великих чисел.

В Excel доступні чотири додаткові формати:

• "Поштовий індекс" ("00000");
• "Індекс + 4" ("00000-0000");
• "Номер телефону" ("[
• "Табельний номер" ("000-00-0000").

Додаткові формати залежать від регіональних параметрів. Якщо додаткові формати недоступні для вашого регіону або не підходять для ваших потреб, ви можете створити власний формат, вибравши у діалоговому вікні Формат осередків пункт (Всі формати).

Типовий сценарій

Функція Текст рідко використовується сама по собі, а частіше застосовується у поєднанні з чимось ще. Припустимо, що ви бажаєте об'єднати текст і числове значення, наприклад, щоб отримати рядок "Звіт надрукований 14.03.12" або "Тижневий дохід: 66 348,72 ₽". Такі рядки можна ввести вручну, але суть у тому, що Excel може це зробити за вас. На жаль, при об'єднанні тексту та форматованих чисел, наприклад дат, значень часу, грошових сум тощо. п., Excel прибирає форматування, оскільки невідомо, як потрібно їх відобразити. Тут знадобиться функція Текст, адже з її допомогою можна примусово відформатувати числа, поставивши потрібний код формату, наприклад "ДД.ММ.РРРР" для дат.

У наведеному нижче прикладі показано, що відбувається, якщо спробувати об'єднати текст і число, не застосовуючи функцію Текст. Ми використовуємо амперсанд (&) для зчеплення текстового рядка, пробілу (" ") та значення: =A2&" "&B2.

Ви бачите, що значення дати, взяте з комірки B2, не відформатовано.У цьому прикладі показано, як застосувати потрібне форматування з допомогою функції ТЕКСТ.

Ось оновлена ​​формула:

Запитання та відповіді

На жаль, ви не можете зробити це за допомогою функції TEXT; необхідно використовувати код Visual Basic для програм (VBA). Наступне посилання містить метод: Як перетворити числове значення на слова англійською мовою в Excel.

Так, ви можете використовувати функції ПРОПІСН, РЯДКОВИЙ і ПРОПНАЧ. Наприклад, формула =ПРОПИСН("привіт") повертає результат "ПРИВІТ".

Чи можна за допомогою функції ТЕКСТ додати новий рядок (розрив рядка) у комірці, як при натисканні клавіш ALT+ВВЕДЕННЯ?

Так, але для цього потрібно виконати кілька дій. Спочатку виберіть осередок або осередки, в яких це станеться, і натисніть клавіші CTRL+1, щоб відкрити діалогове вікно Формат > осередків, а потім елемент управління Вирівнювання > текст > перевірка параметр Обтікати текстом. Після цього додайте до функції Текст код ASCII СИМВОЛ(10) там, де потрібний розрив рядка. Вам може знадобитися налаштувати ширину стовпця, щоб досягти потрібного вирівнювання.

У цьому прикладі використано формулу ="Сьогодні: "&СИМВОЛ(10)&ТЕКСТ(СЬОГОДНІ();"ДД.ММ.РР").

Це називається науковою нотацією, і Excel автоматично перетворює числа, що перевищують 12 цифр, якщо осередки форматуються як загальні, та 15 цифр, якщо осередки відформатовані як число. Якщо вам потрібно ввести довгі числові рядки, але не потрібно їх перетворювати, відформатуйте осередки, про які йдеться, як Текст , перш ніж вводити або вставляти значення Excel.

Мигда радить.

Якщо ви надаєте спільний доступ до файлів та звітів Excel користувачам з різних країн, швидше за все, потрібно, щоб вони були різними мовами. Мінда Трісі (Mynda Treacy), Excel MVP, пропонує чудове вирішення цього завдання у своїй статті Відображення дат Excel різними мовами (англійською). У ній є приклад книги, який ви можете завантажити.