Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Show only the numbers in a cell

Posted on 2011-03-22
Medium Priority
533 Views
Hi,

I have a column that has data like the below. I need to strip the text and just leave the number. Its not standardised in any way. Meaning its in a random place in the each cell.

OvrE LB               GaQZB/EXANQ/305840570975     SAQ
GasdON G              292045400106             SVO

Thanks
0
Question by:andybrooke
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 8

Expert Comment

ID: 35190932
0

LVL 19

Expert Comment

ID: 35190985
use
``````Sub process_contents()

For Each cel In UsedRange.Cells
contents = cel.Value
'-- find first numeric
For pos = 1 To Len(contents)
If IsNumeric(Mid(contents, pos, 1)) Then
contents = Mid(contents, pos)
Exit For
End If
Next pos
'-- find last numeric
For pos = 1 To Len(contents)
If Not IsNumeric(Mid(contents, pos, 1)) Then
contents = Left(contents, pos - 1)
Exit For
End If
Next pos

Next cel

End Sub
``````

as a start
0

LVL 24

Accepted Solution

Tracy earned 2000 total points
ID: 35191083
Try this, it will remove all characters that are not numeric:
``````Option Explicit

Function RemoveAlpha(Rng As String) As String
Dim Tmp As String
Dim i As Integer
Dim Alpha As String

Tmp = Rng
'Numbers are 48-57
For i = 1 To 255
If i >= 48 And i <= 57 Then GoTo skip
Alpha = Chr(i)
Tmp = Application.Substitute(Tmp, Alpha, "")
skip:
Next i
RemoveAlpha = Tmp
End Function
``````
Book1.xls
0

LVL 24

Expert Comment

ID: 35191104
To add a little explanation, the above is a UDF (User Defined Function) and you would enter it like any other function in Excel.  So if you're data is in A1, put this in B1:

=RemoveAlpha(A1)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month10 days, 3 hours left to enroll