Trouble Extracting A String

[Brad24]

Hi, is there an easy way to extract a name from the following string?

The string has the following format. I just want James Bond out of this string.

James Bond J. Bond

 

[Rick Rothstein]

Try this formula to place the desired value (not the formula) directly in the cell...

[table="width: 500"]
[tr]
	[td]Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("O1").Value = Left(Range("A457").Value, InStrRev(Left(Range("A457").Value,Len(Range("A457").Value)-1), ".") - 3)[/td]
[/tr]
[/table]

 

[Brad24]

I spot checked and it works. Can you tell me why these Ranges aren't correct?

For j = StartRow + 3 To EndRow + 2 Step 3
    Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("N" & j).Value = Left(Range("A" & j).Value, InStrRev(Left(Range("A" & j).Value, Len(Range("A" & j).Value) - 1), ".") - 3)
Next j

 

[Rick Rothstein]

I spot checked and it works. Can you tell me why these Ranges aren't correct?

For j = StartRow + 3 To EndRow + 2 Step 3
    Workbooks(MyWorkbook).Sheets(MyWorksheet).Range("N" & j).Value = Left(Range("A" & j).Value, InStrRev(Left(Range("A" & j).Value, Len(Range("A" & j).Value) - 1), ".") - 3)
Next j

What do you mean by "aren't correct"? What is happening... or not happening... that makes you ask that question?

 

[Brad24]

I figured it out. It was looking at the rows that had the number sign like #12 etc, and was failing instead of the rows with the names in them.

Thanks for your help! I appreciate you staying with this one.