Extract three sets of numbers from one cell

Re: Extract three sets of numbers from one cell

It would be best if you uploaded a sample workbook that shows several examples of the data so we can get an idea of the consistency and variation of the strings.
Will the numbers you need always be preceeded by an uppercase letter followed by the equal sign? If so, this should be fairly easy.

Re: Extract three sets of numbers from one cell

I think I understand what you are trying to do. From your example, I put the "B = 6742 F = 3027" into cell A8. I then put the following formulas into cell B8 and C8 respectively.

B8: =MID(A8,FIND("=",A8,1)+2,FIND(" ",A8,FIND("=",A8,1)+2)-FIND("=",A8,1)-2)
C8: =MID(A8,FIND("=",A8,FIND("=",A8,1)+1)+1,LEN(A8)-FIND("=",A8,FIND("=",A8,1)+1)+1)

Simply copy the formulas for the 300+ rows that you have.

Essentially the first formula extracts the text between the first "=" and the space after the number. The second formula extracts the text after the second "=". Note that this formula assumes that you have a "= ' format in your source text.

I have attached as sample that also shows how to convert it to a number (that you can do math on).

Hope that helps.

Re: Extract three sets of numbers from one cell

05125668,

Please DO NOT quote entire posts when replying as it clutters up the thread. If you need to quote just use the specific part of the post that is relevant to your reply. It will make the thread easier to follow. Thanks.

Re: Extract three sets of numbers from one cell

Hi Dan

Given the complexity of your data extract requirements I think that using an excel formula will exceedingly difficult (particularly if you want to avoid going blind reviewing the syntax). I think this is a good example for a user-defined-function (UDF). I am not sure your level of experience with UDF or visual basic. I developed a UDF that will allow you to pass all the different variations of the values you are looking for (i.e. "BLACK", "BLACK:", "BLACK " or "BLACK -". The function will return the associated value. You can use this function for the other lookups (like TOTAL, COLOUR, B/W etc.).

To make life easier I created the UDF and included it in the attached sample file (that was your original post). It illustrates how to use the function.

NOTE: The function assumes that there is always at least a " " before the value we are extracting. Also, the search is case-sensitive. Also, the excel sheet now has an XLSM extension (macro enabled spreadsheet).

I hope this helps ... CF

PS In the event that you have experience with UDF, I have included the code.

Those reading this thread - don't laugh at the code - but here is the code for the UDF

Function Extract_value(search_string As String, search_vlu As Variant, Optional search_vlu2 As Variant = "", _
        Optional search_vlu3 As Variant = "", Optional search_vlu4 As Variant = "", Optional search_vlu5 As Variant = "", _
        Optional search_vlu6 As Variant = "") As Variant
        
        Dim Text_Pos As Integer
        Dim First_blank, Second_Blank As Variant
        Dim values(6, 1) As Variant
        
        values(0, 1) = search_vlu
        values(1, 1) = search_vlu2
        values(2, 1) = search_vlu3
        values(3, 1) = search_vlu4
        values(4, 1) = search_vlu5
        values(5, 1) = search_vlu6
        
        ' Make sure that the search values are sorted largest to smallest
        SortColumn1 = 0
         For i = LBound(values, 1) To UBound(values, 1) - 1
             For j = LBound(values, 1) To UBound(values, 1) - 1
                  Condition1 = values(j, SortColumn1) > values(j + 1, SortColumn1)
                  If Condition1 Then
                    For y = LBound(values, 2) To UBound(values, 2)
                        t = values(j, y)
                        values(j, y) = values(j + 1, y)
                        values(j + 1, y) = t
                        Next y
                    End If
                Next
            Next


        
        
        ' First look for the primary search value
        
        For i = LBound(values, 1) To UBound(values, 1)
            Text_Pos = InStr(search_string, values(i, 1))
            srch_str = values(i, 1)
            If Text_Pos > 1 Then Exit For
        Next
        
        
        If Text_Pos > 1 Then
            '
            ' We assume that the number will be following the first "blank" character
            ' and that number will end at the next blank (or end of string)
            '
            First_blank = InStr(Text_Pos + Len(srch_str) - 1, search_string, " ")
            Second_Blank = InStr(First_blank + Len(srch_str), search_string, " ") - 1
            If Second_Blank = -1 Then Second_Blank = Len(search_string) + 1
            Extract_value = Mid(search_string, First_blank, Second_Blank - First_blank)
        Else
            Extract_value = "None"
        
        End If
        
End Function

Re: Extract three sets of numbers from one cell

Quote from 05125668;515341

CF,
Thanks again for your help. If I have any other further questions then I will let you know.
05125668

Your welcome - anytime.