Extraction of multiple numbers within a long string in excel vba

Hello everyone,

i have a string that looks like this: [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]

I am trying to get every number between the "@" and "," (19, 27, 27, 27, 6, 73...) and multiply them with 0.9.

This is my approach so far but it only gets me to 19. Not sure how to get through the whole string.

Sub ExtractNumbersFromString()

    Dim str As String
    Dim FirstPos As Integer
    Dim LastPos As Integer
    Dim NumberToExtract As String
    
    str = Range("C1").Value
    FirstPos = InStr(str, "@")
    LastPos = InStr(str, ",")
    NumberToExtract = Mid(str, openPos + 1, closePos - openPos - 1)
    
End Sub

What would you guys suggest is the best way to do this?

Thank you!

Quote from rory

It's not clear to me what output you are expecting.

I would like to output the exact same string but with all numbers between "@" and "," multiplied with 0.9 rounded up or down depending on the result.

Quote from Carim

Hi and Welcome to the Forum

You could test the following code

Code

Sub TestArray()
Dim oString As String
Dim nArray
Dim i As Long
' Adjust to your own Cell
oString = Range("A1").Value
nArray = Split(Replace(oString, ",", "@"), "@")
    For i = LBound(nArray) To UBound(nArray)
      If InStr(nArray(i), "h") = 0 Then
         MsgBox Format(nArray(i) * 0.9, "#,##0.00")
      End If
    Next i
End Sub

Display More

Hope this will help

Awesome, this is pretty much exactly what i wanted. Thank you so much!

Quote from rory

You could also achieve this with a formula.

Just out of curiosity, could you give me an example for a formula that does this?

No what i would like to achieve is basically "just" this:

Input cell A:

 [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]

Output cell B:

[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected]

The string always ends without a comma. If a number is 100 or 1 no multiplication is needed.

Hey Carim, thank you so much for your help i appreciate the effort!

Unfortunately this happens when i try to execute it:

Quote from Carim

Absolutely LOVE it !!!

Would you mind attaching a sample file ...

Book1.xlsm

Quote from Carim

Just out of curiosity ...

- What is the Len(A1) ?

- Is it more than 32'767 characters ???

- What is your Cell Limit ?

- 4175 characters

- Cell Limit should be standard never changed anything

Quote from Carim

Hi,

Thanks for the sample ...

Following modification to be tested:

Code

Option Explicit
Option Base 1

Function mystring(rng As Range) As String
' Extraction of multiple numbers within a long string
' In cell B1, Type in  =mystring(A1)
Dim nArray, i As Long
nArray = Split(Replace(rng.Value, Chr(44), Chr(64)), Chr(64))
    For i = LBound(nArray) To UBound(nArray)
        If Len(nArray(i)) <= 3 Then
            If nArray(i) = 1 Or nArray(i) = 100 Then
                ' do nothing
            Else
                nArray(i) = Chr(64) & Round(nArray(i) * 0.9, 0) & Chr(44)
            End If
        End If
    Next i
    mystring = Left(Replace(Join(nArray), " ", ""), Len(Replace(Join(nArray), " ", "")) - 1)
End Function

Display More

Hope some progress has been made

Display More

Yes for sure, its working now! The rounding is already perfect as it is. Thank you so much!

Hey Carim,

hope you can help me again here.

What would be the easiest way to be able to change the multiplier "0.9" through a textbox manually? I was thinking of passing the multiplier through the function call but i dont even know if thats possible or if there is a better way.