# SEARCH next instance of ANY LETTER in a cell string value

##### Board Regular
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

##### Board Regular
I should clarify:

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

#### NeonRedSharpie

##### Well-known Member
Code:
``````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.

Code:
``````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:
Code:
``=NEXTLETTER(AU3,2,TRUE)``
Return TH:
Code:
``=NEXTLETTER(AU3,2,FALSE)``

Last edited:

##### Board Regular
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.

Replies
5
Views
502
Replies
1
Views
53
Replies
6
Views
954
Replies
1
Views
263
Replies
12
Views
260

1,196,010
Messages
6,012,841
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back