Solved

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

Posted on 2015-01-09
164 Views
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
Question by:EMCIT
• 3
• 2
• 2
• +2

LVL 45

Expert Comment

``````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
``````
0

LVL 18

Accepted Solution

SimonAdept earned 500 total points
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;
``````

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

LVL 26

Expert Comment

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
``````
0

LVL 119

Expert Comment

i think this simple query will do the job

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

.
0

LVL 49

Expert Comment

Use this minimal expression:

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

/gustav
0

LVL 26

Expert Comment

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

Nick67
0

LVL 119

Expert Comment

speaking of minimal,

see http:#a40540769
0

LVL 49

Expert Comment

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

LVL 26

Expert Comment

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

## Join & Write a Comment Already a member? Login.

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

#### 728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!