Find Last Occurence Of Space In String?

[indi visual]

How do I find the last space in a string, and replace it with "{" ?

 

[Luke M]

=SUBSTITUTE(A2," ","{",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

 

[indi visual]

Luke, am I close?


Sub Add_Brace2LastWord()

Dim Cell As Range

Dim MyString As String

Dim FindLastBrace As String


For Each Cell In Range("A1", Range("A65536").End(xlUp))

MyString = Cell.Value


FindLastBrace = Mid(Cell, Len(Cell) - 1, 1)


If FindLastBrace Like "}" Then

MyString = WorksheetFunction.Substitute(MyString, " ", " {", Len(MyString) - Len(WorksheetFunction.Substitute(MyString, " ", "")))


Cell.Value = MyString

End If

Next Cell

End Sub

 

[Luke M]

Pretty close. You can save a few steps by not checking for a space by using a "resume next on error" for the substitution.

Sub Add_Brace2LastWord()

Dim Cell As Range

Dim MyString As String


For Each Cell In Range("A1", Range("A65536").End(xlUp))

MyString = Cell.Value

On Error Resume Next

MyString = WorksheetFunction.Substitute(MyString, " ", "}", Len(MyString) _

- Len(WorksheetFunction.Substitute(MyString, " ", "")))

On Error GoTo 0

Cell.Value = MyString

Next

End Sub

 

[indi visual]

Absolutely fab.


But it throws the brace "{" on all last spaces in every string.

The "{" is backwards in the code above but that's ok ; )


However, do I tweak it so it only places it on strings that end with "}"


I was trying to use this below but it doesn't work.


FindLastBrace = Mid(Cell, Len(Cell) - 1, 1)


If FindLastBrace Like "}" Then

''run the code you just helped me with

 

[Luke M]

Gotcha. Perhaps this?

If Right(Cell,1) = "}" Then

'rest of code

 

[indi visual]

Perhaps Fantastic.


Awesome thx Luke.


Resolved.