+ Reply to Thread
Results 1 to 8 of 8

Seeming inconsistency in parsing formula

  1. #1
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    9

    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
    Attached Files Attached Files
    Last edited by serge57; 12-05-2018 at 07:05 PM.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,470

    Re: Seeming inconsistency in parsing formula

    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
    Last edited by humdingaling; 12-05-2018 at 08:11 PM. Reason: amended answer to formula output
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,470

    Re: Seeming inconsistency in parsing formula

    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: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Seeming inconsistency in parsing formula

    Thank you, humdingaling. Your formula did work - on every reference that I checked. It is also easier to understand, and therefore, preferable. As I said, I got the formula for my column B from the internet, and used it, although I did not understand it. I've never used curly brackets in a formula before, and so thought the idea of putting all 10 numbers (0,...,9) within curly brackets was so that it would search for each of them separately. Why would it only look for one of them - zero? I am new to this, and would appreciate some help in understanding (if that is appropriate in this forum). Thanks.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,470

    Re: Seeming inconsistency in parsing formula

    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: copy to clipboard
    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: copy to clipboard
    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: copy to clipboard
    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: copy to clipboard
    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
    Last edited by humdingaling; 12-05-2018 at 09:41 PM. Reason: ps sorry for wall of text i went overboard

  6. #6
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Seeming inconsistency in parsing formula

    Wow, that's quite the in-depth explanation! Thank you very much. Your explanation was very thorough. It is clear though, that there is so much to learn! It's incredible how powerful Excel is, and how little of it most of us use.

    Thanks again.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,470

    Re: Seeming inconsistency in parsing formula

    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)

  8. #8
    Registered User
    Join Date
    12-03-2018
    Location
    USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Seeming inconsistency in parsing formula

    Thanks for the tips as well!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1