Results 1 to 3 of 3

find string within a string

Threaded View

  1. #1
    Registered User
    Join Date
    08-26-2011
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation 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
    Last edited by kingweezerdude; 08-26-2011 at 12:43 PM.

Thread Information

Users Browsing this Thread

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

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