Extract tect from cell

[sharky12345]

Is there a way I can extract the word(s) after a specific word from a cell?

The contents of the cell are on numerous lines, so I would need something that searched the cell, finds the phrase 'INDEX:' and then grabs the word(s) after that, but on the same line only, and puts it/them into cell G4.

Anyone?

 

[sharky12345]

Rick - got it to extract what I need but I need to remove the time element, leaving only the date if you can help please?

 

[Rick Rothstein]

Rick - got it to extract what I need but I need to remove the time element, leaving only the date if you can help please?

Make the change I show in red below (it will make the function retrieve the first "word" instead of the all the text after the colon)...

Function IndexWords(S As String) As String
  Dim Words() As String
  Words = Split(S, "Claim created on: ", 2)
  If UBound(Words) = 1 Then IndexWords = [COLOR=#FF0000][B]Split([/B][/COLOR]Application.Trim(Split(Words(1), vbLf)(0))[COLOR=#FF0000][B])(0)[/B][/COLOR]
End Function

 

[sharky12345]

Genius - thank you so much!

 

[sharky12345]

OK, new issues to deal with now - I want to test if the cell M2 contains just text, i.e.

HADS-

or text and numbers, i.e.

HADS-001234

A simple Msgbox advising which will suffice if anyone can assist?

 

[Rick Rothstein]

OK, new issues to deal with now - I want to test if the cell M2 contains just text, i.e.

or text and numbers, i.e.

A simple Msgbox advising which will suffice if anyone can assist?

Try this code snippet in your own code...

If Len(Range("M2")) = 0 Then
  MsgBox "Cell M2 is empty."
ElseIf Not Range("M2").Value Like "*[!0-9]*" Then
  MsgBox "Cell M2 consists of digits only."
ElseIf Range("M2").Value Like "*#*" Then
  MsgBox "Cell M2 consist of a mixture of text and digits."
Else
  MsgBox "Cell M2 has no digits in it at all."
End If