Make last word proper

[kpadventures90]

Hey everyone,

I'm not very good at writing macros, but what I'm trying to do is make the last word in a string be proper, while leaving the rest of the string as it is. The report I have does everything fine, except the last word. Unfortunately I can't just make the whole string proper.

I'm working on a database that has many thousands of entries so it would be applied to a range, rather than just one cell.

I would rather do this with a macro than formulas as this will need to be done regularly and the report has data either side of column E (The one with the string that needs updating).

I've tried to follow what I can find online, but I either just get the last word replacing the entire string or a bunch of errors I don't know how to get around. Can someone who knows what they're doing please help?

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Sub ProperEnd()
Dim Rng As Range, c As Variant, w as Variant

    Set Rng = Range("A1:A3")
   
    For Each c In Rng
        w = Split(c.Value, " ")
        w(UBound(w)) = WorksheetFunction.Proper(w(UBound(w)))
        c.Value = Join(w)
    Next c
   
End Sub

Change the range as needed.

 

[kpadventures90]

Welcome to the MrExcel forum!

Try:

Sub ProperEnd()
Dim Rng As Range, c As Variant, w as Variant

    Set Rng = Range("A1:A3")
  
    For Each c In Rng
        w = Split(c.Value, " ")
        w(UBound(w)) = WorksheetFunction.Proper(w(UBound(w)))
        c.Value = Join(w)
    Next c
  
End Sub

Change the range as needed.

Sorry for the late reply, had life stuff pop up.

That works perfectly Eric, thank you so much!

 

[Eric W]

Happy to help!