WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to separate numbers from text in Excel

August 1, 2022
3.6K Views

Spreadsheets can contain both text and numbers for a variety of reasons. The benefits of separating this content are numerous, and it can improve your capacity for information organization. Here are a few reasons why you might want to divide text and numbers in an Excel document. In Excel, separating text from numbers in different columns or rows makes filtering, sorting, and searching easier. You can sort columns with only numerical data and filter columns with text using functions and tools.

There are numerous ways by which we can easily separate numbers from text in excel.

Firstly, we will separate numbers from text in excel using flash fill method.

This is  a function by which we can separate numbers from text using the flash fill method, this function does not have the commas in the combined data like this.

This need to perform some steps.

1) First we will manually separate the data like I put JOE ROOT in cell C4 and 1960 in cell D4.

2) Then select the rest of the cells in the first column. I select all cells from cells C4 to C7. Then go to data you find the fill option and when you press it an option will pop up, click on flash fill, your all the student’s names will appear in C4 to C7 cells.

3) Same step to follow for Student ID numbers where we can easily separate numbers from text in a new column easily like this.

You simply separate numbers from text in excel using the flash fill method by following all the above guidelines.

How to separate numbers from excel using TEXT to COLUMN function.

1) This function helps to separate text and numbers independently and it improves to look better. First, we have to make data in which we make combine data in a spreadsheet including students’ names and their IDs.

2) Then go to the DATA option and there you find TEXT TO COLUMNS.

3) When you press on the text to column a dialogue box will appear but before clicking on the text to a column, select the data. A dialogue box will show 2 options click on delimited and below the selected text is also showing and then press next, again new options will appear on screen in the same dialogue box, mark comma box and then press next again, again you require to fill mark in the general box and in the destination box use =$C$4, then after clicking on finish automatically your text and student ID separately appear on the screen.

Congratulations you again successfully found how to separate numbers from text in excel easily by all these steps.

How to separate numbers from text in Excel using the FILL HANDLE function.

This is a third function that we use to separate numbers from text, this is also a very easy process.

1) First we take database as similar as above and manually place the student name and student ID in their respective boxes.

2) In The second step, at that point, drag the Fill Handle of the initial section through the other cells. You will get a little symbol called Auto Fill Options in the base right corner in the wake of moving. After that Click the drop-down menu associated with it. You will get four options. Click on Flash Fill, and your student’s ID will appear in their boxes.

Congratulation to you again for finding how to separate numbers from text in excel using the fill handle method.

You can also find this feature in the latest excel versions 2016/2018/2019 spreadsheet, where you can easily find how to separate numbers from text in excel.

Did you learn about how to find how to separate numbers from text in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.