Вставка в эксель с разделителями: Вставка чисел с разделителями в виде точки на лист Excel

Делим слипшийся текст на части

93744
10.11.2012
Скачать пример

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

• ФИО в одном столбце (а надо — в трех отдельных, чтобы удобнее было сортировать и фильтровать) 
• полное описание товара в одном столбце (а надо — отдельный столбец под фирму-изготовителя, отдельный — под модель для построения, например, сводной таблицы)
• весь адрес в одном столбце (а надо — отдельно индекс, отдельно — город, отдельно — улица и дом)
• и т.д.

Поехали..

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные — Текст по столбцам (Data — Text to columns). Появится окно Мастера разбора текстов:

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

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) — необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой

внутри названия.

И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

• общий — оставит данные как есть — подходит в большинстве случаев
• дата — необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
• текстовый — этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

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

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

• в Excel 2003 и старше — меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor)
• в Excel 2007 и новее — вкладка Разработчик — Редактор Visual Basic (Developer — Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String
Dim x As Variant
    x = Split(Txt, Delimiter)
    If n > 0 And n - 1 <= UBound(x) Then
        Substring = x(n - 1)
    Else
        Substring = ""
    End If
End Function

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

где

• Txt — адрес ячейки с текстом, который делим
• Delimeter — символ-разделитель (пробел, запятая и т. д.)
• n — порядковый номер извлекаемого фрагмента

Например:

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Function CutWords(Txt As Range) As String
    Dim Out$
    If Len(Txt) = 0 Then Exit Function
    Out = Mid(Txt, 1, 1)
    
    For i = 2 To Len(Txt)
        If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then
            Out = Out & Mid(Txt, i, 1) & " "
        Else
            Out = Out & Mid(Txt, i, 1)
        End If
    Next i
    CutWords = Out
End Function

Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:

 Ссылки по теме

• Деление текста при помощи готовой функции надстройки PLEX
• Что такое макросы, куда вставлять код макроса, как их использовать

Как разделить текст на столбцы в Microsoft Excel

Виктор Бухтеев

03 фев в 09:27

1. 7K

Обсудить

Личный опыт
#Программы
#Microsoft

8 мин. чтения

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

Как выглядит склеенный текст в Excel

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Разделение текста на столбцы в Excel

Задача ясна, значит, сейчас предстоит разобраться с ней. Откройте лист и найдите там необходимый диапазон с данными. Если еще не вставили его в Excel, создайте пустой лист и сделайте это. После этого выполните такие действия:

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

2. Вызовите инструмент «Текст по столбцам».

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

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

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

6. В моем случае разделителем выступает косая черта (слэш) – нужно отметить галочкой «Другой» и напечатать этот знак в соответствующем поле. Как только вы это сделаете, ниже сразу отобразится результат выполнения операции.

7. Если при использовании в качестве разделителя выступают последовательные знаки, например кавычки, отметьте маркером пункт «Считать последовательные разделители одним» и из раскрывающегося списка выберите подходящий для вас вариант.

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

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

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

1. Выделите столбцы и строки, перейдите на вкладку «Вставка» и нажмите «Таблица».

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

3. Если нужно, разверните меню «Форматировать как таблицу» и выберите другой вариант визуального оформления.

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

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

Личный опыт

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Рекомендуем

Microsoft Excel — Как вставить новый разделитель строк через регулярные (каждые 13-е) интервалы разделителя в текстовом файле, в котором нет нового разделителя строк

спросил
1 год, 8 месяцев назад

Изменено
1 год, 7 месяцев назад

Просмотрено
232 раза

У меня есть TXT-файл с необработанными данными. Мне нужно преобразовать их в Excel, но мои попытки не увенчались успехом. В файле в сотни раз больше разделителя (разделителя), и это помогает мне конвертировать в Excel, но все данные поступают в одну строку. В моих данных нет маркера/разделителя для новой строки. Я знаю, что данные имеют 12 столбцов. Таким образом, на 13-м разделителе должна быть новая строка, и данные могут перейти в новую строку.

Я читал много сайтов о том же, но нигде не нашел такой же ситуации. Здесь, на этом сайте, я нашел небольшое сходство, но у пользователя нет разделителя в данных. Так что мне не пригодится. Что я могу сделать?

• Microsoft-Excel
• редактирование текста
• новые строки
• текстовые файлы

2

Я бы использовал Notepad++, чтобы заменить каждый 13-й символ разделителя новой строкой.

Допустим, ваш разделитель равен @ .

1. Откройте файл . txt с помощью Notepad++
2. Поиск -> Заменить…
3. Что найти: ((?:.*?\@){12}.*?)\@
   Заменить на: $1\n
   Проверить Перенести
   Режим поиска Регулярное выражение
   Заменить все

^{Примечание. Если ваш разделитель не @ , в шаблоне на шаге 3 замените @ с вашим разделителем.}

1

Аналогично (ZygD: спасибо за RE !), но в Perl, а не в NotePad++

?@){12}. ?)@/$1\n/ g ; print; » >rfile

, где ifile — ваш входной файл

 rfile — результаты
  "@" - ваш разделитель - измените в соответствии с вашим случаем
  Это соответствует запрошенным 13 (12+1)
      
 

Кто-нибудь попробует PowerShell?

0

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя электронную почту и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания, политикой конфиденциальности и политикой использования файлов cookie

.

9Функция листа 0000 — вкладка (\ t) не распознается как разделитель столбцов при вставке в Excel 2013

спросил
9 лет, 5 месяцев назад

Изменено
4 года, 9 месяцев назад

Просмотрено
14 тысяч раз

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

Не знаю почему, но Excel больше не распознает вкладки, и мне приходится каждый раз использовать инструменты импорта текста!

Странно, что когда я копирую данные из excel в блокнот, столбцы разделяются табуляцией!! Но если я попытаюсь вставить его обратно в Excel, данные попадут в те же столбцы!

Это разочаровывающее и очень глупое поведение Excel!

Почему раньше работало? Старая версия Excel была умнее новой, что ли?
Что я могу сделать, чтобы это исправить?

Изменить: похоже, это может произойти из-за какой-то ошибки Excel. После перезагрузки \t снова заработал. Так было еще 2 или 3 раза.

• функция рабочего листа
• копировать-вставить
• Microsoft-Excel-2013

6

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

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

1

Эта проблема возникла у меня после запуска макроса, который импортировал данные с помощью «|» символ в качестве разделителя столбца. Несмотря на то, что я закрыл лист, на котором выполнялся этот макрос, и закрыл все листы, созданные макросом, другие мои листы открываются во время выполнения макроса по умолчанию «|» как разделитель, а также любой вновь открытый лист.