Spliting existing data

[Jen]

Re-posting after simplifying the question:

I have existing data in excel to import into a table. The field contains
file numbers that are laid out by a 2 – 4 letter prefix, always letters no
numbers, and a 1 – 5 digit number, always numbers no letters, like this
ABC123. I need to split it in to two fields in a query:
Field A Field B Field C
ABC123 ABC 123

I don’t know if there is a command that may only display numbers or letters
or what but I am dealing with about 20,000 pre existing records.

Help please!

 

[Ken Snell [MVP]]

You'd need to use user-defined functions to do this. (This is just one way
out of many ways to do this.)

First, import the data into a temporary table (named TempTableName) that has
just one field, named fldOrig. That field will hold the original data (e.g.,
ABC123).

Then create a table (named PermanentTableName) that will hold the permanent
data -- three fields: fldOriginal, fldPrefix, fldSuffix.

Then put the following function in a regular module (name the module
basFunction):

Public Function SeparateLettersFromNumbers(strOriginal As String,
blnReturnLetters As Boolean)
Dim lngChar As Long, strHold As String
strHold = ""
If blnReturnLetters = True Then
For lngChar = 1 To Len(strOriginal)
If IsNumeric(Mid(strOriginal, lngChar, 1)) = True Then
Exit For
Else
strHold = strHold & Mid(strOriginal, lngChar, 1)
End If
Next lngChar
Else
For lngChar = 1 To Len(strOriginal)
If IsNumeric(Mid(strOriginal, lngChar, 1)) = True Then
strHold = strHold & Mid(strOriginal, lngChar, 1)
End If
Next lngChar
End If
SeparateLettersFromNumbers = strHold
End Function


Then use an append query to copy the modified data into the permanent table:

INSERT INTO PermanentTableName (fldOriginal, fldPrefix, fldSuffix)
SELECT fldOrig, SeparateLettersFromNumbers(fldOrig, True),
SeparateLettersFromNumbers(fldOrig, False)
FROM TempTableName;

 

[Marshall Barton]

I have existing data in excel to import into a table. The field contains
file numbers that are laid out by a 2 – 4 letter prefix, always letters no
numbers, and a 1 – 5 digit number, always numbers no letters, like this
ABC123. I need to split it in to two fields in a query:
Field A Field B Field C
ABC123 ABC 123

I don’t know if there is a command that may only display numbers or letters
or what but I am dealing with about 20,000 pre existing records.

Nothing built-in so you have to create your own function to
do it. It could be along the lines of this air code:

Public Function AlphaPart(strFileNum As Variant) As Variant
Dim pos As Integer
If IsNull(strFileNum) Then
AlphaPart = Null
Exit Function
End If

If Left(strFileNum, 1) Like "[0-9]" Then
AlphaPart = ""
Exit Function
End If

For pos = 2 to Len(strFileNum)
If Mid(strFileNum, pos, 1) Like "[0-9]" Then Exit For
Next pos

AlphaPart = Left(strFileNum, pos - 1)
End Function

Public Function NumericPart(strFileNum As Variant) As
Variant

NumericPart=Mid(strFileNum,Len(Nz(AlphaPart(strFileNum),""))+1)
End Function

Then the query would look like:

SELECT FileNumber,
AlphaPart(FileNumber) As Prefix,
NumericPart(FileNumber) As Suffix
FROM ...

 

[Jamie Collins]

2 - 4 letter prefix, always letters no numbers
1 - 5 digit number, always numbers no letters

Therefore, the first numeric will appear in position 3, 4 or 5:

SELECT MID(MyDataCol, 1,
SWITCH(
MID(MyDataCol,3,1) LIKE '[0-9]', 2,
MID(MyDataCol,4,1) LIKE '[0-9]', 3,
TRUE, 4)
) AS left_part,
MID(MyDataCol,
SWITCH(
MID(MyDataCol,3,1) LIKE '[0-9]', 2,
MID(MyDataCol,4,1) LIKE '[0-9]', 3,
TRUE, 4)
+ 1) AS right_part
FROM
[Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].[Sheet1$]
;

Jamie.

--