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
1	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
2
3	Look For:		Found in Text?
4	G13-4577S		Yes
5	G10-7050L		Yes
6	abc		No

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C4 =IFERROR(IF(FIND(A4,$A$1)>0,"Yes","No"),"No") <tbody> </tbody>

<tbody>
</tbody>

 

[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
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

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

sheet1​

worksheet formulas cell formula b1 =getvehiclecodes(a1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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