Structured table formula to separate text string after a hyphen

46 views Asked by At

I am trying to find a oneliner VBA code that will use structured table reference to edit values in a column given as "United States of Amercia - USA". The aim is to delete everything before the hyphen (-) and only the "USA" part left. The code so far I have written is as follows:

Dim Rng As Range
Dim Cell As Range
Set Rng = Range("CNSTime[[#Data],[Country]]")
    For Each Cell In Rng
    Cell.Value = Right(Cell, Len(Cell) - InStr(Cell, "-") - 1)
Next Cell
End

Anyone who can help replace this code with a one liner structured formula for example:

Range("CNSTime[[#Data],[Country]]").formula = xxxxxxxxxxxxxx

My table name is CNSTime Set CNSTime = ActiveSheet.ListObjects("CNSTime") and the header of the column I am trying to fix is Country.

0

There are 0 answers