1

I have a string of the following shape:

RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126

The numbers might be seperated by other chars than hyphens (eg spaces). I know how to differentiate them afterwards with len().

I need every string of numbers to be stored seperately (in an array for example), so that I can discriminate them with len() and then use them.

I have found how to strip the characters away from the string : How to find numbers from a string?

But it doesn't suit my problem...

Could you direct me to a function or bit of code that could help me with that?

Community
  • 1
  • 1
Chipsgoumerde
  • 115
  • 1
  • 1
  • 7

5 Answers5

2

This will run much faster than looping

Public Function NumericOnly(s As String) As String
    Dim s2 As String
    Dim replace_hyphen As String
    replace_hyphen = " "
    Static re As RegExp
    If re Is Nothing Then Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "[^0-9 -]" 'includes space, if you want to exclude space "[^0-9]"
    s2 = re.Replace(s, vbNullString)
    re.Pattern = "[^0-9 ]"
    NumericOnly = re.Replace(s2, replace_hyphen)
End Function
scott
  • 2,235
  • 1
  • 14
  • 18
  • This one returns me nothing, I'm reading about regex to understand why. – Chipsgoumerde Mar 13 '13 at 10:31
  • with this code `RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126` returns `90 92800 01097814310149012126` by typing `=numericonly(B1)` where the text string is in `B1` – scott Mar 13 '13 at 13:26
  • Thanks, that way, it works...but, the last string needs to be seperated where the hyphen is... – Chipsgoumerde Mar 13 '13 at 13:34
  • try my edit, this should create a space for the hyphen, any other characters you need to turn into spaces similar to hyphen, then add it to the `"[^0-9 -]"` list – scott Mar 13 '13 at 14:14
  • It appears it is not exactly what I need and still can't figure out the answer by myself. I still need to be able to seperate my string to an array of string so that I can process each part of it. Also I need to add others characters (such as "//" or "/") than the hyphen as a seperator, but I can't figure out how you really did and how to replacate it. – Chipsgoumerde Mar 25 '13 at 07:54
1

Try below code :

Function parseNum(strSearch As String) As String

   ' Dim strSearch As String
    'strSearch = "RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126"

    Dim i As Integer, tempVal As String
    For i = 1 To Len(strSearch)
        If IsNumeric(Mid(strSearch, i, 1)) Then
            tempVal = tempVal + Mid(strSearch, i, 1)
        End If
    Next

    parseNum = tempVal
End Function
  • @Chipsgoumerde how do you want the numbers to appear ? Do you want to add them ? –  Mar 13 '13 at 10:53
  • nop, it's not for calculation, but to search for the number in another worksheet/column. To clarify : I will check for every group of numbers : if it's 9 digits> search for it in column A / if it's 10 digits long search for it in column B – Chipsgoumerde Mar 13 '13 at 11:04
  • @Chipsgoumerde How do you want to group the numbers ? –  Mar 13 '13 at 11:08
  • 1
    RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126 => I want to have 90 / 92800 / 0109781431 / 0149012126 – Chipsgoumerde Mar 13 '13 at 12:21
0

So I realize this was a long time ago... but I was looking for a similar solution online.

Some previous history on my programming skillz (sic): I started with Python and with Python I have a handy tool called List. VBA doesn't have this, so what I'm left with is something that I can input in a variable I called sample below, i.e. sample = [1,4,5].

Back to the small code. I made it so holder would only contain groups of numbers, as how you specified they should be grouped.

Dim count, count1 As Integer
Dim holder As String
Dim sample, smallSample As String


count = 0
count1 = 1
sample = "1ab33 efa 123 adfije-23423 123124-23423"
holder = ""
Do While count <> Len(sample)
    smallSample = Left(sample, 1)
    If smallSample = "0" Or smallSample = "1" Or smallSample = "2" Or smallSample = "3" Or smallSample = "4" Or smallSample = "5" Or smallSample = "6" Or smallSample = "7" Or smallSample = "8" Or smallSample = "9" Then
        holder = holder & smallSample
    Else
        If holder <> "" Then
            Cells(count1,1) = holder
            count1 = count1 + 1
        End If
        holder = ""
    End If
    sample = Right(sample, Len(sample) - 1)

Loop

The output I got was

1

33

123

23423

123124

after I ran the code.

Community
  • 1
  • 1
Nonsingular
  • 113
  • 2
0

Great Simple Python-style loop above.

Extended for a list of strings in column A.

The numbers discovered will show in the columns to the right - B, C, etc.

Dim count, count1 As Integer
Dim holder As String
Dim sample, smallSample As String
Dim r As Integer
Dim c As Integer
r = 1
c = 1


Do While Sheet2.Cells(r, c) <> ""
    count = 0
    count1 = 1
    sample = Sheet2.Cells(r, c)
    holder = ""
    Do While count <> Len(sample)
        smallSample = Left(sample, 1)
        If smallSample = "0" Or smallSample = "1" Or smallSample = "2" Or smallSample = "3" Or smallSample = "4" Or smallSample = "5" Or smallSample = "6" Or smallSample = "7" Or smallSample = "8" Or smallSample = "9" Then
            holder = holder & smallSample
        Else
            If holder <> "" Then
                Sheets(2).Cells(r, c + count1).Value = holder
                count1 = count1 + 1
            End If
            holder = ""
        End If
        sample = Right(sample, Len(sample) - 1)
    
    Loop
    r = r + 1
Loop
Genghiz Khan
  • 11
  • 1
-1

If you are getting the Error "Issue while compiling, user defined type not defined" while using @Scotts Answer Enable the Regular Expression option as seen in Step 1 and 2 here: How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word (Works in Excel also)

P.s. Scotts Solution worked well for me.

experge
  • 1
  • 1