How to extract a string from a cell that contains multiple lines of data by either using formulas or by VBA?

I have 3 tasks that I’m trying to accomplish by extracting data from the source data found in cell A2.

Source data (The source data does not always look the same.  I have 3 examples of source data in the example file)

Code: GNJD
Site: CAL
Name: John Doe
Phone: 619-123-4567
Description: My computer will not boot
Computer Name: CALDDXW145
Tech Name: Mark Myers

Tasks
1.      Extract the name to cell B5 ("Name:" will always be first in the line)
2.      Extract the 4th through 7th characters in the computer name to cell B6 ("Computer Name:" will always be first in the line)
3.      Extract the tech’s name to cell B7 (Search the source data for any names listed in range "Techs!A:A")




Example of output data
John Doe
DDXW
Mark Myers
Example.xlsx
kbay808Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub macro()

aLines = Split(Range("A2"), vbLf)
aTmp = Filter(aLines, "Name: ")
Range("B5") = Split(aTmp(0), ": ")(1)
aTmp = Filter(aLines, "Computer Name: ")
Range("B6") = Mid(Split(aTmp(0), ": ")(1), 4, 4)
aTmp = Filter(aLines, "Tech Name: ")
Range("B7") = Split(aTmp(0), ": ")(1)

End Sub

Open in new window

Regards
0
jkaiosIT DirectorCommented:
Try these built-in formulas:

In cell B5:
  =TRIM(MID(A2, FIND("Name:",A2)+5,10))

In cell B6:
  =TRIM(MID(A2, FIND("Computer Name:",A2)+14+4,4))

In cell B7:
  =TRIM(MID(A2, FIND("Tech Name:",A2)+10,20))
0
als315Commented:
Look at sample with universal functions
Example.xlsm
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rgonzo1971Commented:
@  jkaios
Your solution does not take into account the variable length of names
0
kbay808Author Commented:
All,
Please see below for my notes on everyone’s individual solution.  I also uploaded a new example file named “Example Updated” with the different data source examples on a drop down menu to making testing easier.  I also included an additional example to account for the issues that all of you have exposed.  I need a single solution that will work for all of the different examples.  The examples represent the variances in the data that is being worked with.

Expert: als315
Your solution seems to be working for the name, but not the other 2.  It’s extracting the whole computer name (CALDDXW145), but I need 4th-7th characters on the name (DDXW).  And for the tech name, it’s not matching the name with the list on the “Techs” sheet.  If I delete the name from the list the name still populates.  It shouldn’t populate unless there is an exact match.  Also, you provided 3 different solutions.  One for each of the examples, but I need a single solution that will work for all 3.

Expert: jkaios
Your solution is not going to work.  The functions do not take in account the length of the data or the number of spaces or the lack of a space between the “:” and the data.  Also, the tech name is not be matched to the list of techs on the “Techs” sheet.

Expert: Rgonzo1971
Your solution works well for the name and the computer name, but only for the first example.  The tech name is not being matched with the list of techs on the “Techs” sheet.
Example-Updated.xlsx
0
Rgonzo1971Commented:
Hi,

pls try

Sub macro()

aLines = Split(Range("A2"), vbLf)
strTmp = Filter(aLines, "Name:")(0)
Range("B5") = Trim(Split(strTmp, ":")(1))
strTmp = Filter(aLines, "Computer Name:")(0)
Range("B6") = Mid(Trim(Split(strTmp, ":")(1)), 4, 4)
strTmp = ""
For Each c In Sheets("Techs").Range(Sheets("Techs").Range("A2"), Sheets("Techs").Range("A" & Rows.Count).End(xlUp))
    If InStr(1, Range("A2"), c) Then strTmp = c.Value
Next
Range("B7") = strTmp
End Sub

Open in new window

EDIT Corrected code

Regards
0
krishnakrkcCommented:
UDF

Function ExtractName(InputValue As Variant, ByVal ValueType As String) As String
    
    If TypeName(InputValue) = "Range" Then
        InputValue = InputValue.Cells(1).Value2
    End If
    On Error GoTo Xit
    ExtractName = Trim(Split(Split(InputValue, ValueType & ":", , 1)(1), vbLf)(0))
    Exit Function
Xit:
    Err.Clear: ExtractName = xlErrRef
    
End Function

Open in new window


B5: =ExtractName(A2,"name")
B6: =MID(ExtractName(A2,"Computer name"),4,4)
B7: =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Techs!A2:A5,A2))),LOOKUP(9.9999E+307,SEARCH(Techs!A2:A5,A2),Techs!A2:A5),"")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
Both of your solutions worked great.  Thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.