Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Remove Only Text From Cells that Containing Numbers and Texts in Excel?
If you've ever had to work with data in Excel where some cells mix text and numbers, you are aware of how difficult it can be to distinguish between the two. Whether you're working with survey data, financial data, or any other dataset, it's critical to appropriately clean and arrange your data. You'll have a comprehensive idea of the many strategies you might employ to successfully complete this data cleaning operation by the end of this tutorial.
Remove Only Text From Cells that Containing Numbers and Texts
Here we will create a VBA module and then select the range of cells to complete the task. So let us see a simple process to know how you can remove only text from cells that contain numbers and texts in Excel.
Step 1
Consider an Excel sheet where you have a list of strings that contain both text and numbers, similar to the below image.
First, right-click on the sheet name and select View code to open the VBA application.
Right-click > View Code.
Step 2
Then click on Insert and select Module, then copy the below code into the text box.
Insert > Module > Copy.
Code
Function OnlyNums(strWord As String)
Dim strChar As String
Dim x As Integer
Dim strTemp As String
strTemp = ""
Application.ScreenUpdating = False
For x = 1 To Len(strWord)
strChar = Mid(strWord, x, 1)
If Asc(strChar) >= 48 And _
Asc(strChar) <= 57 Then
strTemp = strTemp & strChar
End If
Next
Application.ScreenUpdating = True
OnlyNums = Val(strTemp)
End Function
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then Cancel = True
End Sub
Step 3
Then close VBA using Alt + Q. Then click on an empty cell and enter the formula as =OnlyNums(A2), click enter, and drag down using the autofill handle.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can remove only text from cells that contain numbers and texts in Excel to highlight a particular set of data.
60 Views
