Reducing Formulas for finding Text in a String

[trafficman30]

Happy Monday Everyone


I can make my way around excel (just), but once I figure out a way to get something working, I usually leave it there.


I created a formula to build a Sheet VKUP_PAGE to use as a VLOOKUP table and return one of 3 variants of the following examples in Sheet TESTPAGE

TEST1 Random bit of text in the middle somewhere Input 30

TEST2 TEST3 Random bit of text in the middle again somewhere Output 122

TEST4 Random bit of text in the middle Result 1

<tbody>
</tbody>

On Sheet LKUP_PAGE I wanted Column 1 to return the Input / Output or Result (and associated number) and I ended up writing this monstrosity...

=IFERROR(IFERROR(IFERROR(TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Result",TESTPAGE!A1))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Output",TESTPAGE!A1)))),TRIM(RIGHT(TESTPAGE!A1,LEN(TESTPAGE!A1)-FIND(" Input",TESTPAGE!A1))))," ")


In the Column 2 on LKUP_PAGE I return the 1st Text String, eg. TEST1 using the following...

=LEFT(TRIM(TESTPAGE!A1),FIND(" ",TRIM(TESTPAGE!A1)&" ")-1)

So for the first 3 rows, I would get

Input 30	TEST1
Output 122	TEST2
Result 1	TEST4

<tbody>
</tbody>

and I use this VLOOKUP table in other pages to call the String in Column 2
example

=IFERROR(VLOOKUP("Output 122",LKUP_PAGE!$A1:$B300,2,FALSE)," ")

<tbody>
</tbody>

It all works as expected, but I just think its very long winded.

Any easier option, (VBA included?) would be good to see and decipher.



Thanks in advance.

 

[pgc01]

Hi

For column 1, try:

=MID(TESTPAGE!A1,MIN(IFERROR(SEARCH({"result","input","output"},TESTPAGE!A1),LEN(TESTPAGE!A1)+1)),99)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

 

[trafficman30]

Hi

For column 1, try:

=MID(TESTPAGE!A1,MIN(IFERROR(SEARCH({"result","input","output"},TESTPAGE!A1),LEN(TESTPAGE!A1)+1)),99)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Thank you pgc01

This works a treat apart from....

(and this is my mistake)


Some of my columns have the search string occurring more than once.
e.g.

TEST1 Random bit of text in the middle somewhere Input 30



TEST1 Random bit of text Input in the middle somewhere Input 30


I need to return the last Input & number string

 

[bosco_yip]

Some of my columns have the search string occurring more than once.
e.g.

TEST1 Random bit of text in the middle somewhere Input 30



TEST1 Random bit of text Input in the middle somewhere Input 30


I need to return the last Input & number string

A bit modified pgc01's formula :

=MID(TESTPAGE!A1,MAX(IFERROR(SEARCH({"result","input","output"},TESTPAGE!A1,ROW($1:$99)),0)),99)

This is an array formula, to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Regards

 

[trafficman30]

A bit modified pgc01's formula :

=MID(TESTPAGE!A1,MAX(IFERROR(SEARCH({"result","input","output"},TESTPAGE!A1,ROW($1:$99)),0)),99)

This is an array formula, to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Regards

Hi bosco_yip

This amendment populates my columns as before with the reduced code and the last string in the text.

My other pages with VLOOKUP references stop populating now, if using either of the formulas

=IFERROR(VLOOKUP("Result 1",LKUP_PAGE!$A1:$B300,2,FALSE)," ")

<tbody>
</tbody>

 

[bosco_yip]

Hi bosco_yip

This amendment populates my columns as before with the reduced code and the last string in the text.

My other pages with VLOOKUP references stop populating now, if using either of the formulas

=IFERROR(VLOOKUP("Result 1",LKUP_PAGE!$A1:$B300,2,FALSE)," ")

<tbody>
</tbody>

This formula appear no problem.

=IFERROR(VLOOKUP("Result 1",LKUP_PAGE!$A1:$B300,2,FALSE)," ")

Your range A1: A300 datas may contain hidden space in behind, please checked by this formula :

=ISNUMBER(MATCH("Result 1",LKUP_PAGE!$A1:$A300,0))

 

[Jonmo1]

Is it fair to say you're trying to find the string at the end, after the 2nd to last space?

To get the Input/Output/Result bit
=TRIM(RIGHT(SUBSTITUTE(TESTPAGE!A1," ",REPT(" ",LEN(TESTPAGE!A1))),LEN(TESTPAGE!A1)*2))

To get the Test bit
=LEFT(TESTPAGE!A1,FIND(" ",TESTPAGE!A1)-1)

Excel Workbook
	A	B	C	D
1	TEST1 Random bit of text in the middle somewhere Input 30		Input 30	TEST1
2	TEST2 TEST3 Random bit of text in the middle again somewhere Output 122		Output 122	TEST2
3	TEST4 Random bit of text in the middle Result 1		Result 1	TEST4
TESTPAGE