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
453 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
8 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:als315
Comment Utility
Look at sample with universal functions
Example.xlsm
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
@  jkaios
Your solution does not take into account the variable length of names
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kbay808
Comment Utility
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 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Both of your solutions worked great.  Thank you very much.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

771 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

12 Experts available now in Live!

Get 1:1 Help Now