+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    The paperclip attach icon doesn't work so click the 'Go Advanced' button, look underneath the post panel for 'Manage Attachments' and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    Hi Richard,

    Thank you for the warm welcome. You're indications as to where the attachment part is really useful I couldn't find where to do that before, cheers.

    I've attached a smaller version of my document with only 4 cells with which I have been experimenting (4 0f 70000 cells). I have highlighted in blue the text that is meant to represent a specific column and in red the value associated with that column (the data is organise as <tr> represents the begining of a string of information and finishes with </tr>, I am only interested in the last value of each thread of information. This red value represents the depth in kilometers under the water height, which should always be 0.0.

    I hope this makes a bit more sense if there is still some confusion please let me know and I'll do my best to clarify.
    Attached Files Attached Files
    Last edited by Tompouce; 05-05-2018 at 02:46 AM.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    Show your expected result
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    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
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    Does this give you an idea
    E2 of the smaller file
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I put that together before I saw your last post. But hopefully it gives you an idea of the technique you need to use if I've understood the requirement.

  7. #7
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    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 .

  8. #8
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    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?

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    OR

    D2
    Please Login or Register  to view this content.
    Try this and copy across

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    =SUBSTITUTE(IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(MID($A2,SEARCH(D$1,$A2)-1,10000),"</td>",REPT(" ",256),6),"<td>",REPT(" ",256),5),256,256)),IF(COLUMNS($D2:D2)=1,0,"*"&INDEX(C2:D2,1))),"**","*")
    made a small change at colored part.

  11. #11
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    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.

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,937

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    There is no Middle_Seds. in "A4" so that just I copied the value from E4 to F4
    What your answer would be in this case

  13. #13
    Registered User
    Join Date
    05-04-2018
    Location
    London
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    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?

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Large string within cell - Isolate text and ninth value after the text, assign to colu

    Quote Originally Posted by Tompouce View Post
    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 .
    Hi,

    Yes that's exctly correct
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The E$1 cell reference is row absolute so that when copied down it always refers to E1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    returns the value 262 which is the position in A2 where the E1 text is found
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    wrapping the Find in a mid returns a section of A2 starting at position 262 + whatever the length fo the E1 text is + 10 which I assumed would be big enough to include e vary big number should ther be on. Hence this MID function returns "2.0</td>"

    Now all that's left to do is strip out the numeric
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The Find bit gest the position of the "<" in the "2.0</td>" which is in this case 4.
    So now adding the LEFT formula finds the leftmost 4 characters in the "2.0</td>" string.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 05-27-2016, 12:30 AM
  2. Replies: 5
    Last Post: 02-11-2015, 10:41 PM
  3. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  4. isolate the first name in this text string
    By xtinct2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2013, 09:52 AM
  5. [SOLVED] Need a formula to isolate a number from a text string
    By chicolocal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 01:19 PM
  6. Can you use an excel function to isolate part of a text string?
    By CharCat in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-27-2012, 02:05 AM
  7. isolate number from string of text
    By Stephen R in forum Excel General
    Replies: 7
    Last Post: 08-04-2005, 04:05 PM

Tags for this Thread

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