In this blog post, I will explain to you how you can Separate Text and Numbers from Mixed Datasets.
Suppose that you have a dataset as appearing in the below image.
Separate Text and Numbers from Mixed Data
Here you can see that we have text values in one cell and in the next cell we have a number values.
What we want, we want to separate all text values in one column and all number values in another column.
Separate Text and Numbers from Mixed Data
We can do it manually, by copying one cell at each time.
It is ok when we have a small amount of dataset, but not possible when we have a huge amount of dataset.
Separate Text and Numbers from Mixed Data
It will take a big time to execute the task.
In this tutorial, I will share with you a super trick by applying this method, you can do the same task within a few seconds.
Now, first of all, select all the mixed datasets, see the image below:-
Separate Text and Numbers from Mixed Data
Then press ‘F5’ from the keyboard, to open a ‘Go to’ dialogue box.
See the image below:-
Now click on the ‘Special’ button appearing below in the ‘Go to’ dialogue box.
And you will get another ‘Go to Special’ dialogue box, as you can see in the image below.
Select ‘Constants’ radio buttons from the listed radio buttons.
And if you want to select number values first, then remove all the checkboxes instead of ‘Numbers’ you can see in the image above.
Similarly, if you want to select the text values first, then remove all other check boxes instead of ‘Text’.
So first we will copy the number values and in the second term, we will select the ‘Text’ values.
Once you applied the above steps select only the number values from the mixed data.
Then press the ‘Ok’ button at the bottom in the ‘Go to Special’ dialogue.
See in the above-mixed dataset, all the number values are selected with one command.
Now press ‘CTRL+C’ to copy all the number values.
Then go to
the destination area, where you want to paste this copied data.
See that all the number values are copied in one place without having any text values.
We will follow the same procedure again to copy only the text data.
Select the mixed data and press ‘F5’ from the keyboard to open the ‘Go to’ dialogue box.
Then again click on the ‘Special’ button and you will get the following image.
This time we will uncheck all the options instead of ‘Text’, see in the image.
Press ‘Ok’ and see that all the text values are selected.
Now press ‘CTRL+C’ to copy the selected data.
See this time all the text values are copied.
Go to the destination where you want to paste all the datasets.
Just press ‘CTRL+V’, and see that all the text values are pasted in the designated place.
See how easily we have separated both the number and text values into different columns.
If you want to learn more about this, please visit Microsoft Office Support.
So this way we can separate our mixed data.
I hope you find this tutorial helpful.
Please put your comments or suggestions in the comment box below.
Related Post
How to Compare Two Excel Workbooks and Highlight Differences in Excel VBA
Calculate the Difference between Two Dates Dynamically
How to Extract Data Between Two Date Ranges in Excel VBA
Combine Data from Multiple Worksheets into a Single in Excel VBA
Excel VBA Macro: – Apply Filter and Copy, Paste Data Into Another Worksheet
3 Easy Methods to Transpose Data in Excel (Step-by-Step Guide)
How to Copy Data From a Closed Workbook in Excel VBA
How to Convert Excel Files to CSV Format File
How to Create Search Box in Excel (No VBA Code Required)
VBA Macro to Dynamically Copy and Paste from One Sheet to Another
How to Lock Scroll Area in Excel with VBA (Step By Step Guide)