Extracting Numbers From a Cell - Excel Macro (VBA)

SurfShark · Apr 14, 2013

Hello all, this is my first post!

[FONT=&quot]I need to extract every day or an eight digit number that always starts with "32......", or an eight digits number that always start with "5.......", or a seven digits number that always start with "1......", or a nine digit number that always start with"9........"

The problem I am having is that the description is always different.
I have created a table with some examples:[/FONT]

FACTURA 51388829 - TEL 951111330 JON NAVARRO TESTA IMP. TRANSF.

FRA 51333335 N TSA120/01300001 ANA MONTANA S.L. IMP. TRANSF. 32888874

1201333765/918422223 MARIA PEREZ IMP. TRANSF.

JOSEFA RODR CAB . N FACT 51693338 Y 51253333 .SOCIO 12 PROV 01586086 NOTPROVIDED 75840606S

[FONT=&quot]I have already created a VBA cod for the Macro although, because it was not initially created for this and modified it does not work properly.

I am using Excel 2010[/FONT]

JoeMo · Apr 14, 2013

What result do you want the macro to return for each example you posted?

SurfShark · Apr 14, 2013

With the Macro return for the above sample I would want to have:

1- ...51388829 (on column B) and 951111330 (on column C)
2- ...51333335 (on column B), 1300001 (on column C), 32888874 (on column D)
3- ...1333765 (on column B) and 918422223 (on column C)
4- ...51693338 (on column B), 51253333 (on column C)and 1586086 (on column D)

The first field (column) is the description

JoeMo · Apr 14, 2013

SurfShark said:
With the Macro return for the above sample I would want to have:

1- ...51388829 (on column B) and 951111330 (on column C)
2- ...51333335 (on column B), 1300001 (on column C), 32888874 (on column D)
3- ...1333765 (on column B) and 918422223 (on column C)
4- ...51693338 (on column B), 51253333 (on column C)and 1586086 (on column D)

The first field (column) is the description

For example 3, it's not clear to me how 1333765 can be extracted from 1201333765 based on the "rules" you posted (7-digit number that always starts with 1). How would one determine that you only want the last 7 digits of the number 1201333765?

SurfShark · Apr 14, 2013

My apologies, I haven´t explain the issue I sometimes have with the number 7-digit number that always starts with 1. That number sometimes has on the beginning the cod "120" and I always have to remove it manually.

Rick Rothstein · Apr 14, 2013

SurfShark said:
My apologies, I haven´t explain the issue I sometimes have with the number 7-digit number that always starts with 1. That number sometimes has on the beginning the cod "120" and I always have to remove it manually.

See if this macro does what you want...

Code:

Sub GetSpecificNumbersFromDescription()
  Dim X As Long, Z As Long, CellVal As String, Cell As Range, Numbers() As String
  Columns("B:E").ClearContents
  For Each Cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    CellVal = Cell.Value
    For X = 1 To Len(CellVal)
      If Mid(CellVal, X, 1) Like "[!0-9]" Then Mid(CellVal, X, 1) = " "
    Next
    CellVal = WorksheetFunction.Trim(CellVal)
    Numbers = Split(CellVal)
    For X = 0 To UBound(Numbers)
      If Numbers(X) Like "1201######" Then Numbers(X) = Mid(Numbers(X), 4)
      Numbers(X) = Val(Numbers(X))
      If Not (Numbers(X) Like "32######" Or Numbers(X) Like "5#######" Or Numbers(X) _
                 Like "1######" Or Numbers(X) Like "9########") Then Numbers(X) = ""
    Next
    Numbers = Split(WorksheetFunction.Trim(Join(Numbers)))
    Cell.Offset(, 1).Resize(, UBound(Numbers) + 1) = Numbers
  Next
End Sub

JoeMo · Apr 14, 2013

SurfShark said:
My apologies, I haven´t explain the issue I sometimes have with the number 7-digit number that always starts with 1. That number sometimes has on the beginning the cod "120" and I always have to remove it manually.

Try this. Assumes your data begin in cell A2, adjust to suit.

Code:

Sub ExtractNumbers()
Dim lR As Long, R As Range, vA As Variant, vN As Variant, n As Long, nmax As Long

lR = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A2", "A" & lR)
vA = R.Value
Application.ScreenUpdating = False
For i = 1 To R.Rows.Count
    If InStr(vA(i, 1), "/") Then vA(i, 1) = Replace(vA(i, 1), "/", " ")
    vN = Split(vA(i, 1), " ")
    For j = LBound(vN) To UBound(vN)
        If IsNumeric(vN(j)) Then
            If Left(vN(j), 1) = 0 Then vN(j) = Right(vN(j), Len(vN(j)) - 1)
            Select Case Left(vN(j), 1)
                Case 3
                If Left(vN(j), 2) = 32 Then
                    n = n + 1
                    If Len(vN(j)) = 8 Then
                        R.Cells(i, 1).Offset(0, n).Value = vN(j)
                    Else
                        n = n - 1
                    End If
                End If
                Case 5
                n = n + 1
                If Len(vN(j)) = 8 Then
                    R.Cells(i, 1).Offset(0, n).Value = vN(j)
                Else
                    n = n - 1
                End If
                Case 1
                n = n + 1
                If Len(vN(j)) = 7 Then
                    R.Cells(i, 1).Offset(0, n).Value = vN(j)
                ElseIf Len(vN(j)) = 10 And Left(vN(j), 3) = 120 Then
                    R.Cells(i, 1).Offset(0, n).Value = Right(vN(j), 7)
                Else
                    n = n - 1
                End If
                Case 9
                n = n + 1
                If Len(vN(j)) = 9 Then
                    R.Cells(i, 1).Offset(0, n).Value = vN(j)
                Else
                    n = n - 1
                End If
            End Select
        End If
    Next j
    nmax = WorksheetFunction.Max(nmax, n)
    n = 0
Next i
Range(Cells(1, 2), Cells(1, nmax)).EntireColumn.AutoFit
End Sub

SurfShark · Apr 15, 2013

It works perfectly! I'll adjust to suit
Thank you very much

JoeMo · Apr 15, 2013

SurfShark said:
It works perfectly! I'll adjust to suit
Thank you very much

I'm curious, which code works perfectly, mine or Rick's?

SurfShark · Apr 15, 2013

yours, unfortunatly the code from Rick is giving me "Run-time error ´1004´" and I was not able to fix it
now because I have more and different scenarios I am trying to make the cod more suitable
once again thank you very much

Extracting Numbers From a Cell - Excel Macro (VBA)

SurfShark

New Member

Excel Facts

JoeMo

MrExcel MVP

SurfShark

New Member

JoeMo

MrExcel MVP

SurfShark

New Member

Rick Rothstein

MrExcel MVP

JoeMo

MrExcel MVP

SurfShark

New Member

JoeMo

MrExcel MVP

SurfShark

New Member

Similar threads

Forum statistics

Share this page

Extracting Numbers From a Cell - Excel Macro (VBA)

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock