Note: The other languages of the website are Google-translated. Back to English
English English

How to separate text and numbers from one cell into two columns?

If you have a column of text strings which are composed of text and numbers, now, you would like to separate the text and numbers from one cell into two different cells as following screenshot shown. In Excel, you can finish this task with these methods.


Method 1: Separate text and numbers with formulas in Excel

With the following formulas, you can extract the text and numbers from the cell into two separated cells. Please do as follows:

1. Enter this formula into a blank cell – C3 where you want to place the result: =LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))-1), (A3 is the cell which contains the text string you want to separate), and then press Enter key to get only the text from the cell A2. See screenshot:

doc split text number 2

2. And then you can extract the numbers from the cell by applying this formula: =RIGHT(A3,LEN(A3)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1),( A3 is the cell which contains the text string you want to separate), enter this formula into cell D3 which you want to place the numbers, and press Enter key, then you will get the numbers as this:

doc split text number 3

3. Then select cell C3:D3, and drag the fill handle over to the cells that you want to contain these formulas, and you can see the text and numbers have been separated into different cells:

doc split text number 4


Split or Separate text strings into individual text and number columns:

Kutools for Excel's Split Cells feature is a powerful tool, it can help you to split cell values into multiple columns or rows, it also can help you to slit alphanumeric strings into separated text and numbers columns, etc… Click to download Kutools for Excel!

doc split text number 16

Method 2: Separate text and numbers with Flash Fill in Excel 2013 and later version

With the above formulas, you just can separate the text and numbers if the text is before the numbers. To separate the text string which number is before the text, you can use the Flash Fill feature of Excel 2013 and later version.

If you have Excel 2013 and later version, the Flash Fill feature may help you to fill the text in one column and numbers in another column, please do as this:

1. Type the numbers of your first text string completely into adjacent blank cell - B3, see screenshot:

doc split text number 5

2. And then select the range B3:B7 where you want to fill the numbers, and click Data > Flash Fill, and only the numbers have been filled in the cells at once, see screenshot:

doc split text number 6

3. Then enter the text string completely into cell C3, see screenshot:

doc split text number 7

4. And select the cell range C3:C7 where you want to fill the text only, click Data > Flash Fill as well as step 2, and you can see, the text has been separated as follows:

doc split text number 8

Tip: You also can drag the filled handle to the range you want to use, and then click Auto Fill Option and check Flash Fill.

doc split text number 9

Method 3: Separate text and numbers which are mixed irregular with User Defined Function

If you have some text string which are mixed text and numbers irregularly like following data shown, Excel does not support the general feature to solve this problem, but, you can create User Defined Function to finish this.

doc split text number 17

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: separate text and numbers into different cells from one cell

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. Then save and close this code, go back to the worksheet, enter this formula =SplitText(A3,FALSE) into a blank cell to get the only the text string and then drag the fill handle down to the cells that you want to fill this formula, see screenshot:

doc split text number 10

4. And thne, type formula =SplitText(A3,TRUE) into another cell and drag the fill handle down to the cells that you want to fill this formula to get the numbers, see screenshot:

doc split text number 11

Note: The result will be incorrect if there are decimal numbers in the text string.


Method 4: Separate text and numbers into two columns with Kutools for Excel

If you have Kutools for Excel, with its powerful tool- Split Cells utility, you can quickly split the text strings into two columns: one is number, and another is text.

After installing Kutools for Excel, please do as follows:

1. Select the data range that you want to separate the text and number.

2. Then click Kutools > Merge & Split > Split Cells, see screenshot:

3. In the Split Cells dialog box, select Split to Columns option under the Type section, and then check Text and number from the Split by section, see screenshot:

doc split text number 13

4. Then click Ok button, and a prompt box will pop out to remind you to select a cell to output the result, see screenshot:

doc split text number 14

5. Click OK button, and the text strings in the selection have been separated into two columns as following screenshot shown:

doc split text number 15

Click Download Kutools for Excel and free trial Now!


Separate text and numbers with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Related articles:

How to split word into separate letters in Excel?

How to split cells into multiple columns or rows by carriage return?


 

 

Comments (34)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thanks a lot. was very helpful. saved lots of time
This comment was minimized by the moderator on the site
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")
This comment was minimized by the moderator on the site
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 BILL ID :AHM CLG 150236 SBI REDDY AGENCIES \HDF \000349 DINAJPUR BEEJ \AXI \055313 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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
=(RIGHT(LEFT("X",10),6))

"X" Replace the 'X' with your data
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
20161021-014340_3125013233_OUTUS_agent012-all.mp3 how to sperate this all in diffrent colums
This comment was minimized by the moderator on the site
Super useful. Thank you very much. You saved my time!
This comment was minimized by the moderator on the site
laure ka baal ,madarjaat
This comment was minimized by the moderator on the site
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!!!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Text to coloum with , separated format.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi.... Thank you for sharing, it is very helpful and save lots of time for me.
Regards,
Saras.
This comment was minimized by the moderator on the site
VERY USEFUL FORMULA I LIKE IT.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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)]
This comment was minimized by the moderator on the site
kereeeennn... berhasil....
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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))
This comment was minimized by the moderator on the site
skyyang thanks very much, it works. I really appreciate your help
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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)"
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
0002786961 TRAK CDFA #: 0008787942 2722 2723 4536841 N/A 4345784 001018809~00077480

Above is an example of data line I need to split these into 3 types: First: starts with 2 and is of 4 digit (2722 in above example) Second: starts with 2 and is of 7 digit third; starts with 4 is of 7 digit.
I tried separating everything into different columns and then putting if and conditions that I mentioned above but the problem is not everything is getting separated and splitting everything is not efficient enough. I am not able to figure out a vba code for something that satisfies all the conditions and works too.
Can anyone help me out?
This comment was minimized by the moderator on the site
Hello, menze
Do you need to split this three parts 2722 2723 4536841 from the long data? If so, you just can use the Text to Column feature in Excel to split the content into multiple cells by space, then delete the extra data and only keep the data you need.
If not, please upload your Excel file or screenshot here for a reference.
Thank you!
This comment was minimized by the moderator on the site
Tenho uma coluna com vários endereços e os números de endereços. Conforme tabela abaixo. Como separo em uma coluna apenas os nomes e outra coluna apenas os números?

endereço
Avenida Angélica 1235
Rua José Maria Lisboa 456
Rua Celso de Azevedo Marques 307
Rua Rio Duas Barras 953
This comment was minimized by the moderator on the site
Hello, Mateus,
To extract the address number only from the address, the folloiwng formula may help you:
=SUMPRODUCT(MID(0&B3, LARGE(INDEX(ISNUMBER(--MID(B3, ROW(INDIRECT("1:"&LEN(B3))), 1)) * ROW(INDIRECT("1:"&LEN(B3))), 0), ROW(INDIRECT("1:"&LEN(B3))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B3)))/10)

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Bom dia.
Consegui extrair o número, muito obrigado!
Mas como eu consigo extrair agora apenas o endereço?
This comment was minimized by the moderator on the site
Hello, Mateus,
If you want to extract both address and address number, you can apply the Method 3 in this article:

https://www.extendoffice.com/documents/excel/2701-excel-separate-text-and-numbers.html#a3

After copying and pasting the code, please apply the below formulas:
Extract address: =SplitText(B2,FALSE)

Extract address number: =SplitText(B2,TRUE)
Please try, hope it can help you! If you have any other problem, please comment here.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations