inStrRev

[Wimpieoh]

Cel K14 contains 'johan van der sterren'.

Sub LastName()
    name = [K14].Value
    MsgBox name                         ' shows johan van der sterren
    spot = InStrRev(name, " ")
    MsgBox spot                          ' shows 22 but it schould be 14; find the last space and count from the beginning
    MsgBox Mid(name, spot)      ' shows space
End Sub

When I put the name hardcoded everything works fine

Sub LastName()
    name = "johan van der sterren"
    MsgBox name                         '  shows johan van der sterren
    spot = InStrRev(name, " ")
    MsgBox spot                          ' shows 14; OK
    MsgBox Mid(name, spot)      ' shows sterren
End Sub

Anyone an idea why the first code goes wrong? Thanks and best regards

 

[Alex Blakenburg]

What happens if you replace your Name = line with this one:

    Name = Replace([K14], ChrW(160), "")

 

[Peter_SSs]

Welcome to the MrExcel board!

If Alex's suggestion does not solve it, what happens if you use this for the Name line?

Name = Trim([K14].Value)

BTW, 'Name' is not a good as a variable name as 'Name' is already used in the vba language. Such double usage may cause unexpected errors in vba code.

 

[Wimpieoh]

What happens if you replace your Name = line with this one:

    Name = Replace([K14], ChrW(160), "")

This workaround resulted also in 22 and space.

 

[Alex Blakenburg]

Thanks for the feedback. Glad Peter's solution worked for you.