Extract numbers between hyphens

[Mr Krabs]

Hi,
I need some help from experts since my VBA skills is not enough for this task.

I have this data looking like this "AB-123456-1". I want to copy only the numbers in between the hyphens, i.e. "123456" in this case. What makes it a bit complicated is that the number of digits may vary. Below are some examples of how the data might look:
AB-13654-4
AB-5849631-8
AB-894651-12

The data is generated in one cell only using XLOOKUP and I will assign the macro to a form control button. The copied number will be pasted manually outside of Excel.

Is there a way to do this using VBA?

Great thanks in advance!

//Mr Krabs

 

[Trebor76]

Hi Mr Krabs,

The following will put the result into a message box (assumes the text is in cell A1 of the active sheet):

MsgBox Split(Range("A1"), "-")(1)

Regards,

Robert

 

[Georgiboy]

You could also do this with formula, where is the data? Do you know how many rows?

Pseudocode:

num = Split(YourValue, "-")(1)

 

[Mr Krabs]

Hi Mr Krabs,

The following will put the result into a message box (assumes the text is in cell A1 of the active sheet):

MsgBox Split(Range("A1"), "-")(1)

Regards,

Robert

This indeed pulls only the numbers between the hyphens. It's not possible to select the text in the message box though.

 

[Mr Krabs]

You could also do this with formula, where is the data? Do you know how many rows?

Pseudocode:

num = Split(YourValue, "-")(1)

The data is located in cell E7. Is there a way to get the numbers in another cell or directly in the clipboard?

 

[Georgiboy]

You have displayed multiple values in the example, are these values all in the same cell separated by a new line?

If this is the case, how would you like to display the result - all in the same cell?

 

[Mr Krabs]

You have displayed multiple values in the example, are these values all in the same cell separated by a new line?

If this is the case, how would you like to display the result - all in the same cell?

Ah, sorry I was not clear. Those were only different examples of how the data could be structured. Cell E7 contain only one row of data.

I managed to get the function I wanted with below code (using A2 as a helper):

num = Split(Range("E7"), "-")(1)
Range("A2").Value = num
Range("A2").Copy

 

[Georgiboy]

That's great, if you had data in range E7:E?? then you could use a loop as below that would place the result in column F.

Sub test()
    Dim rng As Range, rCell As Range
    
    Set rng = Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row)
    
    For Each rCell In rng
        rCell.Offset(, 1) = Split(rCell.Value, "-")(1)
    Next rCell
End Sub

 

[Mr Krabs]

That's great, if you had data in range E7:E?? then you could use a loop as below that would place the result in column F.

Sub test()
    Dim rng As Range, rCell As Range
   
    Set rng = Range("E7:E" & Range("E" & Rows.Count).End(xlUp).Row)
   
    For Each rCell In rng
        rCell.Offset(, 1) = Split(rCell.Value, "-")(1)
    Next rCell
End Sub

Thanks a lot for the help