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
546 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 51

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
To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

 
LVL 51

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 51

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel - Summing a column based on criteria from another column 5 50
Clicking Visio for more details 4 31
Missing Folder in outlook 2013 6 36
Office 365 Spam 3 35
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

710 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