Extracting only letters from data?

[lifeis]

Hey Guys,
This could possibly be very complicated that im trying to do and I am certainly not an expert in VB. But I know that it must be possible so I figured I would ask you guys and give it a shot.
I have uploaded a picture of the table that I wish to extract data from.
Basically, there is a field called 'DeviceTag'
Each entry consists of no more than 3 letters, then numbers (sometimes numbers+letters).
I want to extract only the letters at the begining (either export only the letters at the start of each entry to a new table or just some how that I can easily access them). Then I want to do the same with the rest of the data after the letters.
So ideally after running the script (or whatever you guys suggest) I would have another table with two fields, one containing the Letters at the start, the other containing the numbers/letters at the end.
An example would be:
MAG1001
Would be split into: "MAG" & "1001"
I hope that makes sense.
Thanks guys.
P.S. This Forum is great, very informative.
-Craig

 

[jzwp22]

Since the device tag can start with at most 3 letters, I will assume that the first character must be a letter and that the second and third characters may be letters or numbers. There are several built-in functions within Access that can be used to achieve what you want. The first thing to do is to check whether the 3rd character is a number or letter. We'll need the IIF() function to do the checking and the IsNumeric() function to check whether the character is a number or not. To isolate a specific character or characters, we can use the Mid() function. More detail on each function is available in Access's help section.

The following are the two expressions that you will need:



First Part: IIF(IsNumeric(mid(devicetag,3,1)), IIF(isNumeric(mid(devicetag,2,1)), mid(devicetag,1,1),mid(devicetag,1,2)), mid(devicetag,1,3))

Second Part: IIF(IsNumeric(mid(devicetag,3,1)), IIF(isNumeric(mid(devicetag,2,1)), mid(devicetag,2),mid(devicetag,3)), mid(devicetag,4))

 

[lifeis]

The first few characters are always a letter.
What I want is to seperate so that:
When the code gets to the first character which isnt a letter, it splits.
So examples:
CV2010 >>>> CV & 2010
MGH2136 >>> MGH & 2136
C2510 >>> C & 2510

I will read up on those functions tonight.
Thanks alot for your help.
-Craig

 

[Mr. B]

Here is a user defined function that you can paste into a Module and then use in a query that will return the Text characters from any of your Device Tag values.

Public Function GetStartingText(StringVal As String) As String
Dim cntr
For cntr = 1 To Len(StringVal)
If IsNumeric(Mid(StringVal, cntr, 1)) = True Then
GetStartingText = Left(StringVal, cntr - 1)
Exit Function
End If
Next cntr
End Function

You would use the following in on field in your query to call this function:
StartOfTag: GetStartingText([DeviceTag])

You can then use the following statement in another field of your query to return the remainder of the DeviceTag field:
EndOfTag: Right([DeviceTag],Len([DeviceTag])-Len([startTag]))

Hope this helps.

 

[jzwp22]

The two expressions I provided should take care of all of these situations:

CV2010 >>>> CV & 2010
MGH2136 >>> MGH & 2136
C2510 >>> C & 2510

You should be able to copy the expressions into 2 controls on a form or you can use them in a query. With this approach, you do not need to copy the separated data into another table; in fact, doing so would violate good database practices.

 

[vbaInet]

How about this:

mid([DeviceTag], instr(1, [DeviceTag], val(StrReverse([DeviceTag]))))

 

[MarkK]

Except some of the data in the posted jpg end in an alpha character too...

 

[vbaInet]

Except some of the data in the posted jpg end in an alpha character too...

Thanks for pointing that out. I didn't even look at the attached image. I was just following the OP's examples.

In that case, scrap my function. A function like that of Mr B which reads off every character would be the way to go.

 

[lifeis]

Yes, the last characters after the letters at the begining can be either Numerical or Alpha Characters. They can also be at varying legnths. If I could just split the String into two, splitting at the point where the first number appears. So:
CV2112 would become: CV & 2112
SV2011B Would become: SV & 2011B
etc etc.
The only thing which is in common between each string, is that they all begin with at least one letter and they all should be split wherever the first number occurs.
Thanks alot for all your help guys, I am having a go at some of your suggestions right now.
I appreciate you taking the time to help.
-Craig