Solved

Search multiple lines

Posted on 2016-10-13
3
34 Views
Last Modified: 2016-10-17
Is it possible to search a cell for a particular line and put it in another cell.

For example
=findLine(A2,"Short Results:")

Open in new window


Where in A2 holds:-

SUMMARY
------------------------------------------------------------------
Short Results: Nothing to be done
Operation completed at 18:29:33 on 12/10/2016
Total duration: 00:29:30
------------------------------------------------------------------

And the result of the fomula will be:-
Nothing to be done

If there isnt then Ill make my own function in VBA, just thought Id ask
0
Comment
Question by:tonelm54
3 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41841839
Hi,

pls try
Function FindLine(rng As Range, strText As String) As String
For Each Ln In Split(rng.Value, Chr(10))
    If Trim(Ln) Like strText & "*" Then
        Res = Trim(Mid(Trim(Ln), Len(strText) + 1))
        Exit For
    End If
Next
FindLine = Res
End Function

Open in new window

Regards
0
 
LVL 23

Expert Comment

by:NBVC
ID: 41841847
Perhaps something like:

=TRIM(IF(ISNUMBER(FIND("Short Results:",A2)),MID(A2,LEN("Short Results:")+1,FIND(CHAR(10),A2)-LEN("Short Results:")),""))
1
 
LVL 23

Expert Comment

by:Danny Child
ID: 41843462
Do you just want to check a bunch of cells that contain multiple lines for the "Short Results:" text entry, and if found, display "Nothing to be done" ?

if so, try this:
=IF(FIND("Short Results:",A2)>0,"Nothing to be done","")
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now