A recent thread at Eng-Tips asked the following apparently simple question:

Starting with a string consisting of numbers with a single central group of letters, how can this string be truncated at the end of the letters, so that for instance 3L481 becomes 3L?

This prompted a lengthy discussion, with many twists and turns, and even some useful answers to the original question.

The original question was looking for an on-sheet solution, rather than VBA, and the first working formula is shown below (all 420 characters of it):

A much more practical alternative (in my opinion) is a VBA user defined function (UDF), which requires just the one argument of the cell address. Two examples are shown below:

Public Function LeftToString(InpStr As String) As String
'
'  Removes trailing digits from a string that comprises
'  a mixture of UPPERCASE letters and digits.
'
'  Elaborations might be required for strings that contain:
'      Lower case letters
'      Only letters
'      Characters that are neither digits not letters.
'
Dim L As Long           'Length of input string
Dim i As Long           'General purpose integer

L = Len(InpStr)
If L <= 0 Then
    LeftToString = ""
    Exit Function
End If
'
'  Loop backwards from the input string's end until hit an uppercase letter.
'
For i = L To 1 Step -1
   If UCase(Mid(InpStr, i, 1)) >= "A" And UCase(Mid(InpStr, i, 1)) <= "Z" Then
        LeftToString = Left(InpStr, i)
        Exit Function
    End If
Next i
'
' Input string contains no letters.
'
LeftToString = InpStr
End Function

And a shorter version:

Function LeftToString2(X As String) As String
Dim i As Long

For i = Len(X) To 1 Step -1
    If (InStr("0123456789", Mid(X, i, 1))) = 0 Then
        LeftToString2 = Left(X, i)
        Exit Function
    End If
Next i
End Function

Results of the two UDFs are shown in the screen-shot above. Note that the first UDF checks for upper-case text, between A and Z, so the extracted character must be converted to upper case. The second avoids the problem by checking if the character is a number.

A much shorter on-sheet formula was then supplied:

Even with the shorter formula, it is not immediately obvious how it works, so I have split it up into its constituent parts. With the input string in Cell B30:

• =RIGHT(B30,ROW(INDIRECT(“1:”&LEN(B30)))) returns an array of progressively longer strings, starting from the right hand end.
• =VALUE() converts each member of that array either into a a number or #VALUE!.
• =ISNUMBER() returns TRUE or FALSE for each of those values.
• =SUM(–array) or =SUM(array*1) returns the number of TRUE values. The — or *1 operators are required for the SUM function to treat TRUE as a value of 1, otherwise the SUM will always return 0
• Finally =LEFT(B30, LEN(B30)-K30) extracts the string up to the last text character.

In the latest version of Excel with “dynamic arrays” the second function will work when entered with the enter key, as usual. In older versions it must be entered as an array function, by pressing Ctrl-Shift-Enter.

Even with the second simpler formula, to my mind the VBA function is the better alternative, both in terms of application in a new spreadsheet, and understanding how the thing works. For those not familiar with VBA, the process of creating a new function is quite simple:

• Open a new spreadsheet (or an existing one you want to use the function in) and save with a chosen name.
• Press Alt-F11 to open the VBA editor.
• Right-click on VBAProject(spreadsheet name) in the list of open files on the left, and Insert-Module (see screenshot below)
• Copy the VBA code and paste it in the new module.

Finally I recommend having a look at the full EngTips thread linked above for discussion on a variety of topics, including whether to declare integer variables as Integer or as Long, how the first long formula works, the quick way to review long formulas, without splitting them up into parts, and VBA code for highlighting the active cell.