Excel vba разделить фио
Эта функция извлекает левую часть строки с заданным количеством символов.
Синтаксис функции Left:
Left(строка, длина)
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается левая часть;
- длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.
Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.
Функция Mid
Эта функция извлекает часть строки с заданным количеством символов, начиная с указанного символа (по номеру).
Синтаксис функции Mid:
Mid(строка, начало, [длина])
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается часть строки;
- начало — обязательный аргумент: числовое выражение, указывающее положение символа в строке, с которого начинается извлекаемая часть;
- длина — необязательный аргумент: числовое выражение, указывающее количество вырезаемых символов.
Если аргумент «начало» больше, чем количество символов в строке, функция Mid возвращает пустую строку. Если аргумент «длина» опущен или его значение превышает количество символов в строке, начиная с начального, возвращаются все символы от начальной позиции до конца строки.
Функция Right
Эта функция извлекает правую часть строки с заданным количеством символов.
Синтаксис функции Right:
Right(строка, длина)
- строка — обязательный аргумент: строковое выражение, из значения которого вырезается правая часть;
- длина — обязательный аргумент: числовое выражение, указывающее количество извлекаемых символов.
Если аргумент «длина» равен нулю, возвращается пустая строка. Если аргумент «длина» равен или больше длины строки, возвращается строка полностью.
Пример
В этом примере будем использовать все три представленные выше функции для извлечения из ФИО его составных частей. Для этого запишем в ячейку «A1» строку «Иванов Сидор Петрович», из которой вырежем отдельные компоненты и запишем их в ячейки «A2:A4».
Функции для удобной работы с фамилией, именем и отчеством помогут разделить ячейку с ФИО и автоматизировать процесс создания договора, счета, коммерческого предложения и т.п.
Как разделить ячейку с ФИО на части
Часто при составлении договора, счета, коммерческого предложения или другого документа, мы работаем с фамилией именем и отчеством клиента. Бывает необходимо разделить ФИО и получить только имя, что обратиться к клиенту в тексте документа, бывает нужно сделать графу с подписантом, оставив фамилию и инициалы.
Конечно можно написать формулу, которая извлечет нужные данные из ФИО, но даже опытному человеку потребуется 5-7 минут на это. А что если заранее не знаешь расположение фамилии в тексте? Гораздо проще и удобнее пользоваться готовыми функциями из надстройки VBA-Excel.
Найти фамилию в ФИО
После установки надстройки будет доступна новая функция =ФАМИЛИЯ(ФИО), где
- ФИО - текст или ссылка на ячейку, которая содержит ФИО полностью
Задача данной функции - извлечь из ячейки только фамилию. Причем ей не важен порядок. Фамилия может идти как в начале, так в конце или середине текста.
Найти имя в ФИО
Аналогичная функция для имени =ИМЯ(ФИО). Она извлекает имя из текста с ФИО, также не важно где расположенное.
Найти отчество в ФИО
Последняя функция из этой серии - функция =ОТЧЕСТВО(ФИО) для извлечения отчества.
Примеры работы с ФИО в Excel
Вывести фамилию и инициалы
Одной из распространенных задач при составлении документов - это преобразование текста вида Фамилия Имя Отчество к тексту Фамилия И. О. Решить ее можно используя следующую формулу:
=ФАМИЛИЯ(A1)&" "&ЛЕВСИМВ(ИМЯ(A1))&". "&ЛЕВСИМВ(ОТЧЕСТВО(A1))&"."
где A1 - ссылка на ячейку с фамилией именем и отчеством
Она несколько длинная, но поверьте намного короче и понятнее, если бы мы не использовали функции из надстройки.
Также плюс в том, что нам не важно расположение фамилии в тексте, функция определит все автоматически.
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Распределение содержимого ячейки на соседние столбцы
Смотрите также ветке форума в этой ветке (1 или 2 лично мне дает ограничение - общая конкурсным результатом являетсяВ Интернете решений Здесь может помочь открываем редактор VisualИ, наконец, на третьем в меню«Вставка» нужно учесть, что диапазон ячеек, который взгляд. Давайте разберемся, количество строк, ноПримечание: соответствующей (Вашей) теме.Gustav или 3) на копеечную экономию в
длина текста в кол-во символов этой - масса, от небольшая макрофункция, которая Basic: шаге для каждогоДанные - Текст по, в блоке инструментов Excel воспринимает этим планируем отвести под как разбить ячейку он может содержатьМы стараемся как
fendergod: Тем временем я
выходе получаем это 1-2 знака A1 не превышает одной формулы. Одной ручного "Текст по будет автоматически добавлять
в Excel 2003 и из получившихся столбцов, столбцам
«Иллюстрации», кликаем по
способом разделенную ячейку, таблицу. В той на две части не более одного можно оперативнее обеспечивать: Ребята! Помогите с публикую наше общее слово (без использованияно мне кажется, 99 символов. считаем и формулу столбцам" до пользовательских пробел перед заглавными старше - меню выделяя их предварительно(Data - Text to кнопке как единый элемент. же вкладке в программе Microsoft столбца. Это важные
вас актуальными справочными формулой чтобы считало решение на 65 большого кол-ва пробелов,
оно вполне имеетP.S.2. Сделаем правилом массива, введенную одновременно функций (UDF), формулами буквами. Откройте редакторСервис - Макрос - в окне Мастера,
columns)«Фигуры»А вот, по диагонали«Главная» Excel, и как
из них достаточно материалами на вашем приблизительно вот так знаков. Эта формула с последующим их право на жизнь, еще одно разумное в 3 ячейки - тоже много. Visual Basic как Редактор Visual Basic
4 способа разбить ячейки на части в программе Microsoft Excel
необходимо выбрать формат:.. можно разделить дажев блоке инструментов её разделить по пустыми столбцами справа языке. Эта страница - 74,83 74,83 массива, которую надо удалением). так как на ограничение, органично напрашивающееся при помощи Ctrl+Shift+Enter
Хочется найти - в предыдущем способе,
Разделение ячеек
(Tools - Macro -общийПоявится окноВ открывшемся меню, в обычную ячейку.«Шрифт» диагонали. от выбранных столбец, переведена автоматически, поэтому 74,84 ввести одновременно вЧто то типа практике вряд ли из предыдущего - (как разновидность копирования). именно формулами, но вставьте туда новый Visual Basic Editor)
Способ 1: объединение ячеек
- оставит данныеМастера разбора текстов блокеКликаем правой кнопкой мыши
, кликаем по самой и в появившемся. В появившемся спискеСразу нужно отметить, что которые распространяться данные грамматические ошибки. Для
fendergod и завершить ввод
Способ 2: разделение объединенных ячеек
"Иванов Иван Иванович" длину. превышает 32 символа. - одна формула,Постановка задачи такая. этой функции:Разработчик - Редактор Visual случаевМастера первой фигуре.
-
контекстном меню выбираем выбираем пункт «Все ячейки в программе в любой смежных нас важно, чтобы, в ячейку А1 Ctrl+Shift+Enter. Скрываю текстВ B1 -Ну, и спешу
столбцах. При необходимости эта статья была число,в ячейки B1,C1 формулы под спойлером формула =ПСТР(A1;x;y), где сообщить о своём
Способ 3: разделение по диагонали путем форматирования
32 символа х другая, а в находится правильное полное
-
Range) As String Visual Basic Editor)- необходимо выбирать текста. Или это к углу ячейки«Формат ячеек…»Как видим, несмотря на первичными структурными элементами, вставьте количество пустые вам полезна. Просим Код =ОКРУГЛ(A1/3;2) ,а
Формула - массивная, 2 пробела = копированием C1, то (слева и справа) Len(Txt) = 0Alt+F11 датами, причем формат
Способ 4: разделение по диагонали через вставку фигуры
какой-либо символ отделяет которое вам требуется. на клавиатуре сочетание ничего не делили, подлежат делению на для хранения каждого секунд и сообщить,
-
Код =A1-B1-C1 подумать. например если нужно располагающаяся в 3-х 98 символов, т.е. количество символов решения пробелов и по
помогла ли онаAleksSid200?'200px':''+(this.scrollHeight+5)+'px');">=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";98));;98)) извлечь второе слово, соседних ячейках. в пределах 99. определяется двумя (уникальными) одному пробелу между Out = Mid(Txt,
Делим слипшийся текст на части
и т.д.) уточняется содержимое наших будущих то, что вCtrl+1 создается иллюзия разделенной
- если до этого данных. вам, с помощью: У вас жеКак сможет заметить
- то x=8, y=4viktturMCH формулами в B1 словами. В ячейках 1, 1) For) и копируем туда
- в выпадающем списке отдельных столбцов ( программе Microsoft Excel. ячейки. не были объединены.
- Выделите ячейку, диапазон или
кнопок внизу страницы.
Способ 1. Текст по столбцам
есть файл Excel, пытливый читатель форума,PS: Жаль, что: 73, не массивная.: Есть универсальная (копируемая и C1. B1, C1, D1 i = 2 текст вот этойтекстовыйс разделителями
не существует стандартныхВ открывшемся окне форматаУрок: Как объединить ячейки Но, что делать, весь столбец, где Для удобства также почему все прикладывают идеи темы "Текст у форумчан пропадаетMCH из B1 вМожно включать в нужно вывести Фамилию, To Len(Txt) If пользовательской функции:- этот формат
) или в тексте способов разбить первичную ячеек, переходим во в программе Excel если нам, например, содержатся текстовые значения, приводим ссылку на картинки из файла,
по столбцам" живут интерес к разделу: А ведь достаточно C1 и D1) решение также именованные Имя, Отчество соответственно Mid(Txt, i, 1)Function Substring(Txt, Delimiter, нужен, по большому с помощью пробелов
ячейку на части, вкладкуЕсли нам нужно разделить нужно создать сложную которые требуется разделить. оригинал (на английском проще ведь приложить и побеждают
Serge_007
64 знака 65 формула - 73 (Ctrl+F3) формулы. В по отдельности, без Like "[a-zа-я]" And n) As String
В формулах можно 1, 1) Like Variant x = названием города илификсированная ширина требуемого результата.Около размещенного посередине окна середине таблицы, то
Способ 2. Как выдернуть отдельные слова из текста
делится на двав группе и распространение составные строк. Многие не: Да, за триMCH или будем потихоньку: Достойное начало! в поле "Диапазон",
: Ативности почти нет, вскрываться?Я же пока начиная с =, любые из всех
= Out & n > 0 столбцов с числовымиНа втором шагеИтак, имеем столбец скликаем на одну легче объединить все случае, можно применить
нажмите кнопку ячеек. Например, если свои вопросы именно не догадался. сильные формулисты (DV,Если вскрываться, то
дотелепал до 119
в подсчет включается
- четырех ячеек A1:D1 Mid(Txt, i, 1) And n -
- данными, которые ExcelМастера
- данными, которые надо из двух кнопок,
ячейки двух соседних
Способ 3. Разделение слипшегося текста без пробелов
небольшие хитрости.Текст по столбцам лист содержит потому что неZORRO2005 kim) вообще прогуливают, у нас с в трёх формулах. также само "Имя" (а не только & " " 1 обязательно должен воспринять, если мы выбрали разделить на несколько на которых изображена столбцов, а ужеДля того, чтобы определенные.
длина имени). т.е. можно, например, Out & Mid(Txt, в списке функций для столбца с
Ссылки по теме
- (как в нашем распространенные жизненные примеры: справа налево, или
- нужной ячейки. следует объединить другиеСтолбцах мастера
Разделение ФИО в три ячейки с помощью формул (Формулами! "Текст по столбцам" конкретно здесь не предлагать)
с текстом, которыйКнопка например), то флажок надо - отдельныйПосле этого, ячейка будет«Объединить по строкам»
ячейки. Находясь во Разделение текста по
Сведения об альтернативном способе =ЕСЛИ(СТРОКА()-СТРОКА($D$3)=A$2;B$4-ОТБР(B$4/A$2;2)*(A$2-1);(ОТБР(B$4/A$2;2))) мне нужно показывать и vikttur редко больше нет, иначе
Gustav Пусть это значение означает, что если вид: делимПодробнее (Advanced)Считать последовательные разделители одним столбец под фирму-изготовителя,
разделена косой чертой. вкладке различным столбцам с распределения текста поAlexM только ФИО, а могут уделить хоть бы они проявили: Михаил, я добавил и будет начальным все формулы разные,Деление текста при помощиDelimeter - символ-разделитель (пробел,позволяет помочь Excel (Treat consecutive delimiters отдельный - под по диагонали. Но,Кликаем по той объединенной«Главная» преобразовать мастера текстов, столбцам см. в: Еще вариант решения. остальную инфу вынуждена немного времени на
себя в правила в приближением. Правда, в то суммируются количества готовой функции надстройки запятая и т.д.)
правильно распознать символы-разделители as one)
модель для построения, нужно учесть, что ячейке, которую нужно
на ленте кнопку текста столбцов.Можно объединить ячейки, содержащие
Если формула получается как их использоватьТяжелый случай, но тоже региональных настройках.
Ограничитель строк (Text Qualifier) - отдельно индекс,Следующий способ подойдет для«Объединить и поместить в«Объединить и поместить вПри работе с таблицами функцию СЦЕП или №3 слова в строкеА остальные заняты вижу.
Про массив, думаю, концевые пробелы. Но одна, но такая,
Gustav бывает. Имеем текстЕсли хочется, чтобы такоенужен, чтобы текст отдельно - город,
заключенный в кавычки
отдельно - улица диагонали только в. На этот раз. Жмем на неё. разбить определенную ячейку
Выполните следующие действия.
: Всем огромное спасибо! такое чудо?? делами сделать решение более
неявно имелось в в строгом правильном
B1 дает Фамилию, предложение предновогодней разминки слипшийся в одну
без участия пользователя, (например, название компании и дом) том случае, если выбираем пунктДля наглядности, чтобы лучше на две части.Примечание:AlexMСпасибо!Так что снижение универсальным, без ограничение виду с самого решении знаков может а после копирования на тему этой
длинную фразу (например
то придется использовать "Иванов, Манн ии т.д. она большая, или«Отменить объединение»
Разбить число на 3 ячейки
видеть, что у Но, это не Диапазон, содержащий столбец, который:vikttur активности вызвано объективными
на кол-во символов, начала (как вариант быть существенно меньше. в C1 и очень традиционной задачки. ФИО "ИвановИванИванович"), который небольшую функцию на Фарбер") не делился
, сравнили формулы? Но в соответствующей снижением интереса к слова по порядку в 32 символа правило также разумное и Отчество, то
книгу. Для этоговнутри названия. делить и выберитеНаходясь во вкладке
Дмитрий, спасибо Вам!
Тренинг оказался очень полезным для меня. Формат очень удобен (закачиваешь видео и слушаешь где угодно и когда угодно).
У меня были задумки по автоматизации моментов в моей работе и благодаря Вашему тренингу я теперь знаю как это реализовать. Единственное, хотелось бы, чтобы было отведено побольше времени на прохождение тренинга (например, 3 дня на урок), все-таки сложно сочетать его с работой. Хочется и дальше постигать новое, Жду новых программ по макросам и пр. ))))
Хотел бы сказать Дмитрию большое спасибо за тренинг!
Обучение в такой форме видеоуроков, домашних заданий и общения на форуме имеет очень высокий КПД.
Материал тренинга хорошо структурирован и изложен очень доступно, каждый следующий урок я ждал как новую серию фильма). Всем тем, кто не знаком с макросами и VBA, кто хочет "проапгрейдить" свой уровень Excel, рекомендую данный тренинг. Не собираюсь останавливаться на достигнутом и продолжу обучение на других тренингах Дмитрия.
Для тех, кто будет в дальнейшем проходить этот тренинг хочу сказать, что в работу нужно включаться сразу с первого дня тренинга и не затягивать с выполнением заданий, иначе в конце не успеете, это правда)
Классный и полезный тренинг, рекомендую всем!
С VBA уже дело имела(изучала методом научного тыка). Курс очень понравился, иногда не знаешь, какие вопросы нужно задавать и мучаешься долго и нудно над элементарными, как оказалось, вещами.
Мне было не очень трудно, но очень интересно, изучала запоем, узнала очень много нового. После тренинга поняла, что мои макросы нужно все переписывать, так как оказалось, я не знаю некоторых очень нужных, базовых вещей.
Мне очень понравилась подача материала: просто, доходчиво, с пояснением нюансов и подводных камней.
Очень полезно видеть отладку кода, поиск ошибок по горячим следам. Мне лично уже пригодилось, облегчило жизнь.
Дмитрий, спасибо за терпение и развернутые ответы на очень тупые вопросы!
Дмитрий, хочу еще раз сказать ОГРОМНОЕ спасибо за предоставленные примеры на вебинаре "Пользовательские формы и элементы управления". В моей работе в большинстве случаев приходится сталкиваться с построением отчетов сотрудниками и, чтобы избежать ошибок при вводе данных, я как раз и хотела воспользоваться пользовательскими формами, но знаний для этого было недостаточно. Всю эту информацию конечно можно найти в учебниках, и я не раз их читала, но нет ничего лучше, чем наглядные примеры. По ходу вебинара у меня рождались идеи воплощения того или другого продемонстрированного примера в моей работе. Теперь осталось воплотить это всё в жизнь!
Желаю всем коллегам, присутствовавшим на вебинаре, найти применение полученной информации и не останавливаться на достигнутом!
А от Дмитрия жду новых вебинаров и уроков в свободном доступе. У вас это ОТЛИЧНО получается!
Вчера состоялся мастер-класс по автоматизации в Excel. Для меня это первый опыт участия в обучающем вебинаре.
Огромное спасибо Дмитрию за его добросовестную работу, терпение к новичкам, таким как я, которые задают множество вопросов, порой даже нелепых.
Мастер-класс оказался насыщенным, с большим объемом информации - во всяком случае, для меня. Мы разобрали не только запланированные примеры, но и рассмотрели еще возможные варианты решения поставленной задачи в рамках темы данного мастер-класса. Для себя определила, что занятия он-лайн содержательнее, чем то же занятие в записи: можно задать уточняющие вопросы и увидеть дополнительные решения.
Теперь нужно все уложить "по полочкам" в своей голове и я снова готова к новым занятиям, которые действительно не только полезные, но и увлекательные.
Спасибо, Дмитрий, за Ваш труд, умение простым языком донести непростой материал. Я пару раз открыла учебник VBA и закрыла с твердым убеждением, что это не для моего понимания (хотя за плечами два высших).
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1) , или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1 , не забыв добавить между словами пробелы.
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&"." , не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
- уменьшается количество колонок в таблице;
- в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
- ячейка B1 — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
- ячейка C1 — =ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1)
- ячейка D1 — =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Читайте также: