Adding a symbol to the end of a string in each cell within a column

[j4ttlife]

Hey guys, I was wondering if it's possible to add a symbol to each cell after the last character?

For example, my cells are currently in the format in the first picture.

I was hoping to add a * symbol in each cell at the end as in picture 2.

Thanks.

 

[j4ttlife]

Forgot to mention that my spreadsheet is macro enabled. I'm okay with VBA but far from an expert. Was hoping to get some sort of an idea of how to code my request into the VB editor.

 

[Joe4]

Welcome to the Board!

Here is some VBA code that will create an interactive experience, where you can select the column you want to apply it to, and the character you want to add to the end:

Sub MyInsertMacro()

    Dim col As String
    Dim cr As String
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Prompt to ask which column to amend
    col = InputBox("Which column letter would you like to add character to?")
    
'   Prompt for character to add
    cr = InputBox("What character would you like to add to the end of each entry?")
    
'   Find last row with data in selected column
    lr = Cells(Rows.Count, col).End(xlUp).Row
    
'   Loop through all rows in selected column, starting on row 2
    For r = 2 To lr
'       If entry is not blank, add character to end
        If Cells(r, col) <> "" Then Cells(r, col) = Cells(r, col) & cr
    Next r
    
    Application.ScreenUpdating = False
    
End Sub

 

[Fluff]

Will it always be columns A & B?

 

[j4ttlife]

Will it always be columns A & B?

Yes, the columns will always be A and B.

 

[Joe4]

Yes, the columns will always be A and B.

Do you not like the interactive code I provided?
Do you want it to be hard-coded to ALWAYS use columns A and B, and ALWAYS use a "*"?
Will columns A and B ALWAYS end on the same row?

 

[Fluff]

In that case, another option

Sub j4ttlife()
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@<>"""",@&""*"")", "@", .Address))
   End With
End Sub

 

[j4ttlife]

Do you not like the interactive code I provided?
Do you want it to be hard-coded to ALWAYS use columns A and B, and ALWAYS use a "*"?
Will columns A and B ALWAYS end on the same row?

Hi, I appreciate the answer you gave but I didn't need my code to be interactive. I have used the code provided by Fluff in another reply which works perfectly. Thanks for the help.

 

[j4ttlife]

In that case, another option

Sub j4ttlife()
   With Range("A2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@<>"""",@&""*"")", "@", .Address))
   End With
End Sub

This works perfectly, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.