Searching a string for a character

[pickslides]

I have this data and want to extract the character after the letter Y if the string has a Y in it.


Example data Output

AU 2013 OD ANR B24 Y2 2
AU 2013 OD ANR B24 Y4 4
AU 2013 OD ANR B24 Y5 5
AU 2013 PD HLD NOV B SPA
AU 2013 PD HLD NOV C SPA
AU2013OD ANR B25 Y1 1
AU2013OD ANU B25 Y5 5
AU2013OD WCR FPVN B49
AU2013OD Y6 FPVN B49 6
AU2013OD WCR FPVN B40
AU2013OD WCR FPVN B43


MQ

[daffodil11]

=if(isnumber(search("y",a1)),mid(A1,search("y",a1)+1,1),"")

Even better:

=IFERROR(MID(A1,SEARCH("Y",A1)+1,1),"")

[judgeh59]

a Slighty different tack...

Please Login or Register  to view this content.

I like the search function....maybe just a little cleaner....

EDIT: I always forget to check the Version....

[Richard Buttrey]

Hi,

If it's only ever one character following a Y then

Please Login or Register  to view this content.

[AlKey]

This is for Excel 2003

=IF(ISERROR(SEARCH("Y",A1)),"",--LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"Y",REPT(" ",255)),255))))

	A	B
1	AU 2013 OD ANR B24 Y2 2	2
2	AU 2013 OD ANR B24 Y4 4	4
3	AU 2013 OD ANR B24 Y5 5	5
4	AU 2013 PD HLD NOV B SPA
5	AU 2013 PD HLD NOV C SPA
6	AU2013OD ANR B25 Y1 1	1
7	AU2013OD ANU B25 Y5 5	5
8	AU2013OD WCR FPVN B49
9	AU2013OD Y6 FPVN B49 6	6
10	AU2013OD WCR FPVN B40
11	AU2013OD WCR FPVN B43

[pickslides]

These were great solutions.

Thanks so much!

[AlKey]

You're welcome and thank you for your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).