Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как извлечь первую букву каждого слова из ячейки?

Как извлечь номер или текст из Excel с помощью этой функции

— но если вы используете оба в одной ячейке, вы можете столкнуться с некоторыми трудностями. К счастью, вы можете извлечь числа или текст из ячеек для более эффективной работы с вашими данными. Мы демонстрируем несколько вариантов, в зависимости от формата, в котором находятся ваши данные.

Числа, хранящиеся как текст

Это распространенная ситуация, и, к счастью, ее очень легко исправить. Иногда ячейки, содержащие только числа, неправильно помечаются как текст, что не позволяет Microsoft Excel использовать их в операциях. На изображении ниже видно, что ячейки в столбце A сохранены как текст, о чем свидетельствует зеленая галочка в каждой ячейке, а также поле форматирования, выделенное ниже.

текстовый формат

Преобразовать в число

Чтобы решить эту проблему, просто щелкните в ячейке, нажмите на поле с предупреждением и выберите «Преобразовать в число». Вот и все! Это можно сделать для нескольких ячеек, выбрав их все и щелкнув по полю с предупреждением, но это, вероятно, не так эффективно, как в следующем методе.

преобразовать к числу

Текст в столбцы

Если у вас много ячеек, которые нужно упорядочить, использование этого метода может занять много времени. Чтобы ускорить процесс, можно использовать функцию Microsoft Excel «Текст в столбцах».

функция. Выберите числа, которые вы хотите конвертировать, перейдите на Данные> Текст в столбцы, и используйте мастер, чтобы убедиться, что числа выходят правильно (по большей части, вам просто нужно нажать следующий а также Конец не возиться ни с одной из настроек).

текст в колонки

Мы преобразуем текст в числа, и теперь вы можете начинать. Этот метод работает только в том случае, если у вас один столбец за раз; если у вас много столбцов, вы можете использовать этот метод.

Специальная паста

Чтобы этот метод работал, вам нужно ввести число 1 в ячейку (важно, чтобы оно было в числовом формате). Выберите эту ячейку и скопируйте ее. Теперь выберите все числа, которые вы хотите преобразовать в числовой формат, и нажмите Edit> Paste Special. Выберите «Умножить» в разделе «Операция» и нажмите «ОК».

паста-кратно

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

Извлечение чисел или текста из ячеек смешанного формата

Теперь мы переходим к самой сложной части: извлечению чисел из ячеек, которые содержат несколько форматов ввода. Если у вас есть число и единица измерения (например, «7 лопат», как у нас ниже), вы столкнетесь с этой проблемой. Чтобы решить эту проблему, мы рассмотрим несколько различных способов разделения ячеек на числа и текст, что позволит вам работать с каждой из них в отдельности. Первый метод немного громоздок, но очень хорошо работает с небольшими наборами данных.

смешанный формат-клетка

ЛЕВО ПРАВО ПОИСК

Основной функцией, которую мы будем использовать здесь, является LEFT, которая возвращает самые левые символы в ячейке. Как вы можете видеть в нашем наборе данных выше, у нас есть ячейки с одним, двумя и тремя символами, поэтому нам нужно вернуть первый, второй или третий символ ячеек. Сочетание функции «ВЛЕВО» с функцией «ПОИСК

Мы можем вернуть все, что находится слева от места расположения. Это :

Все элементы будут сдвинуты влево. Использование ручки заливки

Чтобы применить формулу к оставшимся ячейкам, вы получите следующий результат (формулу можно увидеть на панели функций в верхней части изображения):

левая функция номер экстракции

Теперь у нас есть все изолированные числа, чтобы вы могли ими манипулировать. Вы хотите выделить текст? Подобно RIGHT, мы можем использовать RIGHT следующим образом:

Это возвращает x символов справа от ячейки, где x — общая длина ячейки минус количество символов слева от пробела.

правая функция слово экстракция

Теперь вы можете работать с текстом. Хотите совместить их снова? Просто используйте функцию CONCATENATE для всех входных ячеек:

Читайте так же:
Как классифицировать текстовые строки на основе ключевых слов в Excel?

Несомненно, этот метод работает лучше всего, если у вас есть только числа и единицы. Возможно, вам придется изменить формулы, чтобы заставить его работать с другими форматами ячеек. Если у вас есть огромный набор данных, это будет стоить того времени, которое потребуется, чтобы разобраться с формулой!

Текст в столбцы

Функция «Текст в столбцы» полезна для столбцов, содержащих только цифры, но она также может упростить жизнь, если у вас есть ячейки смешанного формата. Выберите столбец, с которым хотите работать, и нажмите Данные> Текст в столбцы. Затем вы можете использовать мастер, чтобы выбрать разделитель (пробел, как правило, лучший) и разделить столбец так, как вы хотите.

текста в колонках смешанного формата

Если у вас есть только одно- и двузначные числа, опция «Фиксированная ширина» также может быть полезна, поскольку она будет разделять только первые два или три символа ячейки (вы можете создать несколько разбиений, если хотите, но я сохраню полное объяснение разбиения по фиксированной ширине для другой статьи).

Если ваш набор данных содержит много столбцов, и вы не хотите использовать текст в столбцах для каждого из них, вы можете легко получить тот же эффект, используя быстрый экспорт и импорт. Сначала экспортируйте вашу электронную таблицу в виде файла значений, разделенных запятыми (CSV). Нажмите Файл> Сохранить как… и сохраните ваш файл в формате CSV.

копи-CSV

Теперь откройте новую электронную таблицу и нажмите Файл> Импорт… Выберите файл CSV и используйте мастер импорта, чтобы разбить данные на два столбца (вы будете использовать те же шаги, что и в мастере «Текст в столбцы»). Выбрав «Пробел» в разделе «Разделители», вы скажете Microsoft Excel разделить данные, где бы они ни находились, чтобы изолировать цифры и текст.

Когда закончите, нажмите «Готово», и вы получите новую электронную таблицу, в которой столбцы разделены на две части. Конечно, если у вас в ячейке более одного пробела, вы получите более двух столбцов, как вы можете видеть здесь:

импортирован-CSV-первенствует

При использовании данного метода нет хорошего решения этой проблемы; вам просто нужно соединить ячейки вместе.

Более сложные ситуации

Приведенные выше стратегии позволят вам извлечь числа или текст из большинства ячеек смешанного формата. Даже если это не так, вы можете использовать некоторые мощные текстовые функции в Microsoft Excel, чтобы получить нужные вам символы. Однако ситуация может быть гораздо сложнее и требовать более сложного решения.

Например, я нашел сообщение на форуме, где кто-то хотел извлечь числа из строки, например, «45t * 65 /», чтобы в итоге он получил «4565». Другой автор дал следующую формулу в качестве одного из способов сделать это:

Честно говоря, я понятия не имею, как это работает. Но, согласно сообщению на форуме, он может извлекать числа из сложной строки цифр и других символов. Дело в том, что при достаточном количестве времени, терпения и усилий вы можете извлечь цифры и текст практически из чего угодно. Вам просто нужно найти нужные ресурсы

Какую стратегию вы используете?

Теперь, когда вы имеете представление о том, как извлекать числа и буквы из ячеек смешанного формата, мы хотим узнать, какие еще стратегии вы использовали для этого. Опубликуйте в комментариях свои сумасшедшие формулы Excel, процессы или что-либо еще, что, по вашему мнению, может быть полезным, чтобы мы все могли учиться на их примере!

«практические работы по « microsoft excel »» выполнила

Основные функции работы с текстом в Microsoft Excel .

Ссылка — объединяет несколько строк текста в одну.

Синтаксис

СЦЕПИТЬ (текст1; текст2;. )

Эта функция объединяет тексты, перечисленные в качестве аргументов функции, в один текст. Аргументами могут быть текстовые строки, числа или ссылки (адреса), которые ссылаются на одну и ту же ячейку. Количество аргументов может достигать 30.

Читайте так же:
Как найти и заменить определенный текст в текстовых полях?

Рассмотрите фрагмент электронной таблицы:

Эта формула дает вам текст «1 сентября» в ячейке B2:

Примечание. Вместо функции COLLECT можно также использовать оператор «&» (без кавычек):

ДЛСТР

Подсчитывает количество символов в строке, т.е. определяет длину строки.

Текстовые аргументы имеют фиксированную длину. Длина строки вычисляется с учетом пробелов.

ДЛСТР («Урок информатики») равняется 16.

ДЛСТР («») равняется 0.

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

P STR (текст; начальное_положение; количество_символов)

Text — текстовая строка, содержащая извлекаемые знаки;

Start_position — позиция в исходном тексте первого символа последовательности символов, извлеченных из текста. Начиная с 1 в исходном тексте, символы нумеруются;

Количество символов указывает, сколько символов следует извлечь из исходного текста.

Функция PSTR возвращает «», если позиция start_position больше длины текста.

В случаях, когда начальная_позиция меньше длины текста, но и начальная_позиция, и количество символов превышают длину текста, функция PSTR возвращает символы до конца текста.

Если начальная позиция меньше 1 или количество символов отрицательно, функция PSTR возвращает значение ошибки # HOLE!

ПСТР("Поток жидкости"; 1; 5) равняется "Поток".

ПСТР("Поток жидкости"; 7; 20) равняется "жидкости".

ПСТР("Байт"; 5; 2) равняется ""(пустой текст).

ЛЕВСИМВ

Дает номер первого (самого левого) символа текстовой строки.

ЛЕВСИМВ (текст; количество_символов)

Текст — текстовая строка, содержащая извлекаемые символы;

Number_characters — указывает, сколько символов должна принимать функция LEFSIMV.

Число_символов должно быть больше или равно 0.

L EVSIMW возвращает весь текст, если число_символов превышает максимальную длину текста.

Если количество символов не указано, оно принимается равным 1.

ЛЕВСИМВ ("Цена товара"; 4) равняется "Цена".

Если ячейка А1 электронной таблицы содержит текст "Швеция", то ЛЕВСИМВ(А1) равняется "Ш".

ПРАВСИМВ

Получает последний символ (самый правый) текстовой строки.

ПРАВСИМВ (текст; количество_символов)

Text — текстовая строка, содержащая извлекаемые знаки;

Число_символов — указывает, сколько символов должна извлечь функция ПРАВИЛА_СИМВОЛОВ.

Число символов должно быть больше или равно нулю.

Функция Число_символов должна быть больше нуля, если количество_символов больше длины текста.

Если количество_символов превышает длину текста, LEVSIMW возвращает весь текст.

Если количество символов опущено, оно принимается равным 1.

RETURNSIMV возвращает полный текст.

Если количество символов опущено, оно считается равным 1.

ПРАВСИМВ ("Продажная цена";4) равняется "Цена".

ПРАВСИМВ ("Ассортиментный номер") равняется "р".

НАЙТИ

Текстовая строка (искомый_текст) находится в другой текстовой строке (просматриваемый_текст) и возвращается в виде целого числа, соответствующего положению начала искомой строки относительно самого левого символа просматриваемого текста.

Определите (искомый_текст; просмотренный_текст; начальная_позиция).

Вы ищете, где встречается этот текст;

Viewable_text — текст, в котором следует искать вхождения искомого текста;

Start_position — позиция символа, с которого должен начинаться поиск.

Первым символом аргумента viewable_text является цифра 1.

При отсутствии аргумента nach_position предполагается, что он равен 1.

Если искомый_текст – это "" (пустая строка), то функция НАЙТИ считает подходящим первый символ в просматриваемой строке (т.е. возвратит значение аргумента нач_позиция или 1.)

В тексте не должно быть символов подстановки (? и *).

НАЙТИ("М; Мама мыла раму ") равняется 1.

НАЙТИ("м; Мама мыла раму ") равняется 3.

НАЙТИ("м; Мама мыла раму "; 5) равняется 6.

Если искомый_текст отсутствует в просматриваемом_тексте, функция ПОИСКПОЗ возвращает значение ошибки #ПРИМЕЧАНИЕ! В зависимости от случая в соответствующей ячейке может быть выведено соответствующее сообщение. Для этого используется функция ЕСЛИ, в которой в качестве условия должна быть использована функция ЕОСН, возвращающая значение True, если результат в данной ячейке имеет значение #ЗНАЧ!

Пусть в ячейке В2 вычисляется положение буквы "д" в тексте, занесенном в ячейку А2.

Введите следующую формулу в ячейку B2, чтобы предотвратить отображение значения #NOTE!

Как разделить текст в excel с помощью формулы

Позвольте мне начать с того, что я служу через этого брокера, он проверен временем! А вот хороший пример заработка, человек зарабатывает через интернет очень много и показывает все на примерах, идите и читайте! Добавьте страницу в закладки. Теперь прочитайте следующее и напишите свое мнение

Читайте так же:
Как использовать окно просмотра для отслеживания формул в Excel?

Ранее мы рассматривали деление текста на колонки на примере деления существительного на составные части.

Для выполнения этой задачи использовался инструмент Excel «Текст по столбцам».

Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]

Это, несомненно, важный и полезный инструмент в Excel, который может упростить многие задачи. Но у этого метода есть небольшой недостаток.

Когда вы постоянно получаете данные в определенном формате и должны постоянно их разделять, это занимает определенное количество времени. Кроме того, если данные поступают к вам повторно, то вам придется повторить все эти действия.

Содержание

  • Как разделить текст Имя по столбцам с помощью формул
  • 1.1 Начните делить первую часть текста — Имя
  • 1.2 Начните делить вторую часть текста — Фамилия
  • 1.3 Начните делить третью часть текста — Фамилия
  • 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы.

Пример 1. Формулы для разделения текста с именами Данные можно разделить с помощью текстовых формул, таких как функции PSTR и Find, которые мы рассмотрели ранее.

В этом случае вам нужно просто вставить данные в определенный столбец, а формулы автоматически разделят текст так, как вам нужно. Давайте начнем рассматривать этот пример. У нас есть столбец со списком имен. Наша задача — отсортировать имена так, чтобы фамилия, имя и отчество находились в отдельных столбцах.

Попробуем описать план действий очень подробно и разобьем решение проблемы на несколько шагов. Сначала мы добавим вспомогательные столбцы для промежуточных расчетов, чтобы сделать все более понятным для вас, и, наконец, объединим все формулы в одну.

Поэтому давайте добавим позицию столбца для первого и второго пробелов. Используя функцию ПОИСК, как уже упоминалось в предыдущей статье, мы находим местоположение первого пространства. Для этого введите формулу в ячейку «H2»

И потяните его вниз. Теперь мы должны найти порядковый номер второго пространства. Мы будем использовать ту же формулу, но она будет немного отличаться. В той же формуле первое пространство будет найдено, но мы должны найти второе пространство.

Это означает, что вам нужно изменить третий аргумент функции ПОИСКПОЗ, а именно начальную позицию, то есть позицию, с которой функция будет искать нужный ей текст.

Мы видим, что второй пробел в любом случае находится после первого, но мы уже нашли позицию первого пробела, поэтому, добавляя 1 к позиции первого пробела, мы указываем функции ПОИСКПОЗ найти пробел, начиная с первой буквы после первого пробела.

Озвучивание функций:

Затем раскройте формулу и получите позиции первого и второго промежутков.

Приступаем к делению первой части текста — Фамилии

Для этого мы воспользуемся функцией PSTR, запомнив ее синтаксис:

=ПСТР(текст- начальная_позиция- число_знаков), где

  • текст — это ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — в нашем случае это 1, то есть начиная с первой буквы;
  • число_знаков — мы видим, что фамилия состоит из всех знаков, начиная с первой буквы и до 1-го пробела. А позиция первого пробела нам уже известна. Это и будет количество знаков минус 1 знак самого пробела.

Формула выглядит следующим образом:

Приступаем к делению второй части текста — Имя

Снова используем функцию =PSTR(текст-начало_позиции-число_знаков), где

  • текст — это тот же текст ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — в нашем случае Имя начинается с первой буква после первого пробела, зная позицию этого пробела получаем H2+1;
  • число_знаков — число знаков, то есть количество букв в имени. Мы видим, что имя у нас находится между двумя пробелами, позиции которых мы знаем. Если из позиции второго пробела отнять позицию первого пробела, то мы получим разницу, которая и будет равна количеству символов в имени, то есть I2-H2
Читайте так же:
Как найти в Excel все возможные суммы двух чисел в списке?

Получим конечную формулу:

Приступаем к делению третьей части текста — Отчество

И снова функция =PSTR(текст-начало_позиции-число_символов), где

  • текст — это тот же текст ФИО, в нашем примере это ячейка A2;
  • начальная_позиция — Отчество у нас находится после 2-го пробелам, значит начальная позиция будет равна позиции второго пробела плюс один знак или I2+1;
  • — в нашем случае после Отчества никаких знаков нет, поэтому мы просто может взять любое число, главное, чтобы оно было больше возможного количества символов в Отчестве, я взял цифру с большим запасом — 50

Видео: Формулы в Эксель

Затем выделите три ячейки и перетащите формулы вниз, чтобы получить желаемый результат. На этом можно закончить, а можно записать вычисления для промежуточного пространственного положения в самих формулах деления текста.

Это очень просто сделать. Мы видим, что расчет первого пробела находится в ячейке H2 — НАЙТИ(» «-A2-1), а расчет второго пробела в ячейке I2 — НАЙТИ(» «-A2-H2+1) .

Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)

Давайте посмотрим на первую формулу выделения Фамилия и увидим, где здесь встречаются H2 или I2, и изменим их на формулы в этих ячейках, аналогично с Фамилиями и Имя

  • Фамилия =ПСТР(A2-1-H2-1) получаем =ПСТР(A2-1-НАЙТИ(» «-A2-1)-1)
  • Имя =ПСТР(A2-H2+1-I2-H2) получаем =ПСТР(A2-НАЙТИ(» «-A2-1)+1; НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)-НАЙТИ(» «-A2-1))
  • Отчество =ПСТР(A2-I2+1-50) получаем =ПСТР(A2-НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)+1-50)

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

Надеемся, этот пример показал вам, насколько полезны текстовые функции Excel для работы с текстом и как они позволяют автоматически разбивать текст, используя формулы для одного типа данных.

Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

Давайте рассмотрим второй пример, который также очень часто встречается на практике. Этот пример похож на предыдущий, но здесь гораздо больше данных для разделения. В этом примере я покажу технику, которая позволит вам решить проблему достаточно быстро и не ошибиться.

Предположим, у нас есть список чисел, перечисленных через запятую, нам нужно разбить текст так, чтобы каждое число находилось в отдельной ячейке (вместо запятой могут быть любые другие символы, включая пробелы). Другими словами, нам нужно разбить текст на слова.

Как разделить текст в excel с помощью формулы

Вручную (без формул) мы уже обсуждали, как легко решить эту проблему с помощью инструмента «Текст по столбцам». Однако в нашем случае нужны формулы. Во-первых, нам нужно найти общее деление, на которое мы будем делить текст.

В нашем случае это запятая, но, например, в первой задаче мы отделяли имя, а разделителем был пробел. Наш второй пример более универсален (больше подходит для больших объемов данных). Так, например, мы могли бы удобно разбить не только имена на отдельные ячейки, но и все предложение: каждое слово в отдельной ячейке.

Этот вопрос действительно возникал в комментариях, поэтому мы решили написать об этом статью. Для удобства мы укажем этот разделитель в соседнем столбце, поэтому его не нужно будет писать в формуле, а просто сослаться на ячейку. Кроме того, просто изменив разделитель между ячейками, мы можем использовать файл и для решения других задач.

Теперь основная суть приема.

Шаг 1. Используя функцию ПОИСКПОЗ во вспомогательном столбце, найдите первый разделитель. Поскольку мы уже рассматривали эту функцию ранее, я не буду описывать ее подробно. Запишите формулу в D1 и перетащите ее вниз на все строки

Читайте так же:
Как изменить отрицательное число на ноль в Excel?

Иными словами, мы ищем запятую, начиная с позиции 1 в тексте

Шаг 2. Чтобы найти вторую цифру, записываем формулу в ячейку E1 (в нашем случае через запятую). Используется аналогичная формула с небольшими изменениями.

  1. Сначала зафиксируем столбец искомого значения и текст, чтобы при растягивании формулы вправо ссылки на ячейки не перемещались. Для этого введите знак доллара перед столбцами B и A — либо вручную, либо выделив A1 и B1, нажмите F4 три раза, после чего ссылки станут абсолютными, а не относительными.
  2. Second: Третий аргумент — начало позиции будет вычисляться как позиция предыдущего разделителя (найденная выше) плюс 1, т.е. D1+1, поскольку мы знаем, что второй разделитель находится точно после первого и нам не нужно это учитывать.

Давайте напишем формулу и загрузим ее.

3. Найдите позиции остальных разделителей. Для этого нужно продлить формулу для нахождения второго разделителя (шаг 2) вправо на столько ячеек, сколько может быть отдельных делений значений с небольшим отступом. Мы получим все позиции разделителей. Ошибка #Sign указывает на то, что значения закончились и формула не может найти больше разделителей. Мы получим следующее

Шаг 4: Выделите первое число из текста, используя функцию PSTR.

Наша начальная позиция равна 1, мы вычисляем количество символов как позицию первого разделителя минус 1: D1-1 мы растягиваем формулу вниз

Как разделить текст в excel с помощью формулы

Шаг 5: Найдите второе слово таким же образом с помощью функции PSTR в ячейке P1.

Мы начинаем вычислять это второе число после первой запятой. Позицию первой запятой мы имеем в ячейке D1, прибавляем единицу и получаем начальную позицию нашего второго числа.

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

Шаг 6: Перетащите формулу из шага 5 вправо и вниз, чтобы текст оказался в разных ячейках.

Как разделить текст в excel с помощью формулы

Шаг 7. По сути, наша проблема уже решена, но для наглядности запишем формулу перехвата ошибки в той же ячейке P1, заменив ее пустым значением.

Вы также можете сгруппировать и уменьшить вспомогательные колонны, чтобы они не мешали. Окончательное решение проблемы выглядит следующим образом

Как разделить текст в excel с помощью формулы

Примечание: Мы сделали первую позицию разделителя и первое деление слова иначе, чем остальные, поэтому мы смогли распространить формулу только на вторые значения.

При написании задачи я заметил, что ее можно упростить. Для этого в столбце C нужно было записать 0 значение первого разделителя. После этого найдите значение первого разделителя

И первый текст в

Затем вы можете применить формулу ко всем остальным значениям. Вот пример такого варианта. Шаблоны могут быть созданы с помощью файла.

Вы вставляете данные в столбец «A», указываете разделитель в столбце «B», растягиваете формулы на нужное количество ячеек и получаете результат.

Предупреждение. Из-за отсутствия разделителя в конце текста количество символов от последнего разделителя до конца строки не подсчитывается, поэтому последний разделенный текст не включается.

Проблему можно решить либо добавлением вспомогательной колонки рядом с исходным текстом, где для выравнивания этого текста можно использовать разделители. У нас будет разделитель в конце текста, и все наши формулы будут рассчитывать его положение соответствующим образом.

Или второе решение — в шаге 3, когда мы составляем формулу для расчета позиций разделителей, заполнить ее. Сделайте проверку, если есть ошибка, то введите сознательно большое число, например 1000.

Как разделить текст в excel с помощью формулы

Поэтому последний текст будет вычисляться от последней запятой до тех пор, пока не останется менее 1000 символов, т.е. до конца строки.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector