How to pull data from a string of text

[sd808]

Example data in a cell:
how can I help you 6/13/17 at 9am. its has been confirmed. Vehicles: G13-4577S G13-5465M G13-5543L G10-7050L G42-1669K
Please Install/De-Install the below vehicles: Old Vehicle: 2013 FORD FOCUS 1FADP3F2XDL375864 G13-2158M New Vehicle: 2017 FORD CMAX 1FADP5AU0HL115466 G13-2215U

I am trying to pull any data with "G13-4577S", "G10-7050L" and so on.... from a string of text but not always getting the data I need.

I"ve used the following formula but its not always pulling the above data out.

=TRIM(RIGHT(SUBSTITUTE(O3," ",REPT(" ",100)),100))

any help would be greatly appreciated.

 

[jim may]

Excel 2010

	A	B	C
Noticed you had not received any response. Just to provide something (while guessing at what you need) here's something... how can I help you 6/13/17 at 9am. its has been confirmed. Vehicles: G13-4577S G13-5465M G13-5543L G10-7050L G42-1669KPlease Install/De-Install the below vehicles: Old Vehicle: 2013 FORD FOCUS 1FADP3F2XDL375864 G13-2158M New Vehicle: 2017 FORD CMAX 1FADP5AU0HL115466 G13-2215U
Look For:	Found in Text?
G13-4577S	Yes
G10-7050L	Yes
abc	No

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IFERROR(IF(FIND(A4,$A$1)>0,"Yes","No"),"No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Not sure what format you're looking for as a result but the quickest method is to use a UDF. Use [Alt]+[F11] to bring up the VBA editor and then select Insert>Module from the menu. Paste this code:

Private regEx As Object
Public Function GetVehicleCodes(inputString As String) As String

If regEx Is Nothing Then Set regEx = CreateObject("VBScript.RegExp")

Dim match As Object

With regEx
    .Pattern = "G[0-9]{2}-[0-9]{4}[A-Z]"
    .MultiLine = True
    .Global = True
    For Each match In .Execute(inputString)
        GetVehicleCodes = GetVehicleCodes & ", " & match.Value
    Next match
End With

If GetVehicleCodes <> "" Then GetVehicleCodes = Mid$(GetVehicleCodes, 3)

End Function

Then you can use this new function to extract the codes:

Book1
	A	B
1	how can I help you 6/13/17 at 9am. its has been confirmed. Vehicles: G13-4577S G13-5465M G13-5543L G10-7050L G42-1669K	G13-4577S, G13-5465M, G13-5543L, G10-7050L, G42-1669K
2	Please Install/De-Install the below vehicles: Old Vehicle: 2013 FORD FOCUS 1FADP3F2XDL375864 G13-2158M New Vehicle: 2017 FORD CMAX 1FADP5AU0HL115466 G13-2215U	G13-2158M, G13-2215U
Sheet1

Cell Formulas
Range	Formula
B1	=GetVehicleCodes(A1)

WBD

 

[wideboydixon]

Alternatively, to extract them into separate columns:

Book1
	A	B	C	D	E	F
1	how can I help you 6/13/17 at 9am. its has been confirmed. Vehicles: G13-4577S G13-5465M G13-5543L G10-7050L G42-1669K	G13-4577S	G13-5465M	G13-5543L	G10-7050L	G42-1669K
2	Please Install/De-Install the below vehicles: Old Vehicle: 2013 FORD FOCUS 1FADP3F2XDL375864 G13-2158M New Vehicle: 2017 FORD CMAX 1FADP5AU0HL115466 G13-2215U	G13-2158M	G13-2215U
Sheet1

Cell Formulas
Range	Formula
B1	=GetVehicleCode($A1,COLUMNS($B$1:B$1))

Private regEx As Object
Public Function GetVehicleCode(inputString As String, index As Long) As String

If regEx Is Nothing Then Set regEx = CreateObject("VBScript.RegExp")

Dim matches As Object

With regEx
    .Pattern = "G[0-9]{2}-[0-9]{4}[A-Z]"
    .MultiLine = True
    .Global = True
    Set matches = .Execute(inputString)
End With

If index <= matches.Count Then GetVehicleCode = matches(index - 1).Value

End Function

WBD

 

[sd808]

Thank you wideboydixon - worked like a charm!!!

not sure what format you're looking for as a result but the quickest method is to use a udf. Use [alt]+[f11] to bring up the vba editor and then select insert>module from the menu. Paste this code:

private regex as object
public function getvehiclecodes(inputstring as string) as string

if regex is nothing then set regex = createobject("vbscript.regexp")

dim match as object

with regex
    .pattern = "g[0-9]{2}-[0-9]{4}[a-z]"
    .multiline = true
    .global = true
    for each match in .execute(inputstring)
        getvehiclecodes = getvehiclecodes & ", " & match.value
    next match
end with

if getvehiclecodes <> "" then getvehiclecodes = mid$(getvehiclecodes, 3)

end function

then you can use this new function to extract the codes:

	a	b
how can i help you 6/13/17 at 9am. Its has been confirmed. Vehicles: G13-4577s g13-5465m g13-5543l g10-7050l g42-1669k	g13-4577s, g13-5465m, g13-5543l, g10-7050l, g42-1669k
please install/de-install the below vehicles: Old vehicle: 2013 ford focus 1fadp3f2xdl375864 g13-2158m new vehicle: 2017 ford cmax 1fadp5au0hl115466 g13-2215u	g13-2158m, g13-2215u

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]1[/td]

[td="align: Center"]2[/td]

</tbody>

sheet1​

[table="width: 85%"]
<tbody>[tr]
[td]worksheet formulas[table="width: 100%"]
<thead>[tr="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]
[th="width: 10"]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]b1[/th]
[td="align: Left"]=getvehiclecodes(a1)[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]



wbd

 

[sd808]

Thank you so much. What you have provided has helped. I am stuck on the last piece with separate the vehicle numbers into its own cells.

 

[wideboydixon]

See post #4 : https://www.mrexcel.com/forum/excel...data-string-text-post4943054.html#post4943054

WBD