Extracting the 2nd Character From the Right in a String

[Philip1957]

Greetings,

I have 3 and 4 character strings (column E) and I need to extract the second character from the right and place it in another cell (column O).
My code is giving me the last 2 characters. Is there a way to get just the second character or do I have to run a second macro to separate them?

I also need this to step & repeat through the selected range in column O but I haven't started that yet.

Option Explicit

Sub ExtractLocCode()

Application.ScreenUpdating = False


Dim CodeString
CodeString = ActiveCell.Offset(0, -10).Value

Dim LocCode
LocCode = Right(CodeString, 2)     ' Returns Loc code.

ActiveCell.Value = LocCode


Application.ScreenUpdating = True


End Sub 'ExtractLocCode

Thanks in advance for any assistance,
~ Phil

 

[Joe4]

This is the line that is returning the last 2.

LocCode = Right(CodeString, 2)

So just take the first character of that, by updating that line to:

LocCode = Left(Right(CodeString, 2),1)

 

[Georgiboy]

You can also use the MID and LEN functions to achieve this.

Secondly you can avoid a loop by evaluating a formula using MID and LEN functions. The formula to do this might be:

=MID(E2:E9,LEN(E2:E9)-1,1)

You can evaluate an entire range in Excel 365 using something like the below, which is basically the formula above but run on a range using VBA:

Sub test()
    Dim rng As Range, var As Variant
   
    Set rng = Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row) ' Range of 3 and 4 character strings
   
    var = Evaluate("MID(" & rng.Address & ",LEN(" & rng.Address & ")-1,1)") ' Evaluate the formula using the above range
    Range("O2").Resize(UBound(var), 1) = var ' Output the evaluated result to a cell, in this case cell O2
End Sub

 

[Philip1957]

Georgiboy,​

Thank you very much. Way more of a response than I was hoping for. I'll try these out right away.

 

[Philip1957]

Wow. Thank you again!

~ Phil