# In excel, I'm trying to find letters and the 1st number in a string from the left.  What is the formula? on
Here is my example ABRAME124001 the answer should be ABRAME1.

Thank you!
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
if the prefix char comes with fixed length, then try formula:

``````=left(A2, 7)
``````

where A2 = ABRAME124001

else we should write a custom function to achieve this, but you got to tell us more details about your value.

Commented:
Thank you.  The number of char (letters) before the number is not always the same amount.

Commented:
try write a custom function like this:

``````Function getString(v As String) As String
v = UCase(v)
For i = 1 To Len(v)
If Asc(Mid(v, i, 1)) >= 65 And Asc(Mid(v, i, 1)) <= 90 Then
getString = getString & Mid(v, i, 1)
Else
getString = getString & Mid(v, i, 1)
Exit Function
End If
Next
End Function
``````

then use it like:

``````=getString(A2)
``````

where A2 = ABRAME124001
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Or something like this...

``````Function getString(ByVal str As String) As String
Dim i As Integer
Dim resStr As String
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
resStr = resStr & Mid(str, i, 1)
getString = resStr
Exit Function
Else
resStr = resStr & Mid(str, i, 1)
End If
Next i
End Function
``````
Then if your string is in A2, try this...
In B2
``````=getString(A2)
``````

Commented:
You can use this formula
``````=LEFT(A2,SEARCH(RIGHT(A2,LEN(A2)-MIN(FIND(0,SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9},0)&0))+1),A2))
``````
Top Expert 2014

Commented:
@Rachel

Will there ever be letter(s) after any digit(s)?

Will there ever be any characters other than letters and numbers?

Commented:
@ Ejgil - thank you but the result with your formula gave me a result of 1.  I need to get the letters along with the first number in the string.

@ aikimark - there will not be letters after the 1st number is found.  There will not be any other characters - only letters and numbers.

To all others who provided function code results, I have no idea where to put that function code - I only how to add copy and paste the formula - sorry - code is over my head.

Thanks All!

Commented:
I have no idea where to put that function code - I only how to add copy and paste the formula
you can create a Module and paste the codes there.

Create custom functions in Excel
https://support.office.com/en-us/article/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f
Commented:
It does that, see sheet.
Rachel-Text-and-first-number.xlsx

Commented:
Thank you Ejgil - that worked!

Do more with 