Thread: find string within a string

Guys-

A text file with many elements is placed in column A1.
I am interested in extracting the elements value and placing it in its appropriate cell.

Column A1
87LPP=5.00 87LP1 = 4.3 50PP1 = 0.67
50PD = 2 51PD = 3.00 79 = Y

for example, I'd like to find 87LPP in column 1, and put 5.00 into range G1. then find 87LP1 and put 4.3 into range G2, then find 50PP1 and put 0.67 into range G3.

The issue I am having is that sometimes spaces between the equal signs may be 1 or 2 or even 3 spaces between values. here's a snipet of code I've come up with, but it's not working entirely.

Your help would be much appreciated, thanks

'******************************************************************************************************
'******************************************************************************************************
'GET SETTING NAME FROM NAME MANAGER AND GET RID OF 'SET'
'FROM NAME MANAGER CHANGE SETTING NAME (IE, SET87LPP TO 87LPP)
        OriginalName = CellName.Name
        SettingName = Mid(CellName.Name, 4, Len(CellName) - 3)
        
        'START AT THE TOP OF THE TEXT FILE IMPORTED TO EXCEL
        'FIND SETTING NAME in A1 COLUMN
        Range("A1").Select
        Cells.Find(What:=SettingName, after:=ActiveCell).Activate
        
        'FIND THE EQUAL SIGN
        MidStartVal = InStr(InStr(1, ActiveCell, SettingName, vbTextCompare), ActiveCell, "=", vbTextCompare) + 2
        
        'FIND VALUE AFTER EQUAL SIGN
        MidValLength = InStr(InStr(InStr(1, ActiveCell, SettingName, vbTextCompare), ActiveCell, "=", vbTextCompare) + 2, ActiveCell, " ", vbTextCompare) - MidStartVal
        
        If MidStartVal + MidValLength <= 0 Then
            SettingValue = "ERROR"
        Else
            SettingValue = Mid(ActiveCell, MidStartVal, MidValLength)
        End If
        
        Range(OriginalName).Value = SettingValue
        SettingValue = ""
    Next CellName

Hello kingweezerdude and welcome to the forum.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here