Extracting a Variable Number of Letters from a Formula using VBA Code

Question

I have a spreadsheet with formulas like =+'Have-in'!$B$. In this case, I want to be able to extract simply the letter "B." I can use Mid for this. For other columns, I do have more formulas, such =+'Have-in'!$AB$. How can I only extract the letters using VBA?

Answer 1

Please, try the next function:

Function extractLetter(rng As Range) As String
    extractLetter = Split(Split(rng.Formula, "!$")(1), "$")(0)
End Function

It can be tested using such a ways:

Sub testExtrLetter()
   Debug.Print extractLetter(ActiveCell)
End Sub

You can use the next fast way to process a column and return in the next one:

Sub extractAllLetters()
  Dim sh As Worksheet, lastR As Long, arrFin, i As Long
  Const colLett As String = "G" 'use here the letter of the column to be processed
  
  Set sh = ActiveSheet
  lastR = sh.Range(colLett & sh.rows.count).End(xlUp).row 'last row in the processed column
  
  ReDim arrFin(1 To lastR, 1 To 1) 'ReDim the array to receive the processed result
  For i = 1 To lastR
        arrFin(i, 1) = extractLetter(sh.cells(i, colLett))
  Next i
  
  'Drop the final array (arrFin) content, at once:
  sh.Range(colLett & 1).Offset(0, 1).Resize(UBound(arrFin), 1).Value2 = arrFin
End Sub