Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як відокремити текст і цифри з однієї комірки на дві колонки?

Якщо у вас є стовпець текстових рядків, які складаються з тексту та цифр, тепер ви хотіли б розділити текст та числа з однієї комірки у дві різні комірки, як показано на наступному знімку екрана. У Excel ви можете виконати це завдання цими методами.


Спосіб 1: Розділіть текст та цифри формулами в Excel

За допомогою наступних формул ви можете витягти текст та числа з комірки у дві відокремлені комірки. Будь ласка, виконайте наступне:

1. Введіть цю формулу в порожню комірку - С3, куди ви хочете помістити результат: =LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))-1), (A3 - комірка, яка містить текстовий рядок, який потрібно відокремити), а потім натисніть вводити , щоб отримати лише текст із комірки А2. Дивіться знімок екрана:

doc розділений текст номер 2

2. А потім ви можете витягти числа з комірки, застосувавши цю формулу: =RIGHT(A3,LEN(A3)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1),( A3 - це комірка, що містить текстовий рядок, який ви хочете відокремити), введіть цю формулу в клітинку D3, де ви хочете розмістити цифри, та натисніть вводити клавіша, тоді ви отримаєте цифри так:

doc розділений текст номер 3

3. Потім виділіть клітинку C3: D3 і перетягніть маркер заповнення до комірок, в які ви хочете містити ці формули, і ви побачите, що текст і числа були розділені на різні комірки:

doc розділений текст номер 4


Розділіть або розділіть текстові рядки на окремі текстові та числові стовпці:

Kutools для Excel's Розділені клітини функція - це потужний інструмент, вона може допомогти вам розділити значення комірок на кілька стовпців або рядків, а також допоможе вам розрізати буквено-цифрові рядки на окремі стовпці тексту та цифр тощо ... Клацніть, щоб завантажити Kutools для Excel!

doc розділений текст номер 16

Спосіб 2: Відокремте текст та цифри за допомогою Flash Fill у програмі Excel 2013 та пізнішої версії

За допомогою наведених вище формул ви просто можете розділити текст і цифри, якщо текст перед цифрами. Щоб відокремити текстовий рядок, номер якого стоїть перед текстом, ви можете використовувати Флеш-заповнення особливість Excel 2013 та пізнішої версії.

Якщо у вас Excel 2013 і пізнішої версії, Флеш-заповнення Ця функція може допомогти вам заповнити текст в одному стовпці, а цифри в іншому стовпці. Будь ласка, зробіть так:

1. Введіть номери першого текстового рядка повністю в сусідню порожню комірку - B3, див. Знімок екрана:

doc розділений текст номер 5

2. А потім виберіть діапазон B3: B7, де ви хочете заповнити цифри, і натисніть дані > Флеш-заповнення, і в клітинки одразу були заповнені лише цифри, див. знімок екрана:

doc розділений текст номер 6

3. Потім введіть текстовий рядок повністю в клітинку C3, див. Знімок екрана:

doc розділений текст номер 7

4. І виберіть діапазон комірок C3: C7, де ви хочете заповнити лише текст, натисніть дані > Флеш-заповнення а також крок 2, і ви бачите, текст було розділено наступним чином:

doc розділений текст номер 8

Чайові: Ви також можете перетягнути заповнений маркер до діапазону, який потрібно використовувати, а потім клацнути Варіант автоматичного заповнення і перевірте Флеш-заповнення.

doc розділений текст номер 9

Спосіб 3: Відокремте текст та цифри, які змішуються нерегулярно, із користувацькою функцією

Якщо у вас є ряд текстових рядків, які змішують текст і цифри нерегулярно, як показано наведених нижче даних, Excel не підтримує загальну функцію для вирішення цієї проблеми, але для цього можна створити визначену користувачем функцію.

doc розділений текст номер 17

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Вікно Microsoft Visual Basic для програм.

2. Клацання Insert > Модуліта вставте наступний код у Вікно модуля.

Код VBA: відокремте текст і цифри в різні комірки з однієї комірки

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Потім збережіть і закрийте цей код, поверніться до аркуша, введіть цю формулу = SplitText (A3, FALSE) у порожню комірку, щоб отримати єдиний текстовий рядок, а потім перетягніть маркер заповнення вниз до комірок, які потрібно заповнити цією формулою, див. знімок екрана:

doc розділений текст номер 10

4. І ще, формула типу = SplitText (A3, TRUE) в іншу комірку і перетягніть маркер заповнення вниз до комірок, які ви хочете заповнити цією формулою, щоб отримати цифри, див. знімок екрана:

doc розділений текст номер 11

примітки: Результат буде неправильним, якщо в текстовому рядку є десяткові числа.


Метод 4: Розділіть текст і цифри на два стовпці за допомогою Kutools для Excel

Якщо у вас є Kutools для Excel, з його потужним інструментом- Розділені клітини утиліта, ви можете швидко розділити текстові рядки на два стовпці: один - це число, а інший - текст.

після установки Kutools для Excel, будь ласка, виконайте наступне:

1. Виберіть діапазон даних, для якого потрібно розділити текст та номер.

2. Потім натисніть Кутулс > Злиття та розділення > Розділені клітини, див. скріншот:

3, в Розділені клітини діалогове вікно, виберіть Розділити на стовпці опція під тип розділ, а потім перевірте Текст і номер від Розділено на розділ, див. знімок екрана:

doc розділений текст номер 13

4. Потім натисніть Ok , і з'явиться підказка з нагадуванням вибрати клітинку для виведення результату, див. знімок екрана:

doc розділений текст номер 14

5. Клацання OK , а текстові рядки у виділенні розділено на два стовпці, як показано на наступному знімку екрана:

doc розділений текст номер 15

Натисніть Завантажити Kutools для Excel та безкоштовну пробну версію зараз!


Відокремте текст та цифри за допомогою Kutools для Excel

Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!

Статті по темі:

Як розділити слово на окремі літери в Excel?

Як розділити комірки на кілька стовпців або рядків шляхом повернення каретки?


Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Aaron Milner · 1 months ago
    Agile (11/20/2017 12:00:00 AM)
    How can I separate this cell into just "Agile" and "(11/20/2017 12:00:00 AM)"
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hello, Milner,
      To get the name text, please use this formula: =TRIM(LEFT(A1, FIND("(", A1)-1))
      To get the date time, please apply this formula: =MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1)
      Please try the formulas, hope them can help you!

  • To post as a guest, your comment is unpublished.
    Sally · 5 months ago
    I have a requirement code that I need to separate out from the text, looks like this 3.1.1.2. Line-of-sight Range Requirements (T=O) The vehicle and units shall be capable of line-of-sight connectivity to all nodes or within a 10-mile radius, whichever is greater. In one column I need the code and the other I need the text. Can you help? 
  • To post as a guest, your comment is unpublished.
    Mohan kumar · 7 months ago
    I have address like 12,anna street,98413256789 i want to seperate address and contact number(mobile or landline) two columns. Is it possible.
    Pls help to solve
    • To post as a guest, your comment is unpublished.
      skyyang · 6 months ago
      Hi, Mohan,
      To seperate address and contact number, please apply the below formulas:
      Address: =LEFT(A1,LEN(A1)-12)
      Mobile number: =RIGHT(A1,11)

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Kenny · 1 years ago
    Please, help me with the VBA code required to take out "INC000010542805" out of "User KSmith Audit ID INC000010542805 Comment None Control Data".
    I have 1,000 rows of this type of data with different number of characters but always with that "INC" string.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Kenny,
      May be the below formula can solve your problem, please try:
      =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("INC",A1),LEN(A1))," ",REPT(" ",100)),100))
      • To post as a guest, your comment is unpublished.
        KennyO · 1 years ago
        @skyyang thanks very much, it works. I really appreciate your help
  • To post as a guest, your comment is unpublished.
    usskp2003@gmail.com · 2 years ago
    how to separate number and letters (1122AB). I tried the upper formula but its not working with me. anybody help me in this regard. Thanks in advance
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Naeem,
      The above formula only works if the text is before the numbers, your numbers before the text, so i recommend the second and third method for you!
      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Hay · 3 years ago
    kereeeennn... berhasil....
  • To post as a guest, your comment is unpublished.
    AM · 3 years ago
    Thanks worked for Alpha-numeric cell data [ =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)]
  • To post as a guest, your comment is unpublished.
    Gowtam Roopun · 3 years ago
    Hello Can anyone help me? I want to remove this zero and braceket by an excel formula.
    1 BR PE-0.50CT(H SI2)- -0.00( )-2 SP PE-0.50CT(H SI1)-1 RU PE-0.40CT(H-SI)-750GF-RG-RING-25-40-2.50GM


    Gowtam
  • To post as a guest, your comment is unpublished.
    RAJ · 3 years ago
    VERY USEFUL FORMULA I LIKE IT.
  • To post as a guest, your comment is unpublished.
    Sarashwaty · 3 years ago
    Hi.... Thank you for sharing, it is very helpful and save lots of time for me.
    Regards,
    Saras.
  • To post as a guest, your comment is unpublished.
    saharsh0101@gmail.com · 3 years ago
    Hi,
    I'm trying to split these values HarryJack 22 3,66 335,77 44,77 into two columns which I can only have text in one column and 22 in the second column.
  • To post as a guest, your comment is unpublished.
    yuvraj · 3 years ago
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11247, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11248, 11249, 11250, 11322, 11323, 11324, 11325, 11326, 11332, 11337
    11248, 11322, 11323, 11324, 11325, 11326, 11332, 11337 how to separate above numbers in different cell with same number
    • To post as a guest, your comment is unpublished.
      S · 3 years ago
      Text to coloum with , separated format.
  • To post as a guest, your comment is unpublished.
    sagar chutiya · 4 years ago
    laure ka baal ,madarjaat
    • To post as a guest, your comment is unpublished.
      Baba · 2 years ago
      Mind your language sagar ch*tiya...

      This is a professional site and professional members.


      If your don't control your abusive and vulgar language then I will just cut-off your tongue.


      Thank You!!!
  • To post as a guest, your comment is unpublished.
    Bhavesh Desai · 4 years ago
    Super useful. Thank you very much. You saved my time!
  • To post as a guest, your comment is unpublished.
    vemky · 5 years ago
    20161021-014340_3125013233_OUTUS_agent012-all.mp3


    how to sperate this all in diffrent colums
  • To post as a guest, your comment is unpublished.
    Raju · 5 years ago
    can you help my in this question (SEONI-MALWA734274SEONIMALWA ) i want to split only number by formula . I tried many time but i split only 734274SEONIMALWA
    so if you have any formula then help me
  • To post as a guest, your comment is unpublished.
    AARON GABRIEL · 5 years ago
    Hey,
    I want to split this cell which contains (TI_122006001550)
    Now my aim is to omit the Everything else on that and keep only 122006
    Please help me generate a formula for this
    Would be great
    Thanks
    • To post as a guest, your comment is unpublished.
      Sunil Pinto · 1 years ago
      =(RIGHT(LEFT("X",10),6))

      "X" Replace the 'X' with your data

  • To post as a guest, your comment is unpublished.
    shaker · 5 years ago
    Thanks for your valuable info.if it is possible can plz explain it briefly.
    This is the data like which i have in E column i want only number whether its starting or middle or last i want numeric number.can you provide code this kind of data
    Appreciate your help
    [b]BILL ID :AHM CLG 150236 SBI
    REDDY AGENCIES \HDF \000349
    DINAJPUR BEEJ \AXI \055313[/b]



    Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
    Dim xLen As Long
    Dim xStr As String
    xLen = VBA.Len(pWorkRng.Value)
    For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitText = SplitText + xStr
    End If
    Next
    End Function
  • To post as a guest, your comment is unpublished.
    sunny · 5 years ago
    thanks a lot. was very helpful. saved lots of time
    • To post as a guest, your comment is unpublished.
      Sunil Pinto · 1 years ago
      I have two text data in two cells, Example "Ramesh" & "Rajesh" and A1 and A2 cells in excel respectively, how I can extract the similar character to B2 Cells (Answer is "Raesh")