?
Solved

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

Posted on 2015-01-09
9
Medium Priority
?
178 Views
Last Modified: 2015-01-09
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
Comment
Question by:EMCIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40540688
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
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40540692
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40540706
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40540769
i think this simple query will do the job

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


.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40540787
Use this minimal expression:

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

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540794
@gustav
Some explanation of what that's doing would be nice :)

Nick67
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40540802
speaking of minimal,

see http:#a40540769
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40540823
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

by:Nick67
ID: 40540832
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month8 days, 16 hours left to enroll

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

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

Join & Ask a Question