Если вы часто используете Excel , вы, вероятно, сталкивались с ситуацией, когда у вас есть имя в одной ячейке, и вам нужно разделить имя на разные ячейки. Это очень распространенная проблема в Excel , и вы, вероятно, можете выполнить поиск в Google и загрузить^{(Google search and download)} 100 различных макросов, написанных разными людьми, чтобы сделать это за вас.

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

Если вам не нравятся формулы и вы хотите более быстрое решение^{(quicker solution)} , прокрутите вниз до раздела « Текст в столбцы^{(Text to Columns)} », в котором вы узнаете, как использовать функцию Excel,^{(Excel feature)} чтобы сделать то же самое. Кроме того, функцию преобразования текста в столбцы также лучше использовать, если в ячейке есть более двух элементов, которые необходимо разделить. Например, если в одном столбце объединено 6 полей, то использование приведенных ниже формул^{(formulas below)} станет очень запутанным и сложным.

Отдельные имена в Excel

Для начала давайте посмотрим, как обычно хранятся имена в электронной таблице Excel^{(Excel spreadsheet)} . Наиболее распространенные два способа, которые я видел, это имя, фамилия^(lastname) только с пробелом и фамилия ^(firstname) ,^(lastname) имя с^(firstname) запятой, разделяющей их. Всякий раз, когда я видел средний инициал, это обычно имя , средний ^(lastname)инициал , ^(firstname)фамилия^(midinitial) , как показано ниже :

Используя несколько простых формул и объединив пару из них вместе, вы можете легко разделить имя, фамилию и отчество в отдельные ячейки в Excel . Начнем с извлечения первой части имени. В моем случае мы будем использовать две функции: left и search^{(left and search)} . Логически вот что нам нужно сделать:

Найдите^(Search) в ячейке текст на наличие пробела или запятой^{(space or comma)} , найдите позицию, а затем выньте все буквы слева от этой позиции.

Вот простая формула, которая правильно выполняет работу: =LEFT(NN, SEARCH(” “, NN) – 1) , где NN — это ячейка, в которой хранится имя. -1 предназначен для удаления лишнего пробела или запятой^{(space or comma)} в конце строки.

Как видите, мы начинаем с левой функции^{(left function)} , которая принимает два аргумента: строку и количество символов, которые вы хотите получить, начиная с начала строки. В первом случае мы ищем пробел, используя двойные кавычки и помещая пробел между ними. Во втором случае ищем запятую вместо пробела. Итак, каков результат для трех сценариев, которые я упомянул?

Мы получили имя из строки 3^{(row 3)} , фамилию из строки 5^{(row 5)} и имя из строки 7^{(row 7)} . Здорово! Таким образом, в зависимости от того, как хранятся ваши данные, теперь вы извлекли либо имя, либо фамилию. Теперь о следующей части. Вот что нам теперь нужно сделать по логике:

– Найдите^(Search) в ячейке текст на наличие пробела или запятой^{(space or comma)} , найдите позицию и затем вычтите позицию из общей длины строки. Вот как будет выглядеть формула:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

Итак, теперь мы используем правильную функцию. Это также принимает два аргумента: строку и количество символов, которые вы хотите захватить, начиная с конца строки, идущей влево. Итак, нам нужна длина строки за вычетом положения пробела или запятой^{(space or comma)} . Это даст нам все, что находится справа от первого пробела или запятой^{(space or comma)} .

Отлично^(Great) , теперь у нас есть вторая часть имени! В первых двух случаях вы почти закончили, но если в имени есть средний инициал, вы можете видеть, что результат по-прежнему включает фамилию с средним инициалом. Так как же нам просто получить фамилию и избавиться от среднего инициала? Легкий! Просто^(Just) снова запустите ту же формулу, которую мы использовали для получения второй части имени.

Таким образом, мы просто делаем еще одно правильно и на этот раз применяем формулу к объединенной ячейке среднего инициала и фамилии^{(name cell)} . Он найдет пробел после среднего инициала, а затем возьмет длину за вычетом положения пробела в количестве^{(space number)} символов от конца строки.

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

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

Есть еще один простой способ разделить объединенный текст на отдельные столбцы в Excel . Это функция под названием « Текст в столбцы^{( Text to Columns)} », и она работает очень хорошо. Также намного эффективнее, если у вас есть столбец, содержащий более двух фрагментов данных.

Например, ниже у меня есть некоторые данные, где в одной строке^{(one row)} содержится 4 элемента данных, а в другой строке — 5 элементов данных. Я хотел бы разделить это на 4 столбца и 5 столбцов соответственно. Как видите, пытаться использовать приведенные выше формулы было бы нецелесообразно.

В Excel сначала выберите столбец, который вы хотите разделить. Затем перейдите на вкладку « Данные^(Data) », а затем нажмите « Текст в столбцы^{( Text to Columns)} » .

Это вызовет мастера Text to Columns^{(Columns wizard)} . На шаге 1^{(step 1)} вы выбираете, будет ли поле с разделителями или фиксированной шириной. В нашем случае мы выберем Delimited .

На следующем экране вы выберете разделитель. Вы можете выбрать табуляцию, точку с запятой, запятую, пробел или ввести пользовательский^{(custom one)} вариант .

Наконец, вы выбираете формат данных для столбца. Обычно General отлично подходит для большинства типов данных. Если у вас есть что-то конкретное, например даты, выберите этот формат.

Нажмите «Готово»^(Finish) и наблюдайте, как ваши данные волшебным образом распределяются по столбцам. Как видите, одна строка превратилась в пять столбцов, а другая — в четыре. Функция Text to Columns^{(Columns feature)} очень мощная и может сделать вашу жизнь намного проще.

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

How to Separate First and Last Names in Excel

If you use Excel a lot, you have probably run across a situation where you have a name in a single cell and you need to separate the name іnto different cells. Τhis is a very common issue in Excel and you can рrobаbly do a Google search and download 100 differеnt macrоs written by various people to do it for yoυ.

However, in this post, I’ll show you how to setup a formula so you can do it yourself and actually understand what is going on. If you use Excel a lot, it’s probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.

If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing. In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.

Separate Names in Excel

To get started, let’s see how names are usually stored in a Excel spreadsheet. The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below:

Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel. Let’s start with extracting the first part of the name. In my case, we’re going to use two functions: left and search. Logically here’s what we need to do:

Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position.

Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. The -1 is there to remove the extra space or comma at the end of the string.

As you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. In the first case, we search for a space by using double quotes and putting a space in-between. In the second case, we are looking for a comma instead of a space. So what is the result for the 3 scenarios I have mentioned?

We got the first name from row 3, the last name from row 5 and the first name from row 7. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. Now for the next part. Here’s what we need to do logically now:

– Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Here’s what the formula would look like:

=RIGHT(NN,LEN(NN) -SEARCH(” “,NN))

So now we use the right function. This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left. So we want the length of the string minus the position of the space or comma. That will give us everything to the right of the first space or comma.

Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. So how do we just get the last name and get rid of the middle initial? Easy! Just run the same formula again that we used to get the second section of the name.

So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.

So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs.

Text to Columns

There is also another easy way you can separate combined text into separate columns in Excel. It’s a featured called Text to Columns and it works very well. It’s also much more efficient if you have a column that has more than two pieces of data.

For example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. I would like to split that into 4 columns and 5 columns, respectively. As you can see, trying to use the formulas above would be impractical.

In Excel, first select the column you want to separate. Then, go ahead and click on the Data tab and then click on Text to Columns.

This will bring up the Text to Columns wizard. In step 1, you choose whether the field is delimited or fixed width. In our case, we’ll choose Delimited.

On the next screen, you will choose the delimiter. You can pick from tab, semicolon, comma, space or type a custom one in.

Finally, you choose the data format for the column. Normally, General will work just fine for most types of data. If you have something specific like dates, then choose that format.

Click Finish and watch how your data is magically separated into columns. As you can see, one row turned into five columns and the other one into four columns. The Text to Columns feature is very powerful and can make your life a lot easier.

If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help. Enjoy!

Related posts

• Добавить линейный Regression Trendline на Excel Scatter Plot

• Как сделать Histogram в Excel

• Как надежно Password Protect Excel File

• Как создать Flowchart в Word and Excel

• Как Create Labels в Word от Excel Spreadsheet

• Как создать Drop Down List в Excel

• Автоподбор ширины столбцов и высоты строк в Excel

• Связать ячейки между листами и книгами в Excel

• Как скрыть листы, ячейки, столбцы и формулы в Excel

• Как удалить, взломать или взломать забытый пароль Excel XLS

• Учебное пособие по основам Microsoft Excel — обучение работе с Excel

• Как фильтровать данные в Excel

• Используйте Excel, чтобы определить эффективную процентную ставку по номинальной процентной ставке

• Как переключаться между листами в Excel

• Как открыть несколько экземпляров Excel

• Как использовать функцию YEARFRAC в Excel

• Как записать Macro в Excel

• Как добавить планки погрешностей в Excel

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

• Форматировать ячейки с помощью условного форматирования в Excel