Formula to remove all characters after the last space in a string.

[saundej]

Hi,

I'm trying to find a formula which will remove all characters to the right of the last space in a string. I've found:

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

which seems to do the trick, however I'd like to add some extra intelligence to it so that it only applies if the string contains 25 characters or more.

Does anybody have any ideas?

 

[Firefly2012]

Hi

Perhaps a simple modification like:

=IF(LEN(A1)>=25,=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),A1)

So if the length of A1 is less than 25 characters, A1 is returned.

 

[saundej]

Hi,

Many thanks for your quick response.

I've tried that but get an error, it doesn't seem to like the comma after >=25 for some reason.

 

[Firefly2012]

Sorry, i left the = sign in by mistake:

=IF(LEN(A1)>=25,LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),A1)

 

[saundej]

That's great - it works perfectly.

Many Thanks

 

[Sektor]

UDF

Function RemoveCharsBeforeLastSpace(cell As Range) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\s+\w+$"
        RemoveCharsBeforeLastSpace = .Replace(cell, "")
    End With
End Function