Extract Text using one formula

[pto160]

I have the latest version of Excel 365 with all the new functions. so I am wondering if extracting text will be easier.
So if cell in column A says "Bank" then the result returned will be the text in column B.
There is two scenarios with the cell named GL in column A where the text string begins with an A. The max length of the text string allowed is 10 characters.
First scenario, is text beginning with a "A" then yymmdd then the last 3 characters reserved for a number. If the last three characters are a number like for example 005 or 010 or 400 then the result returned will be 5, 10 or 400 returned in column C.
The next scenario, If the last character or last two characters are a letter like B or AC, then the text is formatted beginning with an A then mmddyy then the number then the letter or two letters at the end. If the string ends with a two letters like "AC", then I can't use 02 for the month as it would exceed 10 characters. I have to use 2 for the month. The example will makes this clearer.

Book1
	A	B	C	D
1			Result I want
2	Bank	5	5	Text Length
3	GL	A221228005	5	10
4	GL	A221228010	10	10
5	GL	A221228400	400	10
6	GL	A12282201A	1	10
7	GL	A12282202A	2	10
8	GL	A1228222AC	2	10
9	GL	A12282210A	10	10
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IF(A2="Bank",B2)
D3:D9	D3	=LEN(B3)

 

[Peter_SSs]

Is this what you are after?

23 01 05.xlsm
	A	B	C
1			Result I want
2	Bank	5	5
3	GL	A221228005	5
4	GL	A221228010	10
5	GL	A221228400	400
6	GL	A12282201A	1
7	GL	A12282202A	2
8	GL	A1228222AC	2
9	GL	A12282210A	10
Extract

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2="Bank",B2,IF(ISNUMBER(RIGHT(B2,1)+0),RIGHT(B2,3)+0,MID(B2,8,1+ISNUMBER(MID(B2,9,1)+0))+0))

 

[Phuoc]

Or try

Book1
	A	B	C
1			Result I want
2	Bank	5	5
3	GL	A221228005	5
4	GL	A221228010	10
5	GL	A221228400	400
6	GL	A12282201A	1
7	GL	A12282202A	2
8	GL	A1228222AC	2
9	GL	A12282210A	10
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LOOKUP(999,--LEFT(RIGHT(B2,3),{1,2,3}))

 

[kvsrinivasamurthy]

Try,

=IF(A2="Bank",B2,VALUE(TEXTJOIN("",TRUE,IF(ISNUMBER(MID(B2,{8,9,10},1)+0),MID(B2,{8,9,10},1)+0,""))))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1			Result I want
2	Bank	5	5
3	GL	A221228005	5
4	GL	A221228010	10
5	GL	A221228400	400
6	GL	A12282201A	1
7	GL	A12282202A	2
8	GL	A1228222AC	2
9	GL	A12282210A	10
Data

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2="Bank",B2,TEXTBEFORE(RIGHT(B2,3),CHAR(SEQUENCE(,26,65)),,,1)+0)

 

[pto160]

Thanks so much to everyone. These solutions are absolutely fantastic. This is going to save me so much time.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[pto160]

I am not sure if I need to set up a new thread but the data stays the same. In column D, I would like to see if the last character or 2nd last last character ends in for example A or AC then it is text otherwise it is a number.

Book1
	A	B	C	D
1			Result I want	Is Text
2	Bank	5	5	No
3	GL	A221228005	5	No
4	GL	A221228010	10	No
5	GL	A221228400	400	No
6	GL	A12282201A	1	Yes
7	GL	A12282202A	2	Yes
8	GL	A1228222AC	2	Yes
9	GL	A12282210A	10	Yes
10	GL	A12282210BA	10	Yes
Sheet1

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E
1			Result I want	Is Text
2	Bank	5	5	No
3	GL	A221228005	5	No
4	GL	A221228010	10	No
5	GL	A221228400	400	No
6	GL	A12282201A	1	Yes
7	GL	A12282202A	2	Yes
8	GL	A1228222AC	2	Yes
9	GL	A12282210A	10	Yes
10	GL	A12282210BA	10	Yes
11
12
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=IF(ISNUMBER(RIGHT(B2)+0),"No","Yes")

 

[pto160]

That works. Thanks so much.