Extract Numbers from string

[nubranger]

Hi

I have the following samples where I need to extract just the numbers and convert them to years. I was able to accomplish this but by using Indirect which makes my spreadsheet very slow. I need a formula that is not volatile. Also no macro.

ACCT Y/E JUL 20
ACCT YE MAR 18*
FY2019 China AOS IQAR

I have no issue with the first sample. I just use this:

=CONCAT("20",MID(A122,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A122&"0123456789")),LEN(A122)))

>>> and this would return 2020 which what I wanted.

But I'm having issues with the 2nd and 3rd samples. The second sample would return 18* and the last one would return 2019 China AOS IQAR. So basically it returns the number plus all text after the last numeric from the string. I tried IF with ISNUMBER or IF >2000 (since all years to be extract would be 2018+) but none work since the returned values are not numbers.

Thanks for the help.

 

[nubranger]

Ok. I was able to finally use ISNUMBER. by using VALUE. Now I can do an if for the "*" and trim it. My problem is the 3rd sample now.

=IF(ISNUMBER(VALUE(CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79))))),VALUE(CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79)))),0)

 

[nubranger]

Ok. I was able to finally use ISNUMBER. by using VALUE. Now I can do an if for the "*" and trim it. My problem is the 3rd sample now.

=IF(ISNUMBER(VALUE(CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79))))),VALUE(CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79)))),0)

Finally solved the second sample with this:

=IF(ISNUMBER(FIND("*",CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79))))),VALUE(SUBSTITUTE(CONCAT("20",MID(A79,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A79&"0123456789")),LEN(A79))),"*","")),0)

3rd sample is the issue now

 

[Fluff]

This will work for all 3 samples shown
=SUBSTITUTE(MID(A7,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A7&"0123456789")),4),"*","")

 

[nubranger]

This will work for all 3 samples shown
=SUBSTITUTE(MID(A7,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A7&"0123456789")),4),"*","")

Thanks. Works like a charm

 

[nubranger]

This will work for all 3 samples shown
=SUBSTITUTE(MID(A7,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A7&"0123456789")),4),"*","")

What if I have the following entries as well:

ACCT Y/E DEC 19 (SINGAPORE)

ACCT Y/E DEC 19 ( JAPAN)

ACCT Y/E DEC 19 ( INDONESIA)

ACCT Y/E DEC 19 (Interim accounting project)

Thanks for the help

 

[nubranger]

Ok i've use this. not sure if there is something better:

=TRIM(SUBSTITUTE((SUBSTITUTE(MID(A43,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A43&"0123456789")),4),"*","")),"(",""))

 

[lrobbo314]

Using Power Query

Book1
	A	B
1	Column1	Custom
2	ACCT Y/E JUL 20	20
3	ACCT YE MAR 18*	18
4	FY2019 China AOS IQAR	2019
5	ACCT Y/E DEC 19 (SINGAPORE)	19
6	ACCT Y/E DEC 19 ( JAPAN)	19
7	ACCT Y/E DEC 19 ( INDONESIA)	19
8	ACCT Y/E DEC 19 (Interim accounting project)	19
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Extract = Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9"}))
in
    Extract

 

[nubranger]

Using Power Query

Book1
	A	B
1	Column1	Custom
2	ACCT Y/E JUL 20	20
3	ACCT YE MAR 18*	18
4	FY2019 China AOS IQAR	2019
5	ACCT Y/E DEC 19 (SINGAPORE)	19
6	ACCT Y/E DEC 19 ( JAPAN)	19
7	ACCT Y/E DEC 19 ( INDONESIA)	19
8	ACCT Y/E DEC 19 (Interim accounting project)	19
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Extract = Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9"}))
in
    Extract

Thanks. But I don't have experience with Power Query yet. Not sure if it is something that automatically updates.

 

[nubranger]

Not sure if possible. I have the following recurring samples where I need to extract the numbers and convert them to years.

Compilation fee for Q1 (Jan 20 to Mar 20)
Additional Transactions for November 2019
Rep Office from Jan 19 to Mar 19
Services for November 2019 (Early reporting)
Support for FYE 31 Dec 2018
Software from Dec 19 to Mar 20

In the first sample I should get 2020. If i can extract the last 2 numeric characters I could just use concat to add "20" to get the year. The last sample is actually two years but I just need the last two digits which is 20.