Reverse find formula

WWilko3

New Member
Joined
Sep 15, 2014
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I have a string of characters, typically in the range of 120 characters long. Towards the end of that list there will be a string that goes WG?G, where ? is any character. I basically want to find where in the string this WG?G string is. That's easy enough with a Search formula but occasionally I have a string of characters where that WG?G string occurs multiple times. I want to find the last occurrence of this. I thought I might be able to use a Find/Substitute solution as described here but I can't get that to work as the substitute formula doesn't work with the ? wildcard, i.e. if I try a substitute formula with WG?G it searches for a question mark rather than for 'anything' at the 3rd position in that string. Any ideas on how to work around this problem? Trying to avoid doing this without a macro. I had thought about reversing the sequencing and then searching for the first occurrence but reversing a sequence without a macro is not easy either.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post a few examples of your data & expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
If you have office 365 with the LET and FILTER functions then you could use this, without LET it will be a bit longer, without the FILTER function it is going to be far from easy (it might be possible without vba, but I wouldn't recommend it).
Excel Formula:
=LET(arr,MID(A2,SEQUENCE(LEN(A2)-3),4),LOOKUP("zzz",FILTER(arr,(LEFT(arr,2)="WG")*(RIGHT(arr,1)="G"))))

Note that this is not case sensitive in the case that any lower case occurrences of WG?G need to be ignored.
 
Upvote 0
Another option if you have 2013 or newer & you are using a PC
This is case sensitive
+Fluff 1.xlsm
AB
7Teynham WGaG Lynsted WGbaG last time WGcGWGcG
8Teynham WGaG Lynsted WGbG last WGbN time WGbGaWGbG
Main
Cell Formulas
RangeFormula
B7:B8B7=FILTERXML("<k><m>"&SUBSTITUTE(A7," ","</m><m>")&"</m></k>","//m[starts-with(.,'WG') and substring(.,4)='G'][last()]")
 
Upvote 0
One more option:

Excel Formula:
=MID(A7,AGGREGATE(14,6,SEARCH("WG?G",A7,ROW(INDIRECT("1:"&LEN(A7)))),1),4)

or

Excel Formula:
=AGGREGATE(14,6,SEARCH("WG?G",A7,ROW(INDIRECT("1:"&LEN(A7)))),1)

if you just want the location.
 
Upvote 0
Solution
Almost Jason. This tells me the specific string but not its position in the sequence, see below. Can this be tweaked to return the character number?

Example.xlsx
AB
1StringFormula
2QVQLQQWGQGLLKPSETLSLSKNQFSLKLSSVTAADTAVYYCARVINWFDPWGQGTLVTVSSWGQG
3QVQLVQSGAEVKKPGASVKVSCLRSEDTAVYYCTRWGYGFDGAMDYWGPGTLVTVSSWGPG
4
5Want to return the position of the highlighted characters
Sheet1
Cell Formulas
RangeFormula
B2:B3B2=LET(arr,MID(A2,SEQUENCE(LEN(A2)-3),4),LOOKUP("zzz",FILTER(arr,(LEFT(arr,2)="WG")*(RIGHT(arr,1)="G"))))
 
Upvote 0
Judging by post#6 you are using 365 rather than 2016 which your profile says.
 
Upvote 0
Glad we could help! :biggrin:

If you do have Excel 365, this is a tad shorter:

Excel Formula:
=AGGREGATE(14,6,SEARCH("WG?G",A7,SEQUENCE(LEN(A7))),1)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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