Search field in Excel with delimited text

Question

I am having a data in such format (DELIMITED TEXT)

How do I Vlookup "F88" to get 1 dynamic value.

Thanks a ton

You have "DELIMITED TEXT" in a single cell. Like literally you have `'F88','66','149','','','53','13','45','11','92','43','','','' ` in one cell? And you want to look something up in that delimited text? This is entirely unclear what your SPREADSHEET looks like and what you are trying to do. — JNevill, Mar 21 '18 at 14:05
Why value `66` is returned when you input `F86`? Why not `149`? What is the logic here. What if I input `45`, would I get `11` back? — JNevill, Mar 21 '18 at 14:07

Gary's Student · Answer 1 · 2018-03-21T14:53:01.497

0

With your posted data in A1, give this a try:

Sub DataGrsbber()
    Dim s As String
    s = Replace([A1], "'", "")
    ary = Split(s, ",")
    For i = LBound(ary) To UBound(ary) - 1
        If ary(i) = "F88" Then
            MsgBox ary(i + 1)
            Exit Sub
        End If
    Next i
End Sub

EDIT#1:

To retrieve the value into a cell, try this User Defined Function:

Public Function Retriever(BigString As String, LittleString As String) As String
    Dim s As String
    s = Replace(BigString, "'", "")
    ary = Split(s, ",")
    For i = LBound(ary) To UBound(ary) - 1
        If ary(i) = LittleString Then
            Retriever = ary(i + 1)
            Exit Function
        End If
    Next i
    Retriever = ""
End Function

edited Mar 21 '18 at 14:53

answered Mar 21 '18 at 14:12

Gary's Student

95,722
10
59
99

This is helpful, but @Gary how do I take a dynamic value, and instead of msgbox paste it on a cell. I am a newbie on VBA Thanks. – Sadsamosa Mar 21 '18 at 14:28
@Sadsamosa See my **EDIT#1** – Gary's Student Mar 21 '18 at 14:53
Thanks, this is helped me a lot now – Sadsamosa Mar 22 '18 at 05:59
Hi, @Gary how do I take the whole column.for delimited text. – Sadsamosa Mar 22 '18 at 10:42

Search field in Excel with delimited text

1 Answers1