Extracting specific numbers from string

JoeS01

Well-known Member
Joined
Jun 25, 2005
Messages
832
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
Joe

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

Tony
 
Upvote 0
Joe

Try. Test data is in range A6:A8.
Code:
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
 
Upvote 0
Hi
Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top