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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,988
Office Version
  1. 365
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,908
Office Version
  1. 365
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,988
Office Version
  1. 365
Platform
  1. Windows
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()]")
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545

ADVERTISEMENT

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.
 
Solution

WWilko3

New Member
Joined
Sep 15, 2014
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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"))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,988
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Judging by post#6 you are using 365 rather than 2016 which your profile says.
 

WWilko3

New Member
Joined
Sep 15, 2014
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Eric, that works perfectly. Thank you very much!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,545
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)
 

Forum statistics

Threads
1,144,364
Messages
5,723,939
Members
422,527
Latest member
TotalBeginner201

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