=IF(ISNUMBER(SEARCH

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Hi guys,

Using a formula at the moment as:

=IF(ISNUMBER(SEARCH(H8,R24)),H8,"")

Essentially I want to search for contents of H8 in cell R24, and if found, return H8. This formula works but I would like the search to be conducted from right to left? I believe at the moment I will sarch for H8 value from left to right in cell R24...

Thanks
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,075
Office Version
2016
Platform
Windows
Do you mean that if "TOM" is found in cell H8, then it should search cell R24 for "MOT" and not "TOM"?
 

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Not quite, if TOM is found in H8, it should TOM in R24, but start the search from right to left of the cell. Because my formula will have multiple searches back to back. Aka my search criteria may be, TOM, JIM, JED.... I will be search for all three, but if in R24 the data is TOMJIMJED, I need to search for the one closest to end, ie right to left.... Thanks
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,075
Office Version
2016
Platform
Windows
Let's say you have "TOMJIMJED" in R24 and H8 is TOM. Then, irrespective of the direction of the search the match will be found and H8 i.e. TOM will be the return value.
Similarly, if you have "TOMJIMJED" in R24 and H8 is JED. Then, irrespective of the direction of the search the match will be found and H8 i.e. JED will be the return value.
Can you give an example wherein the direction of the search will influence the output?
 

30136353

Board Regular
Joined
Aug 14, 2019
Messages
55
Yes your correct, but I will be cycling through searches, and lets say TOMJIMJED is in the cell, and I am cycling through the search criteria's of TOM, TIM, JED, I need the search to find JED, as this is furthest right... If TOM is searched before jed, this will be returned but I need the furthest right result...
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,075
Office Version
2016
Platform
Windows
I would like you to give an sample of your data in cells H8 and R24 for which you think the direction will influence the output.
Or do you want to say that you have data in cell H8 and R24 where multiple names match? e.g. TOMJIMJED. So far I am guessing that H8 would a single name all the time and R24 would be multiple names.
 

Forum statistics

Threads
1,081,575
Messages
5,359,725
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top