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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help.
 
Last edited:

dchaney

Well-known Member
Joined
Jun 4, 2008
Messages
732
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,136,786
Messages
5,677,724
Members
419,715
Latest member
Daniellovesxl

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
Top