Nested if mid and find, possible?

Hi i'm tearing my hair out as i'm sure many of us visiting looking for excel answers are! you help is much appreciated

I have data which is non structured that i am trying to pull a date from in the format of 09/Sep/2018
i have thousands of rows but the date always appears after 10 different specific words, maybe there is a better way but i have been able to pull the date using the words and the mid functions as the examples below

at the moment it successfully does it but i have to do each word in a different column.

i would like to be able to nest the formulas somehow so i can have the results all in one column but i cant work out how? is this possible or is there a better way?

Thanks in advance


=MID($A2,FIND("dizzying ",$A2)+9,11)

=MID($A62,FIND("COMPLETE ",$A2)+9,11)

=MID($A287,FIND("jukebox ",$A2)+8,11)

=MID($A290,FIND("Yes ",$A2)+4,11)

Thanks guys, i think i may have been bad with the description

ill upload screenshots for now as im not sure how to attach but this is what i want it to look like and the style - its not uniformed so the date is in the same place each time, the date is in a random place.

by 10 words i meant that it does not come after 10 words in a string but there are 10 specific words such as jukebox, complete, dizzying or yes where the date i want always follows. so my current formula looks for that word in the text then returns the date asit comes after and i tell excel to return that number of characters.


i want it to look like
comment date
dave danced in the jukebox 20/jan/2019 20-Jan-19
paul fell dizzying 19/sep/2019 19-Sep-19
graham ate a bannana while crouching on a jukebox 05/Nov/2018 it made him ill 05-Nov-18
1 day the mand from del monte said yes 08/Aug/2019 mike laughed 08-Aug-19
paul said 2 helicopters are scary when the rotors are both complete 12/Dec/2017 im not sure why 12-Dec-17
Helen drank the jukebox 25/Nov/2018 25-Nov-18
Helen said yes 07/Feb/2017 crazy cheese hat 07-Feb-17
the face was complete 23/Mar/2017 Helen was not 23-Mar-17
the Onions hand was dizzying 09/Sep/2018 it cried 09-Sep-18

Attachment 625708

It currently looks like

Attachment 625711

apologies ive been trying to figure out how to upload but the attachment option only gives a blank bar with no options so im not sure how to do it?

thank you for your help

it seems to have worked!

tab1 shows how I want it to look with the calculation in one column

tab2 shows the formula working but in separate columns for the keywords I would like to find a way of nesting the formula so I don't need to spread it across the columns

Thank you

Wow Thank you Jeffrey! such a simple fix without nesting, I really appreciate that

that worked, I have 2 further questions (last ones!) -

if I were to have 2 dates in the description box would there be a way to pull out only the last date shown within the description - this is in the attachment on tab scenario 1



now the tough one - again similar - shown as scenario 2 tab in the attached

I want to find out a 4 digit number beginning with 1 and display the number as the answer in the new cell from a description box with text which is non uniformed and can also contain numbers. it is always preceeded by a / or - or a space. I have thought about vlookup as there is a number range between 1000 and 1999 that the number would fall in but im not sure if that would be usable with find?

Try this in B2 (Scenario 1):

Please Login or Register  to view this content.

Originally Posted by FlameRetired

Scenario 2:
Might work if the 4 digit numbers in question always start with "1" as in the upload.

Formula:

Please Login or Register  to view this content.

This is awesome. The only possible hole that I see would be if there was something like this:
Rob1-SZ-123456/12021/1111/10000001/Kent

But that has not come up in the OP's sample.