Как преобразовать текст в таблицу в Excel
Обновлено: 01.07.2024
Часто можно найти числа, хранящиеся в виде текста в Excel. Это приводит к неправильным вычислениям при использовании этих ячеек в функциях Excel, таких как СУММ и СРЗНАЧ (поскольку эти функции игнорируют ячейки, содержащие текстовые значения). В таких случаях необходимо преобразовать ячейки, содержащие числа в виде текста, обратно в числа.
Теперь, прежде чем мы двинемся дальше, давайте сначала рассмотрим несколько причин, по которым вы можете получить книгу, в которой числа хранятся в виде текста.
- Использование ‘ (апострофа) перед числом.
- Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
- Получение чисел в результате формулы (например, LEFT, RIGHT или MID)
- Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результатом будет число в текстовом формате.
Теперь давайте посмотрим, как действовать в таких случаях.
Преобразование текста в числа в Excel
В этом руководстве вы узнаете, как преобразовать текст в числа в Excel.
Метод, который вам нужно использовать, зависит от того, как число было преобразовано в текст. Вот те, которые рассматриваются в этом руководстве.
- Используя параметр «Преобразовать в число».
- Измените формат с Текст на Общий/Число.
- Использование специальной вставки.
- Использование текста в столбцах.
- Использование комбинации функций VALUE, TRIM и CLEAN.
Преобразование текста в числа с помощью параметра «Преобразовать в число»
Когда к числу добавляется апостроф, формат числа изменяется на текстовый. В таких случаях вы заметите зеленый треугольник в верхней левой части ячейки.
В этом случае вы можете легко преобразовать числа в текст, выполнив следующие действия:
Это мгновенно преобразует все числа, сохраненные в виде текста, обратно в числа. Вы заметите, что после преобразования числа выравниваются по правому краю (в то время как при сохранении в виде текста они выравнивались по левому краю).
Преобразование текста в числа путем изменения формата ячейки
Когда числа отформатированы как текст, вы можете легко преобразовать их обратно в числа, изменив формат ячеек.
Вот шаги:
- Выделите все ячейки, которые вы хотите преобразовать из текста в числа.
- На главную –> Номер. В раскрывающемся списке "Числовой формат" выберите "Общий".
Это мгновенно изменит формат выбранных ячеек на Общий, а числа будут выровнены по правому краю. При желании вы можете выбрать любой из других форматов (например, числовой, валютный, бухгалтерский), что также приведет к тому, что значения в ячейках будут рассматриваться как числа.
Преобразование текста в числа с помощью специального параметра вставки
Чтобы преобразовать текст в числа с помощью параметра "Специальная вставка":
Преобразование текста в числа с использованием текста в столбец
Этот метод подходит в случаях, когда у вас есть данные в одном столбце.
Вот шаги:
Хотя результирующие ячейки по-прежнему могут быть в текстовом формате, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.
Преобразование текста в числа с помощью функции ЗНАЧ
Вы можете использовать комбинацию функций VALUE, TRIM и CLEAN для преобразования текста в числа.
- Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число. удаляет все начальные и конечные пробелы.
- Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут появиться, если вы импортируете данные или загружаете их из базы данных.
Предположим, вы хотите преобразовать ячейку A1 из текста в числа, вот формула:
Если вы хотите применить это и к другим ячейкам, вы можете скопировать и использовать формулу.
Вам также могут понравиться следующие учебные пособия по Excel:
БЕСПЛАТНАЯ КНИГА EXCEL
Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу
7 мыслей о «Преобразование текста в числа в Excel — пошаговое руководство»
Я вижу, что большинство советов очень полезны
Спасибо за это. На самом деле это не сработало, но натолкнуло меня на мысль умножить ячейки на 1, перетащить и использовать вместо них те, которые сработали
У меня есть числа из финансовой базы данных, отформатированные с суффиксом для базисных пунктов, например, 14bp . По умолчанию они считываются в Excel как текст.Я не нашел способа преобразовать их в числа, за исключением очень трудоемких методов: использовать текст в столбцах, чтобы избавиться от bp (что я могу сделать при начальном анализе, когда у меня еще есть пробелы, чтобы я мог использовать фиксированную ширину синтаксический анализ, но если я попытаюсь сделать это после первого синтаксического анализа, то Excel удалит начальные пробелы, и мне придется использовать синтаксический анализ с разделителями и указать, чтобы он рассматривал «b» как разделитель.) В противном случае я ограничиваюсь использованием левого (thenumber, len(thenumber)-2), который затем может потребоваться снова преобразовать в текст.
Есть ли у вас более быстрый способ сделать это? Я подумал, возможно, если бы я сделал bp настраиваемым числовым форматом, Excel распознал бы 14bp как число при чтении, но это не работает.
спасибо. теперь я могу суммировать числа, меняя их формат с текстового на числовой
В этом посте будут показаны все способы преобразования текста в числа в Microsoft Excel.
Проблема, которая довольно часто возникает в Excel, связана с числами, которые были введены или отформатированы как текстовые значения.
Это может вызвать много головной боли при попытке устранить неполадки, почему формула, такая как СУММ, не дает правильного ответа.
Несмотря на то, что данные выглядят как число, на самом деле это может быть текстовое значение, которое будет игнорироваться при любых числовых вычислениях, таких как сумма.
В этом посте я покажу вам, как вы можете идентифицировать такие числа, которые хранятся в виде текстовых значений, и 7 способов, которые вы можете использовать для преобразования текста в правильное числовое значение.
Как идентифицировать числа, введенные как текст
Как узнать, были ли ваши числовые данные введены в виде текста?
Есть довольно много простых способов сказать это!
Выровнено по левому краю
Первый способ определить, есть ли у вас текстовые значения вместо числовых, — это способ выравнивания данных в ячейке.
По умолчанию текст выравнивается по левому краю, а числа выравниваются по правому краю.
Если не было изменено форматирование выравнивания по умолчанию, это будет простой способ определить числа, которые были отформатированы как текст.
Проверка ошибок зеленого треугольника
Если вы не понимаете, что вводите числа как текстовые значения, это может привести к серьезным ошибкам. Вот почему он помечается встроенным средством проверки ошибок Excel.
Все ячейки, помеченные как ошибочные, будут отмечены маленьким зеленым треугольником слева от ячейки, визуально указывающим на ошибку.
< бр />
При выборе такой ячейки появится небольшой значок предупреждения.
< бр />
Когда вы нажмете на это, вы увидите, что это за ошибка. В этом случае вы можете увидеть ошибку «Число, сохраненное как текст».
Вы можете полностью отключить эту проверку ошибок или указать, какие ошибки следует помечать.
Перейдите на вкладку "Файл" и выберите "Параметры" внизу. Откроется меню параметров Excel.
В разделе "Формула" меню "Параметры Excel" выберите "Проверка ошибок".
Снимите флажок Включить фоновую проверку ошибок, чтобы отключить эту функцию. Здесь вы также можете настроить цвет, используемый для обозначения ошибок.
Применен формат текста
Одним из способов ввода чисел в виде текста является форматирование ячейки как текста. Это приводит к тому, что значения вводятся как текстовые значения независимо от того, являются ли они числовыми.
Существует быстрый способ определить, применялось ли к ячейке форматирование текста.
Выберите ячейку и перейдите на вкладку "Главная". Вы сможете увидеть, является ли текст выбранным форматированием, в раскрывающемся меню в разделе «Число» на ленте.
Перед апострофом
Еще один способ ввода чисел в виде текста — использование предшествующего апострофа.
Когда вы вводите апостроф ' в качестве первого символа в ячейке, все после него будет рассматриваться Excel как текстовое значение.
Это означает, что если вы видите передний апостроф в строке формул, вы знаете, что данные являются текстовым значением.
Использовать функцию ISTEXT
В Excel есть функция, которую можно использовать для проверки того, является ли значение текстовым или нет.
- ввод – это значение, ячейка или диапазон, которые вы хотите проверить, являются ли они текстовыми.
Функция ISTEXT вернет значение TRUE, если значение является текстом, и FALSE в противном случае.
Здесь вы можете увидеть, как функция ISTEXT используется для определения того, содержат ли ячейки текст. Функция возвращает TRUE, когда находит текстовое значение.
- ввод – это значение, ячейка или диапазон, который вы хотите проверить, является ли он числом.
Аналогично вы можете проверить, является ли значение числом, используя функцию ISNUMBER. Он принимает один ввод и возвращает TRUE, если значение является числом, и FALSE в противном случае.
Здесь те же данные проверяются с помощью функции ISNUMBER. Функция возвращает FALSE, если число не найдено.
В строке состояния отображается только счетчик
< бр />
Еще один способ быстро проверить диапазон ячеек, чтобы убедиться, что они все текстовые, — использовать строку состояния.
Когда вы выберете диапазон числовых значений, Excel покажет вам некоторые основные статистические данные, такие как сумма, минимум, максимум и среднее значение в строке состояния.
Если все они представляют собой числа, введенные в виде текстовых значений, то эта базовая статистика не будет рассчитываться. Вместо этого вы увидите только количество ячеек в строке состояния.
Примечание. Если хотя бы одна из ячеек является числовым значением, вы увидите полный набор статистики в строке состояния.
Преобразование текста в число с проверкой ошибок
Вы уже видели, что можно использовать проверку ошибок для обнаружения чисел, введенных в виде текста.
Проверка ошибок также позволит вам преобразовать эти текстовые значения в правильные числа.
< бр />
Нажмите значок "Ошибка" и выберите "Преобразовать в число" из вариантов. Затем Excel преобразует каждую ячейку в выбранном диапазоне в число.
Примечание. Этот параметр может работать очень медленно, если нужно преобразовать большое количество ячеек.
Преобразование текста в число с помощью текста в столбец
Обычно текст в столбцы используется для разделения данных на несколько столбцов. Но с помощью этого трюка вы можете использовать его для преобразования текстового формата в общий формат.
Текст в столбец позволит вам выбрать символ-разделитель для разделения данных. Если вы отмените выбор этого параметра и не укажете какой-либо разделитель, ваши данные не будут разделены.
Затем вы сможете выбрать, как форматировать результаты, и именно здесь вы можете выбрать общий формат для вывода.
Выполните следующие действия, чтобы использовать функцию преобразования текста в столбцы для преобразования текста в числа.
Это преобразует ваши текстовые значения в числовые значения.
Команда Text to Column обычно используется для разделения данных, разделенных запятыми или другими разделителями, на несколько столбцов.
Любые данные с разделителями будут текстовыми данными, поэтому, когда Excel разбивает данные на несколько столбцов, он также преобразует числовые данные в числа для дополнительного удобства.
Это верно и в том случае, если делить нечего!
Поэтому мастер преобразования текста в столбцы можно использовать для преобразования числовых текстовых значений в числа.
Преобразование текста в число с умножением на 1
Даже если значения хранятся в виде текста, с ними можно выполнять определенные числовые вычисления, и результаты этих вычислений также будут числовыми значениями.
Вы можете использовать это, чтобы преобразовать текст в числа, умножив их на 1. Поскольку вы умножаете на 1, это не изменит числа, но преобразует их.
< /p>
Вы можете использовать приведенную выше формулу в соседней ячейке, а затем скопировать и вставить ее, чтобы преобразовать весь столбец.
Если вы хотите удалить формулу позже, вы можете скопировать и вставить их как значения.
Преобразование текста в число с помощью специального умножения вставки
Умножение на 1 — отличный прием для преобразования текста в значения, но, возможно, вы не захотите использовать для этого формулу.
Хорошая новость заключается в том, что есть простой способ умножить текст на 1 без использования формулы.
Это означает, что вы можете преобразовать свой текст на месте, и вам не нужно копировать и вставлять формулы в качестве значений на дополнительном шаге.
Вы можете использовать Специальную вставку для преобразования текстовых значений. Выполните следующие действия, чтобы умножить на 1 с помощью специальной вставки.
- Введите 1 в любую ячейку на листе. 1 даже не обязательно должно быть числовым значением и может быть текстовым значением.
- Скопируйте ячейку, содержащую 1, с помощью Ctrl + C на клавиатуре или щелкните правой кнопкой мыши и выберите "Копировать".
- Выберите ячейки с текстовыми значениями для преобразования.
- Нажмите Ctrl + Alt + V на клавиатуре или щелкните правой кнопкой мыши и выберите "Специальная вставка". Откроется меню «Специальная вставка».
- Выберите «Значения» в параметрах «Вставить» и выберите «Умножение» в параметрах «Операция».
- Нажмите кнопку ОК.
- текст — это текстовое значение, которое необходимо преобразовать в числовое значение.
Это умножит все значения на 1, что было значением в скопированной ячейке. В результате текст преобразуется в значения, но поскольку вы умножаете на 1, числа не изменятся.
Преобразование текста в число с помощью функции VALUE
На самом деле существует специальная функция, которую вы можете использовать для преобразования текста в числовые значения.
Функция ЗНАЧ принимает текстовое значение и возвращает текстовое значение в виде числа.
< /p>
Вы можете использовать приведенную выше формулу для преобразования текста в ячейке B3 в число, а затем скопировать и вставить формулу для преобразования всего столбца.
Преобразование текста в число с помощью Power Query
Power Query — прекрасный инструмент для преобразования данных любого типа. Его, безусловно, можно использовать и для преобразования текста в числа.
Power Query строго типизирован. Это означает, что вычисления с несовместимыми типами данных приведут к ошибке. Таким образом, вы не сможете умножить текстовые значения на 1, чтобы преобразовать их в числа.
Но в Power Query есть простой способ преобразования типов данных, включая текст, в числа.
< бр />
Нажмите значок типа данных слева от заголовка столбца для значений, которые вы хотите преобразовать.
Каждый столбец в редакторе Power Query имеет значок типа данных, который отображает текущий тип данных столбца. Если столбцу не назначен тип данных, показанный значок будет ABC123.
В Power Query доступно несколько типов числовых данных, и какой из них вы выберете, зависит от требуемого уровня десятичной точности.
В этом случае вы можете выбрать в меню пункт "Целое число".
< бр />
Обратили внимание, что значения в столбце теперь выровнены по правому краю, а значок типа данных изменился? Это визуальные индикаторы того, что тип данных теперь представляет собой целые числа, а не текст.
Вы увидите, что к запросу был применен новый шаг Changed Type, и он будет использовать приведенную выше формулу кода M.
Затем вы можете нажать кнопку "Закрыть и загрузить" на вкладке "Главная", чтобы сохранить результаты и загрузить данные в Excel.
Возможно, вам даже не потребуется применять этот шаг преобразования типа данных к вашему запросу. Когда вы впервые импортируете данные в Power Query, он обычно угадывает тип данных и применяет преобразование за вас!
Преобразование текста в число с помощью Power Pivot
Если вы хотите проанализировать или обобщить данные после их преобразования из текста в числа, возможно, вы захотите выполнить преобразование в Power Pivot.
Power Pivot – это надстройка, позволяющая эффективно анализировать миллионы строк в модели данных с помощью сводных таблиц.
С помощью Power Pivot вы можете создавать вычисления на уровне строк, используя язык формул DAX, чтобы создавать новые столбцы в своих наборах данных. Затем вы сможете получить доступ к этим новым столбцам в сводных таблицах, подключенных к модели данных.
В этом случае формула DAX, которую вы можете использовать для преобразования текста в числа, точно такая же, как формула в сетке.
Выполните следующие действия в надстройке Power Pivot, чтобы создать новый вычисляемый столбец, преобразующий текст в числа.
< бр />
Выберите любую ячейку под заголовком «Добавить столбец».
Вставьте приведенную выше формулу. TextNumbers — это имя таблицы данных в Power Pivot, а Text — это имя столбца, который вы хотите преобразовать.
Дважды щелкните заголовок столбца, чтобы изменить название.
Нажмите значок "Сохранить" и закройте надстройку Power Pivot.
Теперь вы можете создать сводную таблицу из модели данных, и этот новый столбец будет доступен для использования внутри сводной таблицы, как и любое другое поле.
Вычисляемый столбец будет доступен в списке полей сводной таблицы, и вы сможете использовать его как любое другое числовое поле.
Выводы
Существует множество причин, по которым данные могут содержать числа, отформатированные как текст.
Если вы хотите свободно использовать эти значения в своих расчетах, вам нужно будет преобразовать их в числа.
К счастью, существует множество простых способов преобразования текста в числа, таких как проверка ошибок, специальная вставка, базовое умножение и функция ЗНАЧЕНИЕ. Это все простые способы преобразования текста внутри сетки.
Если вы импортируете данные с помощью Power Query или Power Pivot, вы можете выполнить преобразование в каждом из этих инструментов. У обоих есть методы для преобразования текста в числа.
Используете ли вы какой-либо из этих методов для преобразования текстовых значений в числа? Знаете ли вы какие-нибудь другие интересные способы выполнения этого действия? Дайте мне знать в комментариях ниже!
Об авторе
Джон МакДугалл
Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.
Как преобразовать таблицу Word или Excel в текст
По моему не очень скромному мнению, объект Table в Excel и Word недостаточно используется большинством пользователей и разработчиков Excel и Word. Например, объект таблицы Excel с формулами вычисляется быстрее и занимает меньше места для хранения, чем сопоставимый обычный диапазон. Как бы ни были полезны таблицы в Excel и Word, иногда вы предпочитаете обычный текст.
В этой статье объясняется, как преобразовать таблицу в обычный текст вручную и с помощью VBA для Excel и Word. Эти инструкции относятся к Office 365/Office 2019 для Windows. Эти инструкции не сильно изменились по сравнению с устаревшими версиями Word и Excel для Windows и Mac OS с 2007 года.
Преобразование таблицы Excel в обычный диапазон
Действия по преобразованию объекта Table в текст практически одинаковы для Word и Excel.
<р>1. Установите курсор в любом месте таблицы. Обычно это делается щелчком по ячейке. <р>2. На вкладке «Макет» выберите «Преобразовать в диапазон» (группа «Инструменты»). <р>3. Выберите Да для подтверждения.Преобразование таблицы Word в текст
Как и в Excel, ячейка таблицы в Word представляет собой пересечение строки и столбца, как показано здесь.
<р>1. Установите курсор в любом месте таблицы. Обычно это делается щелчком по тексту. <р>2. На вкладке «Макет» выберите «Преобразовать в текст» (группа «Данные»). <р>3. Выберите способ разделения текста между ячейками таблицы.3а. Если вы выберете «Вкладки», макет таблицы сохранится с позициями табуляции между ячейками, как показано на рисунке. Этот параметр также работает для сохранения текстового файла с табуляцией в качестве разделителя.
3б. Если вы выберете «Абзацы», текст образует единый список, начиная с левого столбца.
3c. Вариант выбора Запятые аналогичны табуляциям, за исключением того, что вместо табуляции вставляются запятые. Выберите этот параметр, чтобы сохранить текст в виде файла CSV (значения, разделенные запятыми). Выбор «Другое» дает аналогичный результат. Выбранный символ становится разделителем.
Преобразование таблицы Excel в обычный диапазон с помощью VBA
Команда «Запись макроса» не записывает преобразование таблицы в обычный диапазон, поэтому ее необходимо ввести самостоятельно. Самый простой подход — добавить в процедуру один оператор. Оператор использует метод Unlist коллекции ListObjects. Ссылайтесь на таблицу по номеру, чтобы преобразовать первую или единственную таблицу на активном листе:
Ссылайтесь на таблицу по имени, чтобы преобразовать одну таблицу на активном листе с несколькими таблицами. Используйте имя таблицы, указанное на вкладке «Конструктор таблиц» (группа «Свойства»):
Добавление вложенного цикла For-Next преобразует все таблицы в активной книге:
Преобразование таблицы Word в текст с помощью VBA
Код VBA для преобразования объекта таблицы Word аналогичен коду Excel. Сравнительный оператор использует метод ConvertToText коллекции Tables.
Word не дает имена своим таблицам, поэтому вручную назначьте закладку для таблицы, если вам нужно использовать VBA для преобразования определенной таблицы в текст. Чтобы назначить закладку, сначала поместите курсор в ячейку таблицы. На вкладке «Вставка» нажмите кнопку со стрелкой вниз «Ссылки» и выберите «Закладка» (шаг 1 ниже). В поле «Имя закладки» введите уникальное имя без пробелов и нажмите «Добавить» (шаг 2).
Шаг 1. Команда «Закладка»
Шаг 2. Добавьте новую закладку
Вот оператор для преобразования «именованной» таблицы в документе в текст. Tables(1) ссылается на первую таблицу в диапазоне закладки MyTable, а не на первую таблицу в документе.
Код VBA для преобразования всех таблиц в документе Word похож на код для преобразования всех таблиц на листе Excel:
Не забудьте включить параметр "Разделитель", если вы не хотите принять разделитель дефиса (-) по умолчанию:
Вы когда-нибудь пытались преобразовать текстовый файл в файл Excel с разделителями? В этой статье я расскажу о методах решения этой проблемы.
Импорт текстового файла на лист с разделителем или без него
Преобразование текстового файла в лист с разделителем с помощью Open
Чтобы преобразовать текстовый файл в лист, вы можете использовать утилиту Open в Excel.
<р>3. Нажмите «Открыть» и в диалоговом окне «Мастер импорта текста» установите флажок «С разделителями» и нажмите «Далее», чтобы проверить разделитель, который вы используете для разделения текста, и продолжайте нажимать «Далее», чтобы указать формат, который вы используете. Смотрите скриншот:
 |
 | < /tr>
 |
Импорт текстового файла на лист с разделителем или без него
Если вы хотите импортировать текстовый файл в определенный диапазон листа с разделителем или без разделителя, как вам нужно, вы можете попробовать Вставить файл в утилиту курсора Kutools for Excel для решения этой задачи.
После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)
Примечание:
ЕСЛИ вы хотите разделить текст на ячейки с разделителем, выберите тексты и нажмите Kutools > Объединить и разделить > Разделить ячейки. Смотрите скриншот:
Затем отметьте нужные параметры в разделе «Тип» и «Разделить по разделам». Смотрите скриншот:
Нажмите "ОК", чтобы выбрать ячейку для размещения текстов.
Нажмите "ОК" для завершения.
Читайте также: