partial vlookup and auto update the incomplete word that contains "-"

[amrans20]

Hello, I've search threads with tags autocomplete and vlookup but haven't found some similarities with what I need, so hopefully someone can help. this is my 2nd post, please go gentle on me if this question has already been asked.

What I would like to find is a way for auto updating a part of the text in a cell with list of the complete version of that text. The text always contains a dash "-".

example (column A)		list (column C)
Swap from KGLR-EN1 to KGL-EN1-TRT		TSS-KGLR-EN1-TRTT
Migrate from TGLR-en1 and KGL-pn1 to KGL-EN1-TRT		TSS-KGL-EN1-TRT
		TGLR-en1-TRs2
		TSS-KGL-pn1-TR8
		TSS-KGL-EN1-TRT

i'm hoping that i could get a formula or VBA code that would get:

Desired Result (column B)

Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT

Migrate from TGLR-en1-TRs2 and TSS-KGL-pn1-TR8 to TSS-KGL-EN1-TRT

Any help would be much appreciated. Thank you for your time. ?

 

[DanteAmor]

Try this UDF

Function CompleteWords(sText As String, list As Range)
  Dim t As Variant, cad As String, f As Range, newText As Variant
  For Each t In Split(sText, " ")
    newText = t
    If InStr(1, t, "-") > 0 Then
      Set f = list.Find(t, , xlValues, xlPart)
      If Not f Is Nothing Then newText = f.Value
    End If
    cad = cad & newText & " "
  Next
  CompleteWords = Trim(cad)
End Function

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CompleteWords just like it was a built-in Excel function. For example:

Dante Amor
	A	B	C
1	EXAMPLE	Desired Result	LIST
2	Swap from KGLR-EN1 to KGL-EN1-TRT	Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT
3	Migrate from TGLR-en1 and KGL-pn1 to KGL-EN1-TRT	Migrate from TGLR-en1-TRs2 and TSS-KGL-pn1-TR8 to TSS-KGL-EN1-TRT	TSS-KGL-EN1-TRT
4			TGLR-en1-TRs2
5			TSS-KGL-pn1-TR8
6			TSS-KGL-EN1-TRT
Sh

Cell Formulas
Range	Formula
B2:B3	B2	=CompleteWords(A2,$C$2:$C$6)

 

[amrans20]

Try this UDF

Function CompleteWords(sText As String, list As Range)
  Dim t As Variant, cad As String, f As Range, newText As Variant
  For Each t In Split(sText, " ")
    newText = t
    If InStr(1, t, "-") > 0 Then
      Set f = list.Find(t, , xlValues, xlPart)
      If Not f Is Nothing Then newText = f.Value
    End If
    cad = cad & newText & " "
  Next
  CompleteWords = Trim(cad)
End Function

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CompleteWords just like it was a built-in Excel function. For example:

Dante Amor
	A	B	C
1	EXAMPLE	Desired Result	LIST
2	Swap from KGLR-EN1 to KGL-EN1-TRT	Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT
3	Migrate from TGLR-en1 and KGL-pn1 to KGL-EN1-TRT	Migrate from TGLR-en1-TRs2 and TSS-KGL-pn1-TR8 to TSS-KGL-EN1-TRT	TSS-KGL-EN1-TRT
4			TGLR-en1-TRs2
5			TSS-KGL-pn1-TR8
6			TSS-KGL-EN1-TRT
Sh

Cell Formulas
Range	Formula
B2:B3	B2	=CompleteWords(A2,$C$2:$C$6)

Hello DanteAmor,

That's awesome, the code works very well. thank you so much for helping me ?

Best Regards,
Amran

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.