Extracting specific numbers from string

[JoeS01]

I have a series of numbers, each separated by a forward slash. There are never more than 2 forward slashes.

I would like any help VBA code that will extract the number immediately to the right of the first forward slash:

132/66/11 >>66
66/11 >> 11
11/0.440 >> 0.440

Any help and direction would be greatly appreciated

 

[acw]

Hi

Duplicate posting
http://www.mrexcel.com/board2/viewtopic.php?t=177488


Tony

 

[JoeS01]

Not a duplicatre posting

Tony, this posting is quite different to the earlier one.

I am after a number in a specific location here - ie., the very first number to the right of the very first slash. The subsequent character to the right may be another forwarsd slash or a blank character.

eg in a string 12/15/20, I am after the middle number 15

I hope you can please help with this

 

[acw]

Joe

My apologies - you are right. Will there always be at least 1 slash???

Tony

 

[acw]

ADVERTISEMENT

Joe

Try. Test data is in range A6:A8.

Sub ddd()
 For Each ce In Range("a6:a8")
  slashh = Len(ce) - Len(WorksheetFunction.Substitute(ce, "/", ""))
  Select Case slashh
  Case 0
   ce.Offset(0, 1) = ce
  Case 1
   ce.Offset(0, 1) = Right(ce, Len(ce) - InStr(1, ce, "/"))
  Case 2
   holder = Right(ce, Len(ce) - InStr(1, ce, "/"))
   ce.Offset(0, 1) = Left(holder, InStr(1, holder, "/") - 1)
  End Select
 Next ce
End Sub

Tony

 

[jindon]

Hi

Sub test()
Dim r As Range
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
    If InStr(r, "/") > 0 Then
        r.Offset(, 1) = Val(Mid(r, InStr(r, "/") + 1))
    End If
Next
End Sub

 

[JoeS01]

PERFECT!!

hi Tony and Jindon,
Many thanks for this - they work perfectly, and there are lots of tricks in the code for me to learn. I appreciate this, as it has saved me many hours, both now and in the future.