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.

 

[Rick Rothstein]

As long as you will never insert any rows before or within the data (otherwise a different, longer formula would be needed), this array-entered** formula would work...

=20&MID(A1,MAX(IF(ISNUMBER(0+MID(A1,ROW($1:$99),1)),ROW($1:$99)))-1,2)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[nubranger]

As long as you will never insert any rows before or within the data (otherwise a different, longer formula would be needed), this array-entered** formula would work...

=20&MID(A1,MAX(IF(ISNUMBER(0+MID(A1,ROW($1:$99),1)),ROW($1:$99)))-1,2)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Hi Rick. Works like a charm. How about if I have this entry also:

services for financial year ended 31 December 2018

It returns 2031.

EDIT: I think it works now. I just need to change the Row($1:$99) to the number of rows I use.

Thanks

 

[sandy666]

@lrobbo314
based on post#10
Year = Table.SelectColumns(Table.TransformColumnTypes(Table.AddColumn(Source, "Year", each "20"&Text.End(Text.Select([raw],{"0".."9"}),2)),{{"Year", Int64.Type}}),{"Year"})
what do you think?

 

[nubranger]

As long as you will never insert any rows before or within the data (otherwise a different, longer formula would be needed), this array-entered** formula would work...

=20&MID(A1,MAX(IF(ISNUMBER(0+MID(A1,ROW($1:$99),1)),ROW($1:$99)))-1,2)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Hi Rick. How about if I have this entry:

Reconciliations for the month of December 2019 (IDR 3,850,000)

 

[Rick Rothstein]

Hi Rick. How about if I have this entry:

Reconciliations for the month of December 2019 (IDR 3,850,000)

Then my formula would not work. I would note, however, that none of your examples indicated here would or could be numbers after the year number you were looking for and, given we know absolutely nothing about your data, it would be impossible for us to guess that possibility from the examples you posted.

EDIT: I think it works now. I just need to change the Row($1:$99) to the number of rows I use.

The 99 in ROW($1:$99) was a guess at a number longer than the maximum length text in any one cell. If your maximum length text is, say, 324 characters, I would use 400 in place of the 99.