Searching A String and Extracting Values of Varying Length

**zac10sim** · 02-01-2021, 03:42 PM

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.

**Norie** · 02-01-2021, 04:10 PM

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.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDst As Range
Dim RegExp As Object
Dim matches As Object
Dim match As Object
Dim strFormula As String

    If Target.Address(0, 0) = "B3" Then
    
        Application.EnableEvents = False
        
        strFormula = Target.Value
        Set RegExp = CreateObject("VBScript.RegExp")

        RegExp.Pattern = "[A-Za-z]+"

        RegExp.Global = True

        Set matches = RegExp.Execute(strFormula)
        
        Set rngDst = Range("B7")
        For Each match In matches
            rngDst.Value = match.Value
            Set rngDst = rngDst.Offset(, 1)
        Next match
        
        RegExp.Pattern = "[.0-9]+"

        Set matches = RegExp.Execute(strFormula)
        
        Set rngDst = Range("B8")
        For Each match In matches
            rngDst.Value = Val(match.Value)
            Set rngDst = rngDst.Offset(, 1)
        Next match
        
        Application.EnableEvents = True
        
    End If
    
End Sub

**zac10sim** · 02-01-2021, 04:50 PM

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.

**Norie** · 02-01-2021, 05:03 PM

This would clear B6:J7 and it would go after Application.EnableEvents = False in the code.

Range("B6:J7").Value = ""

If you want to put - in the cells.

Range("B6:J7").Value = "-"

**zac10sim** · 02-01-2021, 05:34 PM

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.

Searching A String and Extracting Values of Varying Length

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Searching A String and Extracting Values of Varying Length

Re: Searching A String and Extracting Values of Varying Length

Re: Searching A String and Extracting Values of Varying Length

Re: Searching A String and Extracting Values of Varying Length

Re: Searching A String and Extracting Values of Varying Length

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract text from end of string with varying length

[SOLVED] Extracting varying length text from the middle of a string

[SOLVED] Take date from string and input into varying column length

Extract 2nd word from a string - varying length, between colons

Extracting numbers of varying length from a string

Extract data from a string - Varying length, between spaces

[SOLVED] extracting data from a text string of varying length

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions