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
484 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 49

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 39

Expert Comment

by:als315
ID: 40396259
Look at sample with universal functions
Example.xlsm
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 49

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 49

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

825 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