Macro for extracting text from one cell and entering it into cell beside

Welcome to the forum!

Insert a Module and paste this in the Visual Basic Editor. Use it as commented. Notice that the 2nd parameter is the word number to pick and the 3rd parameter allows it to strip trailing characters.

'=getword(A1,2,","&CHAR(34)&CHAR(39)&".?!")
Function GetWord(cell As Range, wordNumber As Integer, Optional stripEndChars As String) As String
  Dim a, s As String, i As Integer
  
  a = Split(cell.Value, " ")
  If wordNumber > (UBound(a) + 1) Then
    GetWord = ""
    Exit Function
  End If
  
  s = a(wordNumber - 1)
  GetWord = s
  If stripEndChars = vbNullString Then Exit Function
  
  For i = 1 To Len(stripEndChars)
    If Right(s, 1) = Mid(stripEndChars, i, 1) Then s = Left(s, Len(s) - 1)
  Next i
  GetWord = s
End Function

A better solution would use regular expressions.

Hi Kenneth

Thanks for your reply. Actually I should have been more specific about what i want.

I have a list of names on one sheet - i want to match these names to the long descriptions on another sheet - so i need the long descriptions to look through the list of names on the first sheet and bring back the relevant name in the cell next to it.

I think the macro you kindly provided can only look for one name at a time? not sure?

Appreciate any more hoel you can give on this one.

Thanks
John

Hi again

Thought I'd attach an example file to make the situation clearer - hope this helps.

it shows the target list on the sheet "List" and these targets need to be populated into the yellow highlighted column on the second sheet ("Data").

Thanks again
John

hi - this formula gives me #N/A values

i tried holding Ctrl-Shift-Enter on this column B after dragging the formula but nothing changed, still #N/A values

any ideas?

Thank you! I had forgot to put the {} around the formula - thanks again!