Large string within cell - Isolate text and ninth value after the text, assign to column

Hi Excel Forum Community,

I'm brand new to this forum and quite a newbie when it comes to excel.

I'm trying to tackle formulas in excel in order to make sense of a large amount of data I have downloaded. The data downloaded has loads of information I am not interested in. Basically, it's a collection of thousands of coordinates, which each one of them is associated 5 values (Type, Vp, Vs, Density, Thickness, Top). I am only interested in the "Top" value which effectively corresponds of to the depth position of each layer of the earth. The data is organised as follow: Water, Upper_seds, Middle_Seds, Lower_Seds, Upper_Crust, Middle_Crust, Lower_Crust and Mantle. I'd like to have each one of the previous titles to be a column with it's associated "Top" value organised under. Is that even possible with formulas?

Hopefully, this is not in the wrong thread if it is apologies in advance.

It happens that for one coordinate not all value for every 8 layers of the earth is present and it would be ideal if the value was just replaced with the previous columns exact same value.

I have been trying to extract the depth value of each layer at a given point and assigning it to a specific column without much luck at all for the moment and that's after spending a couple of days reading through blogs dedicated to the subject. Could anybody give me a hand in trying to make sense of all this information?

I hope I've been clear in explaining my issue, I'd be happy to answer any questions.

Thanks,
Tom

I've just uploaded an update to my original file with just a copy and paste of the information I would like in the right columns.

The red text is the copy and pastes from the cell and the black values would be the fill if there aren't any values for the given column name if not present in the cells in column A

Thanks for this Richard. I've tried the formulas in my table and it isn't returning the value I was hoping for now.

I'm trying to decrypt your formula in order to understand what it actually does..... it's just taking me a bit of time as I'm very new to formulas in excel. I'll let you know how I get on. thanks again for your help.

At least it's returning a value back, which it wasn't before with my really poor attempts at this exercise so there is some positive .

Excelformula-decoding1.jpg

I've attached the piece of writing I've been writing in order to make sense of it all. Two points that are confusing me. Why are you using the $ to lock certain cells or is it columns? Are you using the names that are witen in the top of column to isolate text in the A2 string?

Then the LEN function is used to specify the start of the isolation? within the A2 cell?

Wow nflsales! This is amazing, thank you very much for figuring this out.

It has worked brilliantly for the first 3 cells but it seems like there is a problem on the 3rd row. The formulas aren't registering the Middle_Seds value and are just applying the same value as for the Upper_Seds therefore overide one value. DO you think there is a fix to that?

It is so close though, I really appreciate your help with this. You're avoiding me a lot of headache and numerous hours trying to figure this out. That formula seems very complex. I'm trying to figure out what you have done.

You're right! Doing the change you advised in thread 10, changing the $A$2 to $A2, did the trick. I'm really impressed, I still don't really understand what you did but it seems to work perfectly.

As I was testing your formulas with different information I came to realize that in very few cells there is a layer called "Ice" that I didn't account for previously. So I added another column called Ice and even then the formulas was still isolating the value associated with *Ice* in the A column cell. This formulas is just fool proof, love it. Thanks so much nflsales.

How do I accept your answer as being the solution to my problem?