Разделяне на текст в различни колони с функции

Разделяне на текст в различни колони с функции

Можете да използвате функциите LEFT, MID, RIGHT, SEARCH и LEN, за да манипулирате с текстови низове в данните. Можете например да разпределите собственото, бащиното и фамилното име от една клетка в три отделни колони.

Ключът към разделянето на части на име с текстови функции, е позицията на всеки знак в текстовия низ. Позициите на интервалите в текстовия низ са важни, понеже те сочат началото или края на компонентите на името в низа.

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

Тази статия ви показва как да извличате разни части от различни формати на имена с помощта на тези удобни функции. Можете също да разделяте текст в различни колони със съветника за преобразуване на текст в колони.

Примерно име

Описание

Собствено име

Бащино име

Фамилно име

Суфикс

1

Jeff Smith

Няма бащино име

Jeff

Smith

2

Eric S. Kurjan

Един инициал за бащино име

Eric

S.

Kurjan

3

Janaina B. G. Bueno

Два инициала за бащино име

Janaina

B. G.

Bueno

4

Kahn, Wendy Beth

Фамилното име е първо, със запетая

Wendy

Beth

Kahn

5

Mary Kay D. Andersen

Първо име с две части

Mary Kay

D.

Andersen

6

Paula Barreto de Mattos

Фамилно име с три части

Paula

Barreto de Mattos

7

James van Eaton

Фамилно име с две части

James

van Eaton

8

Bacon Jr., Dan K.

Първо фамилното име и суфикс, със запетая

Dan

K.

Bacon

Jr.

9

Gary Altman III

Със суфикс

Gary

Altman

III

10

Mr. Ryan Ihrig

С префикс

Ryan

Ihrig

11

Julie Taft-Rider

Фамилно име с тире

Julie

Taft-Rider

Забележка : В графиките в следващия пример осветеното място в пълното име показва знака, за който извършва търсене съответната формула SEARCH.

Този пример разделя два компонента: собственото име и фамилното име. Двете имена са разделени с единичен интервал.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Jeff Smith

Няма бащино име

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Собствено име

    Собственото име започва с първия знак в низа (J) и завършва на петия знак (интервала). Формулата връща пет знака в клетка A2, започвайки отляво.

    Формула за извличане на собствено име

    Използване на функцията SEARCH за намиране на стойността за "брой_знаци"

    Търсене на числовата позиция на интервала в A2, започвайки отляво.

  2. Фамилно име

    Фамилното име започва от интервала, на пет знака отдясно и завършва с последния знак вдясно (h). Формулата извлича пет знака в A2, започвайки отдясно.

    Формула за извличане на фамилно име

    Използване на функциите SEARCH и LEN за намиране на стойността за num_chars:

    Търсене на числовата позиция на интервала в A2, започвайки отляво. (5)

  3. Пребройте общата дължина на текстовия низ, а после извадете броя знаци отляво на първия интервал, намерен в стъпка 1.

Този пример използва собствено име, инициал за бащиното име и фамилно име. Компонентите на името са разделени с интервали.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Eric S. Kurjan

Един инициал за бащино име

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (инициал за бащино име)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1))

Формула

Резултат в момента (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

  1. Собствено име

    Собственото име започва с първия знак отляво (Е) и завършва на петия знак (първият интервал). Формулата връща пет знака в A2, започвайки отляво.

    Формула за разделяне на собствено и фамилно име, плюс среден инициал

    Използване на функцията SEARCH за намиране на стойността за num_chars

    Търсене на числовата позиция на интервала в A2, започвайки отляво. (5)

  2. Бащино име

    Бащиното име започва от шестата знакова позиция (Б) и завършва на осма позиция (вторият интервал). Тази формула използва вложени функции SEARCH, за да намери втората поява на интервал.

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

    Подробности за формула за разделяне на собствено, бащино и фамилно име

    Използване на функцията SEARCH за намиране на стойността за "начална_позиция":

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво (5).

  3. Добавяне на 1, за да се получи позицията на знака след първия интервал (Б). Тази числова позиция е началната позиция на бащиното име (5 + 1 = 6).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (5)

  4. Добавяне на 1 за получаване на позицията на знака след първия интервал (Б). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(5 + 1 = 6).

  5. Търсене на втората поява на интервал в A2, започвайки от шестата позиция (Б), намерена на стъпка 4. Този номер на знак е крайната позиция на бащиното име. (8)

  6. Търсене на числовата позиция на интервал в A2, започвайки от първия знак отляво. (5)

  7. Взема номера на знака на втория интервал, намерен на стъпка 5, и изважда номера на знака на първия интервал, намерен на стъпка 6. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от шестата позиция, намерена на стъпка 2(8 – 5 = 3).

  8. Фамилно име

    Фамилното име започва шест знака отдясно (Б) и завършва с първия знак отдясно (в). Тази формула използва вложени SEARCH функции, за да намери втората и третата поява на интервал (които са на петата и осмата позиция отляво).

    Формулата извлича шест знака в A2, започвайки отдясно.

    Втората функция SEARCH във формула за разделяне на собствено, бащино и фамилно име
  9. Използване на функцията LEN и вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на интервал в A2, започвайки от първия знак отляво. (5)

  10. Добавяне на 1 за получаване на позицията на знака след първия интервал (Б). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(5 + 1 = 6).

  11. Търсене на втората поява на интервал в A2, започвайки от шестата позиция (Б), намерена на стъпка 2. Този номер на знак е крайната позиция на бащиното име. (8)

  12. Пресмятане на общата дължина на текстовия низ в A2 с последващо изваждане на броя на знаците отляво на втората поява на интервал, намерен на стъпка 3. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име (14 – 8 = 6).

Ето пример за извличане на два инициала в средата. Първата и третата поява на интервал отделят частите на името.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Janaina B. G. Bueno

Два инициала за бащино име

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (инициали за бащино име)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-SEARCH(" ";A2;1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-SEARCH(" ";A2;1))

Формула

Резултат в момента (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

  1. Собствено име

    Собственото име започва с първия знак отляво (J) и завършва на осмия знак (първият интервал). Формулата извлича първите осем знака в A2, започвайки отляво.

    Формула за разделяне на собствено име, фамилно име и два междинни инициала

    Използване на функцията SEARCH за намиране на стойността за num_chars

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (8)

  2. Бащино име

    Бащиното име започва от деветата позиция (B) и завършва на четиринайсетата позиция (третият интервал). Тази формула използва вложени SEARCH, за да намери първата, втората и третата поява на интервал в осмата, единайсетата и четиринайсетата позиция.

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

    Формула за разделяне на собствено име, фамилно име и два междинни инициала

    Използване на функцията SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (8)

  3. Добавяне на 1, за да се получи позицията на знака след първия интервал (B). Тази числова позиция е началната позиция на бащиното име (8 + 1 = 9).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (8)

  4. Добавяне на 1 за получаване на позицията на знака след първия интервал (B). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(8 + 1 = 9).

  5. Търсене на втория интервал в A2, започвайки от деветата позиция (B), намерена на стъпка 4. (11).

  6. Добавяне на 1 за получаване на позицията на знака след втория интервал (G). Този номер на знак е началната позиция, от която искате да започнете търсенето на третия интервал(11 + 1 = 12).

  7. Търсене на третия интервал в A2, започвайки от дванайсетата позиция, намерена на стъпка 6. (14)

  8. Търсене на числовата позиция на първия интервал в A2. (8)

  9. Взема номера на знака на третия интервал, намерен на стъпка 7 и изважда номера на знака на първия интервал, намерен на стъпка 6. Резултатът е броят на знаците, които MID извлича от текстовия низ от деветата позиция, намерена на стъпка 2.

  10. Фамилно име

    Фамилното име започва на пет знака отдясно (B) и завършва с първия знак отдясно (о). Тази формула използва вложени SEARCH, за да намери първата, втората и третата поява на интервал.

    Формулата извлича пет знака в A2, започвайки отдясно на пълното име.

    Формула за разделяне на собствено име, фамилно име и два междинни инициала

    Използване на вложени SEARCH и LEN функции за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (8)

  11. Добавяне на 1 за получаване на позицията на знака след първия интервал (B). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(8 + 1 = 9).

  12. Търсене на втория интервал в A2, започвайки от деветата позиция (B), намерена на стъпка 2. (11)

  13. Добавяне на 1 за получаване на позицията на знака след втория интервал (G). Този номер на знак е началната позиция, от която искате да започнете търсенето на третата поява на интервал(11 + 1 = 12).

  14. Търсене на третия интервал в A2, започвайки от дванайсетата позиция (G), намерена на стъпка 6. (14)

  15. Пресмятане на общата дължина на текстовия низ в A2 с последващо изваждане на броя на знаците отляво на третия интервал, намерен на стъпка 5. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(19 – 14 = 5).

В този пример фамилното име е пред собственото, а бащиното име се появява накрая. Запетаята отбелязва края на фамилното име, а отделните части на името са разделени с интервали.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Kahn, Wendy Beth

Фамилното име е първо, със запетая

Формула

Резултат (собствено име)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-SEARCH(" ";A2;1))

Формула

Резултат (бащино име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

Формула

Резултат в момента (фамилно име)

'=LEFT(A2; SEARCH(" ";A2;1)-2)

=LEFT(A2; SEARCH(" ";A2;1)-2)

  1. Собствено име

    Собственото име започва със седмия знак отляво (W) и завършва на дванайсетия знак (вторият интервал). Тъй като собственото име се намира по средата на пълното име, трябва да използвате функцията MID, за да извлечете собственото име.

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

    формула за разделяне на фамилно име, следвано от собствено и бащино име

    Използване на функцията SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (6)

  2. Добавяне на 1, за да се получи позицията на знака след първия интервал (W). Тази числова позиция е началната позиция на бащиното име (6 + 1 = 7).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (6)

  3. Добавяне на 1 за получаване на позицията на знака след първия интервал (W). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(6 + 1 = 7).

    Търсене на втория интервал в A2, започвайки от седмата позиция (W), намерена на стъпка 4. (12)

  4. Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (6)

  5. Взема номера на знака на втория интервал, намерен на стъпка 5 и изважда номера на знака на първия интервал, намерен на стъпка 6. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от седмата позиция, намерена на стъпка 2(12 – 6 = 6).

  6. Бащино име

    Бащиното име започва на четири знака отдясно (B) и завършва с първия знак отдясно (h). Тази формула използва вложени SEARCH, за да намери първата и втората поява на интервал на шестата и дванайсетата позиция отляво.

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

    Формула за разделяне на фамилно име, следвано от собствено и бащино име

    Използване на функцията LEN и вложени функции SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (6)

  7. Добавяне на 1 за получаване на позицията на знака след първия интервал (W). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(6 + 1 = 7).

  8. Търсене на втората поява на интервал в A2, започвайки от седмата позиция (W), намерена на стъпка 2. (12)

  9. Пресмятане на общата дължина на текстовия низ в A2 с последващо изваждане на броя на знаците отляво на втория интервал, намерен на стъпка 3. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(16 – 12 = 4).

  10. Фамилно име

    Фамилното име започва с първия знак отляво (K) и завършва на четвъртия знак (n). Формулата извлича четири знака, започвайки отляво.

    Формула за разделяне на фамилно име, следвано от собствено и бащино име

    Използване на функцията SEARCH за намиране на стойността за num_chars

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (6)

  11. Изважда 2, за да получи числовата позиция на последния знак на фамилното има (n). Резултатът е броят знаци, които искате да извлече LEFT(6 – 2 =4).

Този пример използва собствено име от две части, Mary Kay. Вторият и третият интервал разделят частите на името.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Mary Kay D. Andersen

Първо име с две части

Формула

Резултат (собствено име)

LEFT(A2; SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=LEFT(A2; SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

Формула

Резултат (инициал за бащино име)

'=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

Формула

Резултат в момента (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

  1. Собствено име

    Собственото име започва с първия знак отляво и завършва на деветия знак (вторият интервал). Тази формула използва вложени SEARCH, за да намери втората поява на интервал отляво.

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

    Формула за разделяне на собствено име, бащино име и фамилно име

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (5)

  2. Добавяне на 1 за получаване на позицията на знака след първия интервал (K). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(5 + 1 = 6).

  3. Търсене на втората поява на интервал в A2, започвайки от шестата позиция (K), намерена на стъпка 2. Резултатът е броят на знаците, които LEFT извлича от текстовия низ. (9)

  4. Бащино име

    Бащиното име започва от десетата позиция (D) и завършва на дванайсетата позиция (третият интервал). Тази формула използва вложени SEARCH, за да намери първата, втората и третата поява на интервал.

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

    формула за разделяне на собствено име, бащино име и фамилно име

    Използване на вложени функции SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки от първия знак отляво. (5)

  5. Добавяне на 1 за получаване на знака след първия интервал (K). Резултатът е номерът на знака, от който искате да започне търсенето на втория интервал(5 + 1 = 6).

  6. Търсене на позицията на втората поява на интервал в A2, започвайки от шестата позиция (K), намерена на стъпка 2. Резултатът е броят на знаците, които LEFT извлича отляво (9).

  7. Добавяне на 1 за получаване на знака след втория интервал (D). Резултатът е началната позиция на бащиното име(9 + 1 = 10).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на знака след втория интервал (D). Резултатът е номера на знака, от който искате да започне търсенето на третия интервал (10).

  8. Търсене на числовата позиция на третия интервал в A2, започвайки отляво. Резултатът е последната позиция на бащиното име (12).

  9. Търсене на числовата позиция на знака след втория интервал (D). Резултатът е началната позиция на бащиното име (10).

  10. Взема номера на знака на третия интервал, намерен на стъпка 6 и изважда номера на "D", намерен на стъпка 7. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от десетата позиция, намерена на стъпка 4(12 – 10 = 2).

  11. Фамилно име

    Фамилното име започва на осем знака отдясно. Тази формула използва вложени SEARCH, за да намери първата, втората и третата поява на интервал в петата, деветата и дванайсетата.

    Формулата извлича осем знака отдясно.

    Формула за разделяне на собствено име, бащино име и фамилно име

    Използване на функцията LEN и вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (5)

  12. Добавяне на 1 за получаване на знака след първия интервал (K). Резултатът е номерът на знака, от който искате да започне търсенето на интервала(5 + 1 = 6).

  13. Търсене на втория интервал в A2, започвайки от шестата позиция (K), намерена на стъпка 2. (9)

  14. Добавяне на 1, за да се получи позицията на знака след втория интервал (D). Резултатът е началната позиция на бащиното име(9 + 1 = 10).

  15. Търсене на числовата позиция на третия интервал в A2, започвайки отляво. Резултатът е последната позиция на бащиното име (12).

  16. Пресмятане на общата дължина на текстовия низ в A2, с последващо изваждане на броя на знаците отляво на третия интервал, намерен на стъпка 5. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(20 – 12 = 8).

Този пример използва фамилно име от три части: Barreto de Mattos. Първият интервал отбелязва края на собственото име и началото на фамилното име.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Paula Barreto de Mattos

Фамилно име с три части

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (фамилно име)

RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Собствено име

    Собственото име започва с първия знак отляво (P) и завършва на шестия знак (първият интервал). Формулата извлича първите шест знака отляво.

    Формула за разделяне на собствено име и фамилно име от три части

    Използване на функцията Search за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  2. Фамилно име

    Фамилното име започва на седемнадесет знака отдясно (B) и завършва с първия знак отдясно (s). Формулата извлича седемнадесет знака отдясно.

    Формула за разделяне на собствено име и фамилно име от три части

    Използване на функциите LEN и SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  3. Пресмятане на общата дължина на текстовия низ в A2 с последващо изваждане на броя на знаците отляво на първия интервал, намерен на стъпка 1. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(23 – 6 = 17).

Този пример използва фамилно име от две части: van Eaton. Първият интервал отбелязва края на собственото име и началото на фамилното име.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

James van Eaton

Фамилно име с две части

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Собствено име

    Собственото име започва с първия знак отляво (J) и завършва на осмия знак (първият интервал). Формулата извлича първите шест знака отляво.

    Формула за разделяне на собствено име и фамилно име от две части

    Използване на функцията SEARCH за намиране на стойността за "брой_знаци"

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  2. Фамилно име

    Фамилното име започва с деветия знак отдясно (v) и завършва с първия знак отдясно (n). Формулата извлича девет знака отдясно в пълното име.

    Формула за разделяне на собствено име и фамилно име от две части

    Използване на функциите LEN и SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  3. Пресмятане на общата дължина на текстовия низ в A2, с последващо изваждане на броя на знаците отляво на първия интервал, намерен на стъпка 1. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(15 – 6 = 9).

В този пример фамилното име е първо, следвано от суфикс. Фамилното име и суфиксът са разделени от собственото име и инициала на бащино име със запетая.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Bacon Jr., Dan K.

Първо фамилното име и суфикс, със запетая

Формула

Резултат (собствено име)

'=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=MID(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1;SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

Формула

Резултат (инициал за бащино име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)+1))

Формула

Резултат (фамилно име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (суфикс)

'=MID(A2;SEARCH(" "; A2;1)+1;(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-2)-SEARCH(" ";A2;1))

=MID(A2;SEARCH(" "; A2;1)+1;(SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-2)-SEARCH(" ";A2;1))

  1. Собствено име

    Собственото име започва от дванадесетия знак (D) и завършва на петнадесетия (третият интервал). Формулата извлича три знака, започвайки от дванадесетата позиция.

    Формула за разделяне на фамилно име и собствено име със суфикс, със запетая

    Използване на вложени функции SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  2. Добавяне на 1 за получаване на знака след първия интервал (J). Резултатът е номерът на знака, от който искате да започне търсенето на втория интервал(6 + 1 = 7).

  3. Търсене на втория интервал в A2, започвайки от седмата позиция (J), намерена на стъпка 2. (11)

  4. Добавяне на 1 за получаване на знака след втория интервал (D). Резултатът е началната позиция на собственото име(11 + 1 = 12).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на знака след втория интервал (D). Резултатът е номерът на знака, от който искате да започне търсенето на третия интервал (12).

  5. Търсене на числовата позиция на третия интервал в A2, започвайки отляво. Резултатът е последната позиция на собственото име (15).

  6. Търсене на числовата позиция на знака след втория интервал (D). Резултатът е началната позиция на собственото име (12).

  7. Взема номера на знака на третия интервал, намерен на стъпка 6, и изважда номера на "D", намерен на стъпка 7. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от дванадесетата позиция, намерена на стъпка 4(15 – 12 = 3).

  8. Бащино име

    Бащиното име започва с втория знак отдясно (K). Формулата извлича два знака отдясно.

    Формула за разделяне на фамилно име и собствено име със суфикс, със запетая

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  9. Добавяне на 1 за получаване на знака след първия интервал (J). Резултатът е номерът на знака, от който искате да започне търсенето на втория интервал(6 + 1 = 7).

  10. Търсене на втория интервал в A2, започвайки от седмата позиция (J), намерена на стъпка 2. (11)

  11. Добавяне на 1 за получаване на знака след втория интервал (D). Резултатът е началната позиция на собственото име(11 + 1 = 12).

  12. Търсене на числовата позиция на третия интервал в A2, започвайки отляво. Резултатът е последната позиция на бащиното име (15).

  13. Пресмятане на общата дължина на текстовия низ в A2 с последващо изваждане на броя на знаците отляво на третия интервал, намерен на стъпка 5. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(17 – 15 = 2).

  14. Фамилно име

    Фамилното име започва с първия знак отляво (B) и завършва на шестия знак (първият интервал). Следователно формулата извлича първите шест знака отляво.

    Формула за разделяне на фамилно име и собствено име със суфикс, със запетая

    Използване на функцията SEARCH за намиране на стойността за num_chars

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  15. Суфикс

    Суфиксът започва със седмия знак отляво (J) и завършва на деветия знак отляво (.). Формулата извлича шест знака, започвайки от седмия знак.

    Формула за разделяне на фамилно име и собствено име със суфикс, със запетая

    Използване на функцията SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  16. Добавяне на 1 за получаване на знака след първия интервал (J). Резултатът е началната позиция на суфикса(6 + 1 = 7).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  17. Добавяне на 1 за получаване на числовата позицията на знака след първия интервал (J). Резултатът е номерът на знака, от който започва търсенето на втория интервал (7).

  18. Търсене на втория интервал в A2, започвайки от седмия знак, намерен на стъпка 4. (11)

  19. Изважда 1 от номера на знака на втория интервал, намерен на стъпка 4, за да получи номера на знака “,”. Резултатът е завършващата позиция на суфикса(11 – 1 = 10).

  20. Търсене на числовата позиция на първия интервал (6).

  21. След намиране на първия интервал, добавете 1, за да намерите следващия знак (J), намерен също в стъпки 3 и 4 (7).

  22. Взема номера на знака на “,” намерен на стъпка 6 и изважда номера на знака на “J”, намерен в стъпки 3 и 4. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от седмата позиция, намерена на стъпка 2(10 – 7 = 3).

В този пример собственото име е в началото на низа, а суфиксът е в края, така че можете да използвате формули, подобни на пример 2: Използвайте функцията LEFT за извличане на собственото име, функцията MID за извличане на фамилното име и функцията RIGHT за извличане на суфикса.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Gary Altman III

Собствено име и фамилно име със суфикс

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (фамилно име)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";A2;1)+1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";A2;1)+1))

Формула

Резултат (суфикс)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

  1. Собствено име

    Собственото име започва с първия знак отляво (G) и завършва на петия знак (първият интервал). Следователно формулата извлича първите пет знака отляво.

    Формула за разделяне на собствено и фамилно име, следвано от суфикс

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (5)

  2. Фамилно име

    Фамилното име започва от шестия знак отляво (G) и завършва на единадесетия знак (вторият интервал). Тази формула използва вложени SEARCH, за да намери позициите на интервалите.

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

    Формула за разделяне на собствено и фамилно име, следвано от суфикс

    Използване на функцията SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (5)

  3. Добавяне на 1, за да се получи позицията на знака след първия интервал (A). Резултатът е началната позиция на фамилното име(5 + 1 = 6).

    Използване на вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (5)

  4. Добавяне на 1 за получаване на позицията на знака след първия интервал (A). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(5 + 1 = 6).

  5. Търсене на числовата позиция на втория интервал в A2, започвайки от шестия знак, намерен на стъпка 4. Този номер на знак е крайната позиция на фамилното име (12).

  6. Търсене на числовата позиция на първия интервал (5).

  7. Добавете 1, за да намерите числовата позиция на знака след първия интервал (A), намерена също в стъпки 3 и 4 (6).

  8. Взема номера на знака на втория интервал, намерен на стъпка 5 и изважда номера на знака “A”, намерен в стъпки 6 и 7. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от шестата позиция, намерена на стъпка 2(12 – 6 = 6).

  9. Суфикс

    Суфиксът започва на три знака отдясно. Тази формула използва вложени SEARCH, за да намери позициите на интервалите.

    Формула за разделяне на собствено и фамилно име, следвано от суфикс

    Използване на функцията LEN и вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (5)

  10. Добавяне на 1 за получаване на знака след първия интервал (A). Резултатът е номерът на знака, от който искате да започне търсенето на втория интервал(5 + 1 = 6).

  11. Търсене на втория интервал в A2, започвайки от шестата позиция (A), намерена на стъпка 2. (12)

  12. Пресмятане на общата дължина на текстовия низ в A2, с последващо изваждане на броя на знаците отляво на втория интервал, намерен на стъпка 3. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(15 – 12 = 3).

В този пример пълното име се предшества от суфикс и използвате формули, подобни на пример 2: функция MID за извличане на собственото име, функция RIGHT за извличане на фамилното име.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Mr. Ryan Ihrig

С префикс

Формула

Резултат (собствено име)

'=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";A2;1)+1))

=MID(A2;SEARCH(" ";A2;1)+1;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)-(SEARCH(" ";A2;1)+1))

Формула

Резултат (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2;1)+1))

  1. Собствено име

    Собственото име започва от петия знак отляво (R) и завършва на деветия знак (вторият интервал). Формулата влага SEARCH, за да намери позициите на интервалите. Тя извлича четири знака от петата позиция.

    Формула за разделяне на собствено име, предхождано от префикс

    Използване на функцията SEARCH за намиране на стойността за start_num:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (4)

  2. Добавяне на 1, за да се получи позицията на знака след първия интервал (R). Резултатът е началната позиция на собственото име(4 + 1 = 5).

    Използване на вложена функция SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (4)

  3. Добавяне на 1 за получаване на позицията на знака след първия интервал (R). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(4 + 1 = 5).

  4. Търсене на числовата позиция на втория интервал в A2, започвайки от петия знак, намерен в стъпки 3 и 4. Този номер на знак е крайната позиция на собственото име (9).

  5. Търсене на първия интервал (4).

  6. Добавете 1, за да намерите числовата позиция на знака след първия интервал (R), намерена също в стъпки 3 и 4 (5).

  7. Взема номера на знака на втория интервал, намерен на стъпка 5 и изважда номера на знака “R”, намерен в стъпки 6 и 7. Резултатът е броят на знаците, които MID извлича от текстовия низ, започвайки от петата позиция, намерена на стъпка 2(9 – 5 = 4).

  8. Фамилно име

    Фамилното име започва на пет знака отдясно. Тази формула използва вложена SEARCH, за да намери позициите на интервалите.

    Формула за разделяне на собствено име, предхождано от префикс

    Използване на функцията LEN и вложени функции SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (4)

  9. Добавяне на 1 за получаване на позицията на знака след първия интервал (R). Резултатът е номерът на знака, от който започва търсенето на втората поява на интервал(4 + 1 = 5).

  10. Търсене на втория интервал в A2, започвайки от петата позиция (R), намерена на стъпка 2. (9)

  11. Пресмятане на общата дължина на текстовия низ в A2, с последващо изваждане на броя на знаците отляво на втория интервал, намерен на стъпка 3. Резултатът е броят на знаците, които да се извлекат отдясно на пълното име(14 – 9 = 5).

Този пример използва фамилно име с тире. Компонентите на името са разделени с интервали.

Копирайте клетките в таблицата и поставете в работен лист на Excel в клетка A1. Формулата, която виждате отляво, ще се покаже за справка, а Excel автоматично ще преобразува формулата отдясно в съответния резултат.

Съвет    Преди да поставите данните в работния лист, задайте ширините на колони A и B на 250.

Примерно име

Описание

Julie Taft-Rider

Фамилно име с тире

Формула

Резултат (собствено име)

'=LEFT(A2; SEARCH(" ";A2;1))

=LEFT(A2; SEARCH(" ";A2;1))

Формула

Резултат (фамилно име)

'=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;1))

  1. Собствено име

    Собственото име започва с първия знак отляво и завършва на шестата позиция (първият интервал). Формулата извлича първите шест знака отляво.

    Формула за разделяне на собствено име и фамилно име с тире

    Използване на функцията SEARCH за намиране на стойността на num_chars:

    Търсене на числовата позиция на първия интервал в A2, започвайки отляво. (6)

  2. Фамилно име

    Цялото фамилно име започва десет знака отдясно (T) и завършва с първия знак отдясно (r).

    Формула за разделяне на собствено име и фамилно име с тире

    Използване на функциите LEN и SEARCH за намиране на стойността за num_chars:

    Търсене на числовата позиция на интервала в A2, започвайки от първия знак отляво. (6)

  3. Пресмятане на общата дължина на извличания текстовия низ с последващо изваждане на броя на знаците отляво до първия интервал, както е намерено на стъпка 1(16 – 6 = 10).

Вж. също

Разделяне на текст в различни колони със съветника за преобразуване на текст в колони

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×