Jtdylktuy

I need a formula to find and return the first letter in a string. So if a cell contains:

1234(*&^%$23ADFG54

the formula would return:

A

EDIT#1:

I am currently using the following UDF:

Public Function FirstLetter(Sin As String) As String

    Dim i As Long, CH As String

    FirstLetter = ""

    For i = 1 To Len(Sin)

        If Mid(Sin, i, 1) Like "[A-Z]" Then

            FirstLetter = Mid(Sin, i, 1)

            Exit Function

        End If

    Next i

End Function

But I need a non-VBA solution.

This is probably not the most elegant but it stays away from Ctrl+Shift+Enter.

=MIN(INDEX(ROW(INDIRECT("1:"&LEN(A1)))+((CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<65)+(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>90))*1E+99,,))


Just Enter normally. Fill down as necessary. Note that the UPPER makes it case-insensitive.

How It Works

1. An A is ASCII character 65 and a Z is 90. These numbers can be derived from a single string character using the CODE function.


2. You need to crawl through the string to be examined character by character. The MID function can peel out a single character from within the string but it needs a starting point which will increase by 1 until each character in the string has been examined.


3. Typically this is done with the ROW function like ROW(1:50) to increment through numbers 1 to 50 if we knew that the string was 50 characters long. There is no guarantee of that string length so we have to construct the row reference using 1 and the LEN function then turn that concatenated string into a usable worksheet cell range address with the INDIRECT function.


4. The UPPER function was used on the string so that we only have to look for upper case ASCII character codes. If it wasn't in place the criteria would have to be doubled up to also compare to ASCII codes 97 (e.g. a) and 122 (e.g. z).


5. The INDEX function is used in its array form to provide the iteration to crawl through the string character by character. Any character peeled out that is not within A-Z causes the number returned by ROW(1:<length-of-string>) at that position to be multiplied by 1E+99 (a very large number and not the minimum of anything on the worksheet). If it is within A-Z, the position or ROW(1:<length-of-string>) remains the same value.


6. So INDEX is returning an array of numbers (aka positions), some of which are in the 1E+99 range. The MIN function takes the smallest of these which represents the first alphabetic character in the string.


7. With that formula in the first cell, fill down as necessary to evaluate all the strings in column A. Easy-peasy.

It may be worthwhile to point out that using ROW like this to provide an incremental series of number for array processing is usually down by locking down the actual numbers as absolutes like ROW($1:$50) so they do not change when filled down. This is unnecessary here as the "1:" is text and will not change when the copied/filled to another location.

Another way to determine the position

Column B



=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},UPPER(A1)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

This returns the position of the first letter found. Returns Len(A1) + 1 in case no letter is found.

The first argument of Find is an array of all letters to find in the string. The second argument is the actual string in upper case along with all letters at the end so that the find function always returns a value for all the letters.

Column C



=IF(LEN(A1) >= B1, RIGHT(LEFT(A1, B1),1), "")

This returns the letter found in the string. In case the string has no letter, it would return empty.

Reference:
https://exceljet.net/formula/split-text-and-numbers

The link provides a solution for numbers. The above formula is switched for letters.