EMCIT
asked on
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lots of different ways to do that.
A bunch of folks will chime in.
Pick the most efficient and elegant method suggested
Using VBA
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
i think this simple query will do the job
select [F1], left(replace([F1],val([F1] ),""),1) as FirstLetter
from tablex
.
select [F1], left(replace([F1],val([F1]
from tablex
.
Use this minimal expression:
StrFirstChar = Mid(strNumberText, Len(CStr(Val("1" & strNumberText))), 1)
/gustav
StrFirstChar = Mid(strNumberText, Len(CStr(Val("1" & strNumberText))), 1)
/gustav
@gustav
Some explanation of what that's doing would be nice :)
Nick67
Some explanation of what that's doing would be nice :)
Nick67
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
"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
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 :)
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 :)
Open in new window