从多行字符串中提取文本，例如键值字符串

I have a cell text in Excel with like the following

我在Excel中有一个单元格文本,如下所示

Cell A1:

Key1: Value1
Key2: Value2
 Key3: Value3
 Key Name4: Value4
Key5: Value5

Now I would like to extract Value4 given that i know its key is Key Name4, any suggestion using Excel formulas?

现在我想提取Value4,因为我知道它的关键是Key Name4,任何使用Excel公式的建议?

PS: there is a space before my interested key

PS:我感兴趣的钥匙前有一个空格

4 个解决方案

#1

Here is another using SEARCH which is case insensitive:

这是另一个使用不区分大小写的SEARCH:

=TRIM(MID(A1,SEARCH(B2,A1)+LEN(B2)+1,IFERROR(FIND(CHAR(10),A1,SEARCH(B2,A1))-SEARCH(B2,A1)-LEN(B2),LEN(A1))))

#2

Assuming that the key value you are searching for is in B4 the following should work:

假设您要搜索的键值在B4中,则以下内容应该有效:

=MID(A1,FIND(B4,A1)+LEN(B4)+2,IFERROR(FIND(CHAR(10),MID(A1,FIND(B4,A1)+LEN(B4)+2,LEN(A1))),LEN(A1)))

#3

With data in A1, in B1 enter:

使用A1中的数据,在B1中输入:

=LEFT(MID(A1,FIND("Key Name4: ",A1)+10,9999),FIND("Key5",MID(A1,FIND("Key Name4: ",A1)+10,9999))-1)

#4

This is how my VBA solution would work:

这就是我的VBA解决方案的工作方式:

That is the code:

那是代码:

Option Explicit

Public Function TakeMeAValue(rngRange As Range, lngCounter As Long)

    Application.Volatile
    Dim lngStart        As Long
    Dim lngEnd          As Long
    Dim strWord         As String: strWord = "Key"
    Dim strWordToSearch As String

    On Error GoTo TakeMeAValue_Error

    strWordToSearch = CStr(strWord & lngCounter & ":")
    lngStart = InStr(1, rngRange, strWordToSearch) + Len(strWordToSearch)
    lngEnd = InStr(lngStart, rngRange, strWord & lngCounter + 1)

    If lngEnd = 0 Then
        lngEnd = Len(rngRange) + 1
    End If

    TakeMeAValue = Trim(Mid(rngRange, lngStart, (lngEnd - lngStart)))

    Exit Function

TakeMeAValue_Error:
    TakeMeAValue = err.Description

End Function

#1