SEARCH next instance of ANY LETTER in a cell string value

[killpaddy]

I have a string in cell AU3: E50C [cell is formatted to 'text']
(this value is different for other cells E100B, E110 TH, E20 Y etc.)

I simply want to find next LETTER after second position in string (to miss the consistent "E") and split the string at this point, returning the left portion in one cell and the right portion in another.

Heres the formulas i have in two cells:

=RIGHT(AU3,SEARCH("[A-Z]",AU3,2))
=LEFT(AU3,SEARCH("[A-Z]",AU3,2)-1)

both are showing a #VALUE!

I'm fairly sure i have tried every possible combination of [A-Z], [a-z], "[a-z]" etc. but none of them seem to work?!?!

Any help would be appreciated, Thanks.

 

[killpaddy]

I should clarify:

For "E50Y" i want to return "E50" and "Y"
For "E110 TH" i want to return "E110" and "TH"

 

[NeonRedSharpie]

Function NEXTLETTER(rng As Range, start As Integer) As String    For x = start To Len(rng.Value) Step 1
        If IsNumeric(Mid(rng.Value, x, 1)) = False Then
            NEXTLETTER = Mid(rng.Value, x, 1)
            Exit Function
        End If
    Next x
End Function

I forsee a very ugly nested substitute function (unless I'm wrong) and I like VBA solutions so here's a custom function that will get your answer.





I saw your post, so here's a modification to the above formula.

Function NEXTLETTER(rng As Range, start As Integer, left As Boolean) As String    
    Select Case left
    Case False
        For x = start To Len(rng.Value) Step 1
            If IsNumeric(Mid(rng.Value, x, 1)) = False Then
                NEXTLETTER = Mid(rng.Value, x, Len(rng.Value) - x + 1)
                Exit Function
            End If
        Next x
    Case True
        For x = start To Len(rng.Value) Step 1
            If IsNumeric(Mid(rng.Value, x, 1)) = False Then
                NEXTLETTER = Mid(rng.Value, 1, x - 1)
                Exit Function
            End If
        Next x
    End Select
End Function

Adding FALSE will return text to the right, and adding TRUE will return text to the right.

So for the example of "E110TH" your cells will be:

Return E110:

=NEXTLETTER(AU3,2,TRUE)

Return TH:

=NEXTLETTER(AU3,2,FALSE)

 

[killpaddy]

OK, thanks, was hoping excel would have this seemingly simple function built in already, to search for any letter or any number, but oh well.

Tested this and it works great.