how to extract numbers from file names in an excel query?

[yabi100]

I have four files in a folder. Names contain text and numbers. I want to extract numbers in a query and assign them into a column. These numbers are years and I want to build a year column in the query. I don't know how to extract numbers from file names in a query?

 

[RoryA]

Which version of Office do you have? (please update your profile as it will influence the possible solutions that can be offered) Also, is there any pattern to where the numbers might appear?

 

[yabi100]

Which version of Office do you have? (please update your profile as it will influence the possible solutions that can be offered) Also, is there any pattern to where the numbers might appear?

I am using excel 2016. Yes there is a pattern. Filename format is as follows: year ** of sales. ** are numbers. Two digits for years are the 5th and 6th character.

 

[RoryA]

You can use a custom column with a formula like:

=Text.Range([Filename],4,2)

Note that the start position goes from 0 so 4 is the 5th character.

 

[yabi100]

[Filename]

Are the brackets [] also part of the formula?
Does it matter, which filename should be put in the formula?
I did it withou brackets and put the first file name with 08 as numbers. It returned 08 for all 4 rows. they should be 08, 09, 10,11

 

[RoryA]

FileName is the name of the column that has the filenames in it.

 

[yabi100]

A

Are the brackets [] also part of the formula?
Does it matter, which filename should be put in the formula?
I did it withou brackets and put the first file name with 08 as numbers. It returned 08 for all 4 rows. they should be 08, 09, 10,11

As they are numbers, I used "Add Index Column" and put the formula as follows: starting index: 11, increment -1. It solved my problem but I don't like it. Will be happy to hear comment on format for filename.
Which website, can I find more on formula formats for Power Query?

 

[yabi100]

FileName is the name of the column that has the filenames in it.

Thanks RoryA. Sorry for a basic question. I am new to power query.