In excel, I'm trying to find letters and the 1st number in a string from the left.  What is the formula?

Rachel
Rachel used Ask the Experts™
on
Here is my example ABRAME124001 the answer should be ABRAME1.  

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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

=left(A2, 7)

Open in new window


where A2 = ABRAME124001

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

Author

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

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

Open in new window


then use it like:

=getString(A2)

Open in new window


where A2 = ABRAME124001
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Subodh Tiwari (Neeraj)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

Open in new window

Then if your string is in A2, try this...
In B2
=getString(A2)

Open in new window

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))

Open in new window

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?

Author

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!
Ryan ChongSoftware Team Lead

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

Author

Commented:
Thank you Ejgil - that worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial