Repalce with Superscript

[Legacy 116183]

I hope this is simple because I am.

I wish to replace a column of the phrase Lanc with the phrase Lancs where the final s is supeerscript.

I understand that I must use a macro to do this as Excel won't do it, but as I have never done one before ever, can someone please tell me what to do ?

Thanks

 

[brettdj]

Note you can do it manually by hightlighting just the "s" in the formula bar, then
Format ... Cells .. ticking 'superscript'

If you still need VBA:

1) is Lanc the only text in the cells, or can it be part of a larger string?
2) is Lanc only in some cells of the column?

Cheers

Dave

 

[Legacy 116183]

Thanks for the reply, I know that I can do it via the formula bar but there are 300 entries in the column.

There columns all begin Lanc then a space then various other letters which are different in each column

Thanks

 

[brettdj]

You could try this for Column A - change the "A" references in this line for a different column
Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))

Normally I'd use an array for a read write, but this needs a cell loop to handle the formatting entry

Cheers

Dave

Sub Macro1()
    Dim rng1
    Dim c
    Set rng1 = Range([A1], Cells(Rows.Count, "A").End(xlUp))
    For Each c In rng1.Cells
        If Left$(c.Value, 5) = "Lanc " Then
            c.Value = "Lancs " & Right$(c.Value, Len(c.Value) - 5)
            c.Characters(Start:=5, Length:=1).Font.Superscript = True
        End If
    Next c
End Sub

 

[Legacy 116183]

Thanks, I'll give it a shot