• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

How to return the first letter of a string of letters and numberr

I have a field that contains a string of numbers and letters that always has 1 or more numbers at the beginning. I need to pull only the first letter, from the left,  that is contained in the string.
0
EMCIT
Asked:
EMCIT
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Martin LissOlder than dirtCommented:
Dim lngIndex As Long

For lngIndex = 1 To Len(MyString)
    If Not IsNumeric(Mid(MyString, lngIndex, 1)) Then
        MsgBox Mid(MyString, lngIndex, 1)
        Exit For
    End If
Next

Open in new window

0
 
SimonCommented:
This works (in a query)

SELECT Left(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Yourstring],'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),1) AS Expr1
FROM Table1;

Open in new window


Yes, that really is ten levels of nested replace functions ;)

This should be faster than vba if you're processing the contents of 100s of rows. Obviously not so good for a single field value on a form.
0
 
Nick67Commented:
Lots of different ways to do that.
A bunch of folks will chime in.
Pick the most efficient and elegant method suggested

Using VBA

Function ReturnFirstLetter(strData as string) as String
Dim Done as Boolean

Done = False
'Lets recursively select the first character of the string
Do until Done = True
    if Len(StrData) = 0 then
        ReturnFirstLetter ="No Letter!"
        Done = True
        Exit Do
    end if
    'Check if it's a number
    If isNumeric(Left(StrData,1)) = True
        ReturnFirstLetter = Left(StrData,1)
        Done = True
    else
        'trim off the leftmost character if it isn'y
        StrData = Right(StrData, Len(StrData)-1)
    end if
Loop

End Function

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Rey Obrero (Capricorn1)Commented:
i think this simple query will do the job

select [F1], left(replace([F1],val([F1]),""),1) as FirstLetter
from tablex


.
0
 
Gustav BrockCIOCommented:
Use this minimal expression:

StrFirstChar = Mid(strNumberText, Len(CStr(Val("1" & strNumberText))), 1)

/gustav
0
 
Nick67Commented:
@gustav
Some explanation of what that's doing would be nice :)

Nick67
0
 
Rey Obrero (Capricorn1)Commented:
speaking of minimal,

see http:#a40540769
0
 
Gustav BrockCIOCommented:
Assuming that a value can have a leading zero:

    "01234abcd"

I prefix a "1" to avoid a leading zero:

    "101234abcd"

Now Val safely can extract the number part:

    101234

This is converted to a string:

    CStr(Val("1" & strNumberText))

which has a length:

    Len(CStr(Val("1" & strNumberText)))

which is used as the second parameter in Mid to define the position of the character:

    Mid(strNumberText, Len(CStr(Val("1" & strNumberText))))

Finally the last parameter is applied to extract one character only:

    Mid(strNumberText, Len(CStr(Val("1" & strNumberText))), 1)

Great fun!
Gustav
0
 
Nick67Commented:
I had lost sight of the original parameters
Multiple numbers before letters.
Now Val() makes sense
Get the count of numbers from the front
Shear those off and take the first thing remaining.

or replace them with "" as @Rey did

Lots of ways to do it.
Didn't figure mine'd be the winner :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now