Solved

Show only the numbers in a cell

Posted on 2011-03-22
4
512 Views
Last Modified: 2012-05-11
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
Comment
Question by:andybrooke
  • 2
4 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35190932
0
 
LVL 19

Expert Comment

by:akoster
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

Open in new window


as a start
0
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 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

Open in new window

Book1.xls
0
 
LVL 24

Expert Comment

by:broomee9
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now