Solved

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

Posted on 2014-10-21
8
624 Views
Last Modified: 2014-10-22
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
0
Comment
Question by:kbay808
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40396228
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
 
LVL 12

Expert Comment

by:jkaios
ID: 40396243
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
 
LVL 40

Expert Comment

by:als315
ID: 40396259
Look at sample with universal functions
Example.xlsm
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40396262
@  jkaios
Your solution does not take into account the variable length of names
0
 

Author Comment

by:kbay808
ID: 40396372
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
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40396396
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 250 total points
ID: 40396565
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
 

Author Closing Comment

by:kbay808
ID: 40397186
Both of your solutions worked great.  Thank you very much.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 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