Auditportal.ru

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

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

Объединение ячеек в Microsoft Excel

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

Самый простой способ

1 . Выделите то, что вы хотите объединить.

2 . Нажимаем на кнопку во вкладке «Главная» в верхней панели.

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

Еще один простой способ

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

2 . Чтобы объединить ячейки в окне, перейдите на вкладку «Выравнивание», установите флажок «Объединить ячейки» и нажмите OK.

Объединение ячеек с данными

Возможно, что при объединении нескольких ячеек, уже состоящих из информации, некоторая информация не будет сохранена. Необходимо будет удалить некоторую информацию.

Чтобы избежать этого, необходимо вставить их другим способом. Существует несколько способов сделать это: объединение данных, использование блокнота и макроса.

Первый способ (сцепление) я бы рекомендовал начинающим пользователям, второй (Блокнот) – любителям необычных решений, а третий (макрос) – тем, кто знает, что такое Visual Basic.

Способ первый (через сцепление)

Шаг 1: склеиваем данные

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

Склеивание через функцию «СЦЕПИТЬ» . Допустим, у нас есть отдельно имя, фамилия и отчество. Все эти данные нужно объединить, а для этого сначала склеить их.

1 . Щелкните по пустой ячейке.

2 . CUT пишется прописными буквами после символа =.

3 . Открываем скобку и щелкаем по первой ячейке с данными, затем печатаем точку с запятой и щелкаем по второй, затем опять точку с запятой – и по третьей. После это закрываем скобку.

4 . Нажмите клавишу Enter на клавиатуре.

Все данные слепятся в одну сплошную строку. Если же их нужно каким-то образом разделить (пробелом, запятой и т.д.), этот символ также нужно добавить в формулу, но только в кавычках. Пример знака – в качестве разделителя:

То же самое с пробелом: его нужно набрать в кавычках и разделить точками с запятой:

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

Склеивание через знак & . Он расположен в том же месте на клавиатуре, где и символ 7. При наборе текста на английском языке держите нажатой клавишу Shift.

Процедура аналогична склеиванию с помощью функции Link (Связь):

1 . Заполните пустую ячейку, щелкнув в ней мышью и набрав =

2 . Начните с первой ячейки данных, введите символ &, щелкните на второй ячейке, снова введите символ и щелкните на третьей, и так далее.

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

Шаг 2: убираем дубли

Независимо от того, каким способом, мы склеивали данные.

Если нужно получить такой же результат и для остальных ячеек, эту «формулу» можно растянуть:

  • Щелкните ячейку с результатом
  • Наведите курсор на маленький черный квадрат в правом нижнем углу
  • Когда курсор появится в виде тонкого черного знака плюс, нажмите левую кнопку мыши и, не отпуская ее, перетащите курсор в другие ячейки.

Вот что я получил:

Подробнее о «растягивании» можно узнать из вот этого урока.

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

Иногда это работает, но что если вам нужны только склеенные данные? Выход есть!

1 . Выберите новые (склеенные) ячейки.

2 . Щелкаем по любой из них правой кнопкой мышки и из меню выбираем «Копировать».

3 . Любые другие пустые ячейки также должны быть выделены.

4 . Нажимаем по любой из них правой кнопкой мышки и выбираем «Специальная вставка».

5 . В окошке выбираем пункт «Значения» (вверху) и нажимаем ОК.

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

Теперь можно удалить все предыдущие данные и скопировать и вставить эти (новые) данные вместо них.

Способ второй (через Блокнот)

Честно говоря, мне этот способ даже больше нравится – он быстрее.

1 . Выделите ячейки с нужными данными в Excel (можно выделить целые столбцы/строки).

2 . Копируем их (правая кнопка мыши – Копировать).

3 . Открываем программу Блокнот: Пуск – Все программы – Стандартные — Блокнот. Или открываем Пуск и печатаем слово блокнот в поле для поиска (внизу).

4 . Вставляем в окно программы скопированные данные (правой кнопкой мышки по пустому месту – Вставить).

5 . Скопируйте символ табуляции.

В Блокноте нажмите Tab один раз в пустой строке (можно попасть туда, нажав Enter).

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

Указатель сразу перемещается чуть правее. Зажимаем левую кнопку мышки и выделяем этот отрезок, который визуально похож на десяток пробелов. Затем копируем (правой кнопкой по выделенному – Копировать).

6 . Вверху программы Блокнот нажимаем на пункт «Правка» и выбираем «Заменить…».

7 . В первое поле («Что:») вставляем скопированный символ табуляции, а во второе поле («Чем:») вставляем необходимый нам разделитель, например, жмем клавишу пробел.

8 . Нажимаем на кнопку «Заменить все» и закрываем это маленькое окошко.

После этого данные в Блокноте немного изменятся – текст как будто бы чуть-чуть сожмется.

9 . Выделите все это в блокноте и скопируйте.

10 . Перейдите в Excel и вставьте скопированные данные (предыдущие данные будут удалены).

Способ третий (макрос)

Еще один способ объединить ячейки в Excel без потери данных. Это немного сложнее — для тех, кто знает, что такое Visual Basic.

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

1 . Открываем вкладку «Вид» и нажимаем на кнопку «Макросы».

2 . Печатаем название для макроса, нажимаем «Создать».

3 . Откройте редактор Visual Basic. Код выглядит следующим образом:

Sub MergeToOneCell()
Const sDELIM As String = " " 'разделитель в данном случае пробел.
Dim rCell As Range
Dim sMergeStr As String
If TypeName(Selection) <> "Range" Then Exit Sub 'если выделяются не ячейки, тогда программа выходит
With Selection
For Each rCell In .Cells
sMergeStr =sMergeStr & sDELIM & rCell.Text 'процесс сбора текста из ячеек
Next rCell
Application.DisplayAlerts = False 'выключаем обычное предупреждение о потере текста
.Merge Across:=False 'объединение ячеек
Application.DisplayAlerts = True
.Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) ' добавляем к объединенным ячейкам суммированный текст
End With
End Sub

4 . Закрываем редактор Visual Basic.

Теперь, чтобы объединить ячейки с данными, нужно их выделить, после чего на вкладке «Вид» нажать кнопку «Макросы» и в новом окошке на «Выполнить».

Правда, такой файл нужно будет сохранить в специальном формате с поддержкой макросов: Файл – Сохранить как – в поле «Тип файла» выбрать «Книга Excel с поддержкой макросов».

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

Как лучше всего оставить в ячейке только цифры или только текст?

Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 — чтобы можно было провести с этим некие математические действия. Если это только одна ячейка — проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?

  • Поставлено 763 мыла
  • Всего 34
  • Тюбика — 54 поставлено
  • И т.д.
Читайте так же:
Как извлечь первые или первые два слова из текстовых строк в листе Google?

Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.

СПОСОБ 1: не используем макросы
можно применить формулу массива , вроде такой:
=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99)));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99)))+1)
Три важных момента:

  1. Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
  2. в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА($1:$99) на СТРОКА($1:$200). Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
  3. формула не обработает корректно текст "Было доставлено кусков мыла 763шт., а заказывали 780" и ему подобный, где числа раскиданы по тексту.

Теперь кратко объясним формулу на примере предложения: Было доставлено 763 куска мыла.

  • в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
  • блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99)))
    вычисляет позицию первой цифры в ячейке — 29
  • блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1));СТРОКА($1:$99))
    вычисляет позицию последней цифры в ячейке — 31
  • в результате получается: =ПСТР(A1; 29 ; 31 — 29 +1)
    функция ПСТР извлекает из текста, указанного первым аргументом(A1) текст, начиная с указанной позиции( 29 ) с количеством символов, указанным третьим аргументом( 31 — 29 +1)
  • И в итоге:
    =ПСТР(A1; 29 ; 31 — 29 +1)
    => =ПСТР(A1; 29 ;2+1)
    => =ПСТР(A1; 29 ;3)
    => 763

СПОСОБ 2: используем макросы
Самый главный недостаток метода при помощи формулы, приведенной выше — из текста "Было доставлено кусков мыла 763шт., а заказывали 780" формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780.
Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот — цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции , которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.

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

F unction Extract_Number_from_Text(sWord As String, Optional Metod As Integer) ‘sWord = ссылка на ячейку или непосредственно текст ‘Metod = 0 – числа ‘Metod = 1 – текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len(sWord) sSymbol = Mid(sWord, i, 1) If Metod = 1 Then If Not LCase(sSymbol) Like "*[0-9]*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i — 1, 1) Like "*[0-9]*" And Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(sWord, i — 1, 1) Like "*[0-9]*" Or Not Mid(sWord, i + 1, 1) Like "*[0-9]*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_Number_from_Text = sInsertWord End Function

Перенос текста в ячейке Excel

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

  1. 1 Перенос текста в одной ячейке Excel
  2. 2 Перенос текста в объединенных ячейках Excel
  3. 3 Как сделать перенос строки в ячейке Excel
    1. 3.1 Использование клавиш быстрого доступа
    2. 3.2 Перемещение линий в ячейке Excel с помощью формулы

    Перенос текста в одной ячейке Excel

    Есть длинный текст, который мы хотим поместить в одну ячейку, пока он виден целиком. Есть два варианта расположения текста: без объединения ячеек и с объединением ячеек. Для начала рассмотрим первый вариант.

      Выделите ячейку мышкой, нажмите по выделенной области правой кнопкой мыши и выберите «Формат ячеек»;

    Формат ячеек

    Excel - переносить по словам

    Длинный текст в ячейке Excel

    Длинный текст в ячейке Excel

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

      Выделите мышкой ячейку, затем на панели инструментов, в блоке «Выравнивание» нажмите пиктограмму «Перенести текст» (смотрите скриншот);

    Перенести текст Excel

    Длинный текст в ячейке Excel с переносом

    Длинный текст в ячейке Excel с переносом

    Перенос текста в объединенных ячейках Excel

    Вот как вставить дефисный текст в объединенные ячейки. Для чего это нужно? Все очень просто: вам может понадобиться большая ячейка «перед» маленькими ячейками, используемыми по умолчанию в Excel.

      Выделите мышкой или с помощью комбинации Shift + Стрелки нужное количество ячеек;

    Выделение нескольких ячеек Excel

    Объединение нескольких ячеек Excel

    Перенести текст Excel

    Длинный текст в объединенных ячейках Excel с переносом

    Тот же результат может быть получен следующим образом:

      Выделите требуемое количество ячеек, кликните по выделенной области и выберите в меню опцию «Формат ячеек»;

    Формат нескольких ячеек Excel

    Перенос по словам и объединение ячеек в Excel

    Длинный текст в объединенных ячейках Excel с переносом

    Как сделать перенос строки в ячейке Excel

    С помощью «горячих клавиш»

    Самый простой способ переноса текста в ячейку Excel — это использование Alt + Enter .

    1. Установите курсор в ячейку и начинайте вводить текст;
    2. В том месте, где вы хотите сделать перенос, нажмите Alt + Enter и продолжайте вводить текст с новой строки;

    Перенос строки в ячейке Excel с помощью Alt + Enter

    Ячейка Excel с переносом текста на новую строку

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

    Перенос строки в ячейке Excel формулой

    Для переноса текста в ячейки можно также использовать готовые формулы, например формулу совпадения текста. Он имеет следующий формат:

    Между частями текста добавляется специальный невидимый символ разрыва, который называется SYMBOL(10). Кстати, при нажатии Alt + Enter такой же дефис добавляется в конце строки. Текст в двойных кавычках может быть произвольным, если только фразы находятся на разных строках внутри ячейки.

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

    Функция СЦЕПИТЬ и знак для переноса текста в ячейке Excel

    Excel - переносить по словам

    Ячейка Excel с переносом текста на новую строку с помощью символа

    Как скрыть длинный текст в ячейке Excel

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

      Выделите ячейку и вставьте в нее свой текст так, как есть. Он растянется по всей длине ячеек;

    Длинный текст не умещается в ячейке

    Формат ячеек

    Формат: Выравнивание - По горизонтали с заполнением

    Как скрыть текст в ячейке Excel

    Увидеть скрытый текст ячейке Excel целиком

    Вот альтернативный способ.

      Выделите ячейку и вставьте в нее свой текст так, как есть. Естественно, он растянется по всей длине ячеек;

    Используя этот метод, можно увидеть больше скрытой информации.

      Объедините две или три ячейки с помощью инструмента «Объединить ячейки»;

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

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