Text to Column on first alpha character

[detrie]

Access 2007
SQL 2005

I have a field Contact.Direct_phone (varchar) that may include numeric special and alpha characters. example +44112 12345 ext 117

Basically, I need to move the extension to Contact.DirectPhoneExt.

What can I use to find the first alpha character "e" in the above example and move everything to right of and (including the "e") to the DirectPhone.Ext column> From there I can strip out special and alpha characters in another step (I've got that figured out)

I've been trying to use the Right() feature is this the best way to go?

TIA

 

[Galaxiom]

Mid([fieldname], InStr([fieldname],"e"))

 

[Galaxiom]

On rereading I see you are looking for a more general solution for any alpha character. This will require a loop in a VBA function.

Select each character using the Mid function in a loop as you increment the start position. Then use the IsNumeric function to determine if it is an alpha character.

Once you find the alpha character use Mid to get the rest of the string.

 

[boblarson]

If they all will have it as ext then you can use something like this:

DirectPhoneExt: Trim(Mid([Direct_phone],InStr(1,[Direct_phone],"t")+1))

(Tested)

 

[detrie]

Thanks Bob...
It may have any alpha character (nothing is ever easy)

 

[detrie]

I've found this.. but I'm not sure where to go with it

Public Function GetAlphaString(strMyString) As String
Dim i As Integer
Dim iAsc As Integer
For i = 1 To Len(strMyString)
iAsc = Asc(Mid(strMyString, i, 1))
If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
Exit For
End If
Next i
End Function

 

[Galaxiom]

I've found this.. but I'm not sure where to go with it

Put the code in a Standard module in VBA.
In your queries use this expression for a derived field.

Extension: GetAlphaString([Direct_phone])

You should also post code in code tags with indentation to make it easier to read. Something like this.

Public Function GetAlphaString(strMyString) As String
Dim i As Integer
Dim iAsc As Integer
   For i = 1 To Len(strMyString)
      iAsc = Asc(Mid(strMyString, i, 1))
      If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
         GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
         Exit For
      End If
   Next i
End Function

It will noeed more code to deal with Nulls or values that have no alpha character.

 

[detrie]

Hey Galaxiom... Thanks for helping me this...

I put Extension: GetAlphaString([Direct_phone]) into a query and executed it and I am being thrown a

Run-time error '94':
Invalid use of null
at
For i = 1 To Len(strMyString)

and #Error in the query results grid.

.. is this what you meant by more code needed to deal with nulls and no alpha characters?

 

[Galaxiom]

.. is this what you meant by more code needed to deal with nulls and no alpha characters?

Exactly what has happened. The function has been fed a Null from your table. The Len of a Null is "invalid use of Null".

Add this line before the For Loop.

strMyString = Nz(strMyString, "Invalid")

Crude but effective. Where there is no entry the result should read "Invalid"

The other problem might be when there is no alpha character found. It might return an empty string but if it complains then make an Else entry for the If.

Else: GetAlphaString = "Invalid"

You can use substitute a string you prefer or the null string if you like.
Otherwise change the first line to:

Public Function GetAlphaString(strMyString) As Variant

This allows it to return a Null.
In this case you could use this as the Else line
Else: GetAlphaString = Null

 

[detrie]

Here is what I've got....

Public Function GetAlphaString(strMyString) As Variant
Dim i As Integer
Dim iAsc As Integer

strMyString = Nz(strMyString, "Invalid")
For i = 1 To Len(strMyString)
iAsc = Asc(Mid(strMyString, i, 1))
If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
GetAlphaString = Right(strMyString, Len(strMyString) - i + 1)
Else: GetAlphaString = Null
Exit For
End If
Next i
End Function

****************
No errors but...

The characters after the alpha part of the direct_phone does not parse out to Extension

Where direct_phone is blank Extension = d (not a big deal.. I could clean this up)

 

[Galaxiom]

The Else line should be after the Exit For line. Otherwise it will exit the loop if the first character is not alpha.

Actually my else suggestion is not so elegant since it sets the variable to null repeatedly until it finds the alpha character. A better way is to test after the loop to see if the counter got to the last character and if so then set the return value to Null.

Another alternative to the Nz bit is to test if the input value is Null and if so set the return value to Null and exit.

 

[WayneRyan]

detrie,

Use the Split function and return the last two "words":

Public Function GetString(strYourField As String) As String
Dim varArray As Variant

varArray = Split(strYourField, " ")
If UBound(varArray) < 2 Then
   GetString = ""
Else
  GetString = varArray(UBound(varArray) - 1) & " " & varArray(UBound(varArray))
End If

End Function

Wayne

 

[detrie]

I simply added Not Null criteria to the query.

So tell me.. does it actually hurt to be so smart?

 

[Galaxiom]

If you are avoiding Nulls being processed you should declare the function input argument As String. Without a declaration it will be Variant. It saves processing if the variable is declared as a fixed type.

 

[detrie]

Ok.. So it wont be as easy as I thought to clean these fields up.

I was able to clean the Ext field by striping all non-numeric characters .

To strip the result of GetAlphaString from [Direct_Phone], I've been trying unsuccessfully to use this to strip the "GetAlphaString" from the Direct_Phone".

UPDATE tblContact SET [Direct_Phone] = Mid(Direct_Phone, InStrRev(GetAlphaString))

 

[Galaxiom]

GetAlphaString is a function so it won't work like that without an argument.

If you don't want the alpha characters in the extension number it would be better to configure your function to do the whole job and return just the numeric section rather than reprocessing the output.

The function could also be modified to return the characters to the left of the first alpha. As both tasks are closely related I would combine them into one function and add a boolean argument to indicate which half was required.

 

[detrie]

I've added a function... GetAlphaEXT to GetAlphaPHONE

I wasn't sure how to bring it all into one function and add the boolean argument as Galaxiom suggested.

When I put this in a query, the "Ext: GetAlphaPhone([account_phone])" seems right but the "Phone: GetAlphaExt([account_phone])" does not strip out the Ext


Public Function GetAlphaEXT(strMyString) As String

Dim i As Integer
Dim iAsc As Integer

strMyString = Nz(strMyString, "Invalid")
For i = 1 To Len(strMyString)
iAsc = Asc(Mid(strMyString, i, 1))
If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
GetAlphaEXT = Right(strMyString, Len(strMyString) - i + 1)

Exit For

Else: GetAlphaEXT = Null
End If
Next i
End Function


Public Function GetAlphaPHONE(strMyString) As String

Dim i As Integer
Dim iAsc As Integer


strMyString = Nz(strMyString, "Invalid")
For i = 1 To Len(strMyString)
iAsc = Asc(Mid(strMyString, i, 1))
If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <= 122) Then
GetAlphaPHONE = Left(strMyString, Len(strMyString))
Exit For

Else: GetAlphaPHONE = Null
End If
Next i
End Function

 

[Galaxiom]

I don't think GetAlphaPHONE can be right because you would get the whole input string. The line should be:
GetAlphaPHONE = Left(strMyString, i - 1)

Else: GetAlphaPHONE = Null will be a problem because you cannot return a Null as a String only as a Variant.

To get rid of the alpha characters, once you hit the first one then keep testing the characters until you hit the next numeric before you Mid() or Right() the end of the string.

 

[detrie]

The Phone was spot on.

you're right "Invalid Use of Null". If I comment out Else: GetAlphaPHONE it seems to run fine. What's the risk of eliminating the Else statement?

I need to strip all Alpha and "special characters". So I run strAPhone = "UPDATE Contact SET Contact.account_phone = StripEx([account_phone],32)"


Public Function StripIllegal(strCheck As String) As String



On Error GoTo StripIllErr
'illegal file name characters included in default string are ? [ ] / \ = + < > :; * " , '
Dim intI As Integer
Dim intPassedString As Integer
Dim intCheckString As Integer
Dim strChar As String
Dim strIllegalChars As String

StripIllegal = ""

strIllegalChars = "?[]/\=+<>:;,*" & chr(34) & chr(39) 'add any other characters to remove to this string
intPassedString = Len(strCheck)
intCheckString = Len(strIllegalChars)
If intPassedString < intCheckString Then

For intI = 1 To intCheckString
strChar = Mid(strIllegalChars, intI, 1)
If InStr(strCheck, strChar) > 0 Then
strCheck = Replace(strCheck, strChar, "")
End If

Next intI

Else

For intI = 1 To intPassedString
strChar = Mid(strIllegalChars, intI, 1)
If InStr(strCheck, strChar) > 0 Then
strCheck = Replace(strCheck, strChar, "")

End If

Next intI

End If
StripIllegal = Trim(strCheck)
StripIllErrExit:
Exit Function


StripIllErr:
MsgBox "The following error occured: " & Err.Number & " " & Err.Description
Resume StripIllErrExit

End Function

 

[Galaxiom]

Without the Else it should return a NullString so no problem really.

StripEx is a user custom function so I wouldn't know it works. I am guessing it is used to remove all characters below the ASCII of the second argument. The example shows 32 (space) which is the first of the normal characters.

The StripIllegal function you posted is not the way for you since you only need the numeric characters anyway.

The way you are going about this trying to tack together other people's code is not ideal. You would be much better off starting again on your function, nutting out the logic then implementing the code to do it efficiently.

All the techniques you need to use are in the code discussed in the this thread. Stepping through a string character by character, testing each one for a condition then extracting the parts required. You just need to put it together in the right way.

The best way with the extension is as I described above. Once you get to the first alpha keep stepping through the characters until you hit the first numeric then grab the rest.