Search for specific text and return a value to the left that begins with a certain letter

[pareid]

Hi everyone,

I have a spreadsheet that has an entry in column A, B and C as below. The formula I need in column D is to do the following:-

Search column A to see if it contains the number that is in column C. If true then I need it to bring back the word to the left that begins with the letter G or R.
The number may appear in the middle of the string as you will see. If FALSE i.e. the number in column C does not appear in column A then it should match what is in column B and do the same thing i.e. bring back the word to the left that begins with G or R.

I hope that makes sense but as an example on the data below column D would give the following answers:-

1. G3
2. RWS7
3. R5

Column A

1.G8 SIS 100111129;G3 SIS 100269008;RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN
2.G3 SIS 100111129;G3 SIS 100269008;RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN
3.G3 SIS/R5 Sk;LOGIN

In column B i have entries such as:-

1.SIS
2.SIS
3.Sk

And column C i have:-

1. 100269008
2. 100494914
3. 567938374

Thank you for any help given.

Regards

Paul

[XLent]

Hi, this might be complicated to do in single-cell calc pending real-life variations of the G/R terms being returned, coupled with lengths of source strings etc...

The below is designed to be pretty flexible, i.e. to account for more varied permutations than your 3 samples indicate.

Please Login or Register  to view this content.

Modify references to $1:$10 to be $1:$n where n is the max number of items in delimited string ... I've assumed no more than 10 semi-colon delimited items {your samples do not exceed 5}

edit: modified the FIND to include space prefix & semi-colon suffix to avoid false positive matches.

[pareid]

Wow that is some formula, thank you very much. It looks like it is working fine but I have noticed in my data that sometimes in column A "/" has crept in and then I get a #NA result or there is no semi colon at all or a "/" after the policy number.

Is there any way the formula could be adapted to allow for such anomalies?

Really appreciate your help.

Regards

Paul


e.g. column A may have

G3 SIS/R7 SW;LOGIN
or
G3 SIS (i.e. no semi colon on the end)
or
G8 SIS 100111129;G3 SIS 100269008/RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN (/ has appeared after the one number)
or
G8 SIS 100111129;G3 SIS 100269008=RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN (= has appeared after the one number)


Column A

1.G8 SIS 100111129;G3 SIS 100269008;RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN
2.G3 SIS 100111129;G3 SIS 100269008;RWS7 SIS 100494914;RWS7 SIS 100494912;LOGIN
3.G3 SIS/R5 Sk;LOGIN

[XLent]

It will be easy enough to adapt, and/or provide alternative approaches, however it would help if you could provide a sample file which illustrates a large number of permutations, with expected results. To attach a file, click "GoAdvanced" (when Replying), and attach via "Manage Attachment" link.

[Bo_Ry]

Please try at D1 and copy down.

Please Login or Register  to view this content.

If this not work, please help to upload sample file.

[pareid]

No problem I have attached a sample file.

Thanks

Paul

[pareid]

Hi ,

thanks for the reply. I get a #VALUE error with the formula so I have uploaded an example file.

Thank you for your help.

Regards

Paul

[Bo_Ry]

Please try at D2 and copy down

Please Login or Register  to view this content.

[XOR LX]

Hi,

Array formula** in D2:

=TRIM(LEFT(SUBSTITUTE(MID(A2,-LOOKUP(1,-SEARCH(B2:C2,A2))-MATCH(1,N(EXACT("R",MID(SUBSTITUTE(A2,"G","R"),-LOOKUP(1,-SEARCH(B2:C2,A2))-ROW($1:$30),1))),0),99)," ",REPT(" ",99)),99))


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Regards

[pareid]

That does seem to work, thank you so much.

I will continue to do some testing but so far so good.

Cheers

Paul

[pareid]

Hi Bo_Ry,

Sorry for the delay in getting back to you but I have been doing some further testing and I have just come across an example where it does not seem to work e.g.

RWS5 SIS;RWS4 SIS 100501564;RWS6 SIS 100501563 this gives the answer RWS5 when column C has 100501564 in it. I would expect RWS4 to be the answer for this.

I just wanted to see if you could see what might cause this.

Thank you for your help.

Regards

Paul

[Bo_Ry]

There is some flaw with that formula, Please try new one

D2

=TRIM(LEFT(SUBSTITUTE(MID(A2,MATCH(1,1/MMULT(--(MID(LEFT(A2,-LOOKUP(1,-SEARCH(B2:C2,A2))),ROW($A$1:$A$40),1)={"R","G"}),{1;1})),20)," ",REPT(" ",20)),20))

[pareid]

Thank you for coming back to me. I have tried it on a few cells and it looks good. I have just come across one that is giving a different answer. This is the example. I have put my exact column letters above for my data but with this one the number in column M does not appear in C but the 'SE' in column I does appear so it should return R5. Currently it returns G3. Sorry to be a pain with this and thanks again for spending the time to help me.

Regards

Paul

C
G3 SL/R5 SE:LOGIN

I
SE

M
9085724

[Bo_Ry]

It show R5 here, maybe upload you file and see.

[pareid]

Hi
It seems it is because my columns with the data in are not A,B and C but C, I and M. If I put an extra column in between B and C on your spreadsheet it gives the G3 answer. Is there a way of modifying the formula to account for this?

Thanks again

Regards

Paul

[Bo_Ry]

Try this for Row2

=TRIM(LEFT(SUBSTITUTE(MID(C2,MATCH(1,1/MMULT(--(MID(LEFT(C2,-LOOKUP(1,-SEARCH(IF({1,0},I2,M2),C2))),ROW($A$1:$A$40),1)={"R","G"}),{1;1})),20)," ",REPT(" ",20)),20))

[pareid]

Hi,

That seems to correct the last problem but I have got to this and it gives the answer G2.

C
G2 SIS 100522471;G2 SIS 100178113;G2 SIS 100110051;G2 SIS 100355038;G2 SIS 100187501;G3 SIS 400055032;G2 SIS 100411969/R4 SE 9591400;R5 SE 9174402;R5 Sk CAB013320307;R4 Sk CAB013531242;R4 Sk CAB013531291;R4 Sk CAB013531283;LOGIN

I
SE

M
9591400

Thanks

Paul

[Bo_Ry]

Sorry, I miss 2 points

Match(2,()) to find last position before M or I
Alphabet length before M or I increases from 40 to 200


=TRIM(LEFT(SUBSTITUTE(MID(C2,MATCH(2,1/MMULT(--(MID(LEFT(C2,-LOOKUP(1,-SEARCH(IF({1,0},I2,M2),C2))),ROW($A$1:$A$200),1)={"R","G"}),{1;1})),20)," ",REPT(" ",20)),20))

[pareid]

Thank you for that, I think it is almost there. I have found one so far that doesn't seem to work as below. It returns G3 instead of expected RWS6 because the number in M is not present in C so it should find the SIS entry with no number. (I hope that makes sense)

Thanks

Paul

C
G3 SIS 100110879;G2 SIS 100110881;RWS6 SIS;RMW ML;RWP Pru/R4 Sk;POST

I
SIS

M
1051145

[Bo_Ry]

Please try this

=TRIM(LEFT(SUBSTITUTE(MID(C2,MATCH(2,1/MMULT(--(MID(LEFT(C2,IFERROR(-LOOKUP(2,-SEARCH(M2,C2)),MATCH(2,INDEX(1/(MID(C2,ROW($A$1:$A$200),3)=I2),)))),ROW($A$1:$A$200),1)={"R","G"}),{1;1})),20)," ",REPT(" ",20)),20))


If still doesn't work, Please upload you file. and highlight the wrong result.

[pareid]

Hi, no problem and thank you.

I get a #N/A error with the latest formula. I will see if I can sort out a file to upload. The problem I have is that there are nearly 80,000 rows so the file is huge. I could try and create a smaller one of sample data but of course may miss out something accidentally.

Thanks

Paul

[Bo_Ry]

I think this "3" cause error, try change to LEN(I2)

=TRIM(LEFT(SUBSTITUTE(MID(C2,MATCH(2,1/MMULT(--(MID(LEFT(C2,IFERROR(-LOOKUP(2,-SEARCH(M2,C2)),MATCH(2,INDEX(1/(MID(C2,ROW($A$1:$A$200),LEN(I2))=I2),)))),ROW($A$1:$A$200),1)={"R","G"}),{1;1})),20)," ",REPT(" ",20)),20))

[pareid]

Hi Bo_Ry,

I think you may have sorted it. Let me do some further looking/testing and I will come back to you. I cant thank you enough for this.

Regards

Paul

[pareid]

Hi Bo_Ry,
You helped me a lot with a formula back in October 2018 and it has been a real help. Because I am working with such a large data set I occasionally come across a few anomalies and I was hoping you may beable to help me again. I have attached an example of some things I have found and put the expected result of the ones where it is giving an incorrect answer. The formula is in column B and the expected result in D.

Because we put some notes in brackets I was wondering whether the formula could ignore anything in bracket? I think it is the free text in brackets that is making the formula go funny because the text almay contain the letters R and G. Also could it give a blank answer if there is nothing in either column C or column A?

Look forward to hearing from you.

Thanks

Paul

[Bo_Ry]

Is this work?
B2
=IF(C2="","",SUBSTITUTE(MID(LEFT(SUBSTITUTE(A2,"(",REPT(" ",300)),300),MAX(1,IFERROR(SEARCH(C2&" "&I2,A2),SEARCH(C2,A2))-6),5),";",))

[pareid]

Thank you for the reply,

It does look like it works in the main but there are a couple of cells that have returned an unexpected result. I have attached the file. The numbers it is looking at are mentioned in column A but they don't have a corresponding R or G reference to the left of them. They are also in brackets so can be ignored for this purpose. I hope that makes sense. I would expect nothing to be returned in this instance unless there was an entry in A such as GDP4 SIS;GDP5 SIS 400049273;GDP6 SIS 100214010;RGro Pru;RGro Pru ISA;R6 Sk MIP013571529;(No Review or Switch on 100392220 100392217)LOGIN
Thanks again.

Paul

[Bo_Ry]

How about

=IF(C2="","",IFERROR(SUBSTITUTE(MID(LEFT(SUBSTITUTE(A2,"(",REPT(" ",300)),300),MAX(1,IFERROR(SEARCH(C2&" "&I2,A2),SEARCH(C2&";",A2))-6),5),";",),""))

[pareid]

Thanks for that,

It does look like it works in the main. I have applied it to my larger data set and notice a couple of things. Sometimes the data has been inputted incorrectly so will show R7 SE instead of RDP7 SE. The formula in these instances brings through R7 SE. Is it possible to bring through just R7? Also it is possible that the reference to RDP or DP could be longer such as RDP4Ethical SE. In this case it brings through hical.


I have attached the spreadsheet for you to look at and highlighted the cells.


Thanks

Paul

[Bo_Ry]

Ok, Please try

Please Login or Register  to view this content.

[pareid]

Thanks Bo_Ry,

that looks like it may have done it. Thank you again for your help. I will be doing some more testing over the next few days so will come back to you if I see anything else. I wish I understood the formula but definitely beyond me.

Regards

Paul

[Bo_Ry]

For Row16

A. =TRIM(MID(SUBSTITUTE(A16,";",REPT(" ",300)),ROW($A$1:$A$20)*300-299,300))&0
={"GDP5 SIS0";"RDP5 CL0";"RDP5Bespoke SIS 1001494000";"(Using ptfl A & B strategies for SIS)LOGIN0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0";"0"}
Seperate 1 text to 20 texts by ";" and add 0 at the end

Then search for =C16&" "&I16 ="SIS 328901"

B. =SEARCH(C16&" "&I16,MID(SUBSTITUTE(A16,";",REPT(" ",300)),ROW($A$1:$A$20)*300-299,300))
=#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;… ;#VALUE!;#VALUE!}
but not found , this return 20 error #VALUE!

then search for =C16&0 ="SIS0"
C. =SEARCH(C16&0,TRIM(MID(SUBSTITUTE(A16,";",REPT(" ",300)),ROW($A$1:$A$20)*300-299,300))&0)
={6;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;…;#VALUE!}

Found at the fisrt text in 6th character, "GDP5 SIS0" -> "G",1 "D",2 "P",3 "5",4, " ",5, "SIS0", 6 add 0 to make sure this not found SIS with other number eg: SIS 1001494000

D. =MATCH(0,INDEX(-SEARCH(C16&0,TRIM(MID(SUBSTITUTE(A16,";",REPT(" ",300)),ROW($A$1:$A$20)*300-299,300))&0),))
return position of number which is 1st

E. similar to A.
=TRIM(MID(SUBSTITUTE(A16,";",REPT(" ",300)),ROW($A$1:$A$20)*300-299,300))
But instead of seperate 20 text, only seperate 1 text from D. position.
=TRIM(MID(SUBSTITUTE(A16,";",REPT(" ",300)),D.*300-299,300))

="GDP5 SIS"

F
=IFERROR(TRIM(LEFT(SUBSTITUTE("GDP5 SIS"," ",REPT(" ",50)),50)),"")

Remove text after space