VBA code: copy string from right up to first space

[thedogg]

I am looking for the code which will extract the string from right side of the worksheet name up to first space in entire string and paste it in cell B11.

Example of the string (worksheet name) : my sheet.1 - 31111111C --------> result in cell B11 should be 31111111C
for that I have code below and it works:

targetWorkbook.Worksheets(ii).Range("B11").value = Right(TargetSheet.name, 9)

but if there is more than 9 digits from the right side up to the first space I have no idea.
but it doesnt work in that case

targetWorkbook.Worksheets(ii).Range("B11").value = Right(TargetSheet.name, InStr(TargetSheet.name, " - "))

I need a code who will works for all casses, doesnt mater how many digits I will have from the right side up to first space.

 

[Fluff]

Try

Right(TargetSheet.Name, Len(TargetSheet.Name) - InStrRev(TargetSheet.Name, " "))

 

[Peter_SSs]

Another option:

targetWorkbook.Worksheets(ii).Range("B11").Value = Split(TargetSheet.Name)(UBound(Split(TargetSheet.Name)))