Seeming inconsistency in parsing formula

I have a long list of references (citations). I would like to parse each reference (string) to separate out the author(s), year, title of the article, and source (url). Since there are over 6500 references, I would obviously like to do this programmatically. My formulae seem to be working well (please see attached for a sample). However, I cannot determine why my formula for "Year" (Column D) is being inconsistent. It is working perfectly correctly until Row #5, pulling out the year where there is a year in the citation, and putting "N/A" where there isn't one. But for Row #6, I am getting N/A when I should get 1984. The funny part is if I only use the MID part of the formula, I get the correct result (as can be seen in Cell #D13). So why isn't it working in Cell $D6, but working in the other cells of that column?

Also, I obtained the formula for the position of the first number (Column B) from the internet. It works for my purpose, but I don't understand the part from the ampersand (&) onward in the formula. Why & and why are all the numbers being repeated?

Any help will be appreciated.

Thanks,

Serge

i can quickly answer why its inconsistent....

your formula actually looks for ZER0
thats why some work and some dont
the ones with 0 in them will work while the ones without does not

To test in A6, put a 0 anywhere
this will trigger the formula which is
from the first number from column B ....4 characters

your formula is actually no better than just putting this formula which gives more returns IF your data set is consistent

in D2 fill down

Formula:

Please Login or Register  to view this content.

I'm not expert of array formulas i only have a moderate knowledge of it ....so ill try explain best i can with plain English

Your understanding of the curly brackets is pretty spot on...the caveat is...it works only in certain formula in certain ways
when it doesn't work it just looks for the 1st number in the brackets

Formula:

Please Login or Register  to view this content.

this part of the formula actually works

however it errors out when you put it in the IF statement you have

Formula:

Please Login or Register  to view this content.

lets say you have number 0, 1, 2, 5 in string (ie 2015)

ok it finds 0..no problems..your isnumber = TRUE so it gives you your mid formula which gives 4 character string

The formula does know what to do when it finds 1 (or any number after 0)? since it doesn't know what to do so it does nothing and just ignores the other 9 numbers
now if your following so far.....my post #2 should make sense now

so your asking....but how does Column in B work

Formula:

Please Login or Register  to view this content.

Lets break down what B2 gives
it looks for 0 in A2,..which is 18 because "Macaya-Berti J. 20" = 18
1 gives 19
2 gives 17
3 gives 192
etc....(rest is bigger numbers)

so 17 is the smallest hence the answer is 17

now the above formula can actually be written like

Formula:

Please Login or Register  to view this content.

and it still works and gives the same answer

ill explain why
in cell b4 where no numbers exist
the length of A4 is 153
so the formula with {} gives (154,154,154,154,154 .....(10 times)

This formula though
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
gives (154,155,156,157...etc)
however the min in both cases is still 154

your now asking why put the "&" at all
its to stop errors appear, once an error appears the formula breaks down and gives your #value error
if any text does not have a certain number it will just error out
ie min (154,154,#n/a,154...etc) = Error

anyways this is a pretty depth rich subject
if you want to learn more i suggest you google Array formulas
there is much to learn and i'm always been shown new ways of using it

not a problem, learning and helping others learn is what helps most of the frequent users on here

if you want to learn...just practice
go on forums and pick up questions to answer

you don't have to be one that answers it necessarily but you can certainly learn by how someone else answers it
sometimes you will get explanation sometimes you wont ....

learn to use the evaluate formula
https://www.myexcelonline.com/blog/e...step-in-excel/
if you know the result is the correct answer, you can (generally in most cases) backwards engineer it

this goes for most topic in excel (not VBA though..that's another kettle of fish)