+ Reply to Thread
Results 1 to 5 of 5

Searching A String and Extracting Values of Varying Length

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Searching A String and Extracting Values of Varying Length

    Hello,

    I am relatively new to excel Programming and am trying to figure out the best way to code the below problem. I have a cell which is numbers and text mixed together. It looks something like this.

    Cr.15Al2.0V0.5

    The goal is to populate a table That is the element above with the amount below.

    Cr Al V
    0.15 2.0 0.5

    I have figured out how to populate the amount Row using the below text. It searches my large string for the element after I manually enter it and returns the three characters following the string. It works for any length elemental abbraviation, so V if fine.

    =IF(ISNUMBER(SEARCH(C6,$B3)),VALUE(MID($B3,(FIND(C6,$B3,1)+LEN(C6)),3)),0)

    What I would like to be able to do though is just enter the "total string" and have the element symbols populate.

    I can't seem to find an elegant way however to just input the string into the "total string" cell and have all the relevant values extracted into their appropriate rows without a second reference to search.

    For a more complete set showing of what I am trying to discuss please see the attached workbook.
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Searching A String and Extracting Values of Varying Length

    If you are looking for code you could try this.

    It would go in the sheet module of 'HEA Mass Calculator' and will be triggered when a formula is entered in B3.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    02-01-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Searching A String and Extracting Values of Varying Length

    Thanks. This worked really well for the most part. The index was one row too low, so I changed that.

    There is a small problem though with the code. If I were to write a string composed of three elements, then move to one composed of just two, the then the third element remains which throws off my calculation.

    My assumption for how to fix this would be the first step of the macro be writing "-" into the cells B6:J6, but I don't know how to do this either in VBA. Thank you for your help.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Searching A String and Extracting Values of Varying Length

    This would clear B6:J7 and it would go after Application.EnableEvents = False in the code.
    Please Login or Register  to view this content.
    If you want to put - in the cells.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-01-2021
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Searching A String and Extracting Values of Varying Length

    You are a life saver. Thank you so much.

    Looking through this code it mostly makes sense. I do have a question about where you select for the elemental names. The A-za-z section what does that do? It has something to do with the letter selection I assume, but I am not familiar enough with this syntax to understand the output.

+ 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. [SOLVED] Extract text from end of string with varying length
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 05:16 PM
  2. [SOLVED] Extracting varying length text from the middle of a string
    By tm314159 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-20-2014, 07:23 PM
  3. [SOLVED] Take date from string and input into varying column length
    By bruizer31 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-26-2013, 10:55 AM
  4. Replies: 5
    Last Post: 09-11-2012, 08:40 AM
  5. Extracting numbers of varying length from a string
    By bethj in forum Excel General
    Replies: 3
    Last Post: 12-13-2011, 06:03 AM
  6. Replies: 6
    Last Post: 10-01-2009, 02:50 PM
  7. extracting data from a text string of varying length
    By andy from maine in forum Excel General
    Replies: 4
    Last Post: 03-28-2005, 03:06 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