# Searching A String and Extracting Values of Varying Length

1. ## 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.

2. ## 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.``

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. ## 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. ## 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.

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