VBA - Find value x within a cell and get character after

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

I am trying to figure this out, and have been struggling.

I am currently using a "For - Next" loop to cycle through rows in a spreadsheet. This code looks for a keyword ("SHELF") within column "I" and I need to pull back the number (1 - 6) that is 2 characters after this word in the cell (i.e - "C:D (SHELF 1, BIN 13) / B:V:U:Z (SHELF 5, BIN 4) / FRAME WASH"). This number is not always in the same location within the cell as the location data differs.

Does anyone know a way to get that data?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Your example, "C:D (SHELF 1, BIN 13) / B:V:U:Z (SHELF 5, BIN 4) / FRAME WASH", has the word SHELF in it twice.
Do you only want the number after the first one?

Here is a little sample code that shows you how to get the single digit number after the first instance of "SHELF" in cell A1:
VBA Code:
    Dim rng As Range
    Set rng = Range("A1")
    MsgBox Mid(rng, InStr(rng, "SHELF") + 6, 1)
 
Upvote 0
Solution
Your example, "C:D (SHELF 1, BIN 13) / B:V:U:Z (SHELF 5, BIN 4) / FRAME WASH", has the word SHELF in it twice.
Do you only want the number after the first one?

Here is a little sample code that shows you how to get the single digit number after the first instance of "SHELF" in cell A1:
VBA Code:
    Dim rng As Range
    Set rng = Range("A1")
    MsgBox Mid(rng, InStr(rng, "SHELF") + 6, 1)[/CODE
[/QUOTE]

Yes, I was looking for the first instance, thanks a lot. This worked for this instance.
 
Upvote 0
You are welcome.
Glad I was able to help.
 
Last edited:
Upvote 0
Joe, just curious incase I need it in the future, but What would you use if you were looking for the second instance of the word?
 
Upvote 0
Really just posting to suggest
- that you move your excel version & platform info from your signature block to your Account details where most helpers will look for it now. Click your user name at the top right of the forum & don't forget to Save

1618320360141.png


- that you also update the part of your signature about posting code as the # symbol no longer exists there. ;)

But since you have asked more about your original question, here is a slightly different approach for both first and second instance. This lends itself a bit better to finding second, third etc instance.

VBA Code:
Dim txt As String

txt = Range("A1").Text
MsgBox Mid(Split(txt, "SHELF")(1), 2, 1)
MsgBox Mid(Split(txt, "SHELF")(2), 2, 1)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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