Remove eveything (including text) to the right. What's wrong with this code?

[DetrieZ]

This is only removing the text characters.. I need it to remove all the text and everything after it



Public Function ExtractNumeric(TextString As String) As String
Dim x As Long
Dim sDigit As String



ExtractNumeric = vbNullString
For x = 1 To Len(TextString)
sDigit = Mid(TextString, x, 1)
If sDigit >= "0" And sDigit <= "9" Then
ExtractNumeric = ExtractNumeric & sDigit

End If
Next x
End Function

[pbaldy]

Does adding the bit in red work for you?

If sDigit >= "0" And sDigit <= "9" Then
  ExtractNumeric = ExtractNumeric & sDigit
Else
  Exit Function
End If

[DetrieZ]

Hey Paul...
Thanks but it that didn't help..

When I pull it into a query to test, The value comes up blank

[DaveT]

Your function works for me as shown. Are you expecting something else?

[DetrieZ]

Hey Dave..
Right,.. this is what I get too.. the Text is striped. I need to strip the texts as well as everything behind it..
This function is being used for splitting a "Phone Number" column into "Phone" and "Ext" columns..

In your example.. Row 2 it is possible that a text character is entered at the beginning of the field "ext 217 (312)555-5555" in which the function would completely wipe out everything after and including the first text character "e"...

I'm trying to get to...

(312)555-5555 Ext 217 = tblcontact.[Phone] 3125555555 tblContact.[ex] 217

my current code gets me 3125555555217

[pbaldy]

I based mine on this:

I need it to remove all the text and everything after it

Can we clarify the goal, including what you might start with and what you want to end up with?

[DetrieZ]

Sorry I wasn't more clear.


I'm trying to get from
tblContact.[PhoneNumber] = (312)555-5555 Ext 217
To
tblcontact.[Phone] 3125555555



my current code gets me from
tblContact.[PhoneNumber] = (312)555-5555 Ext 217
to
tblcontact.[Phone] 3125555555217

notice the 217 still exists and only the "Ext" has been removed

[pbaldy]

Presuming the parentheses are actually in the data, the code modification I posted earlier can be tweaked to pass over them:

If sDigit >= "0" And sDigit <= "9" Then 
  ExtractNumeric = ExtractNumeric & sDigit 
ElseIf sDigit = "(" OR sDigit = ")" Then 
  'don't do anything
Else
  Exit Function 
End If

[weekend00]

Presuming the parentheses are actually in the data, the code modification I posted earlier can be tweaked to pass over them:

If sDigit >= "0" And sDigit <= "9" Then 
  ExtractNumeric = ExtractNumeric & sDigit 
ElseIf sDigit = "(" OR sDigit = ")" Then 
  'don't do anything
Else
  Exit Function 
End If

you forgot the "-"

[pbaldy]

you forgot the "-"

I did indeed.

[DaveT]

This works if Ext occurs after number or if Ext is missing.
If you have the case of Ext 123 (999)-555-1234. let me know and I can tweak the code some more.

<code>
Public Function ExtractNumeric(TextString As String, EP As String) As String

'version 1.0 DLT
'pass string such as 999-555-1111 Ext 123 (with or without Ext present)
'pass "E" or "P"
'if E then return extension: 123
'if P then return phone: 9995551111

Dim x As Long
Dim sDigit As String
Const EXT As String = "Ext"
Const DIGITS As String = "0123456789"
Dim strNumber As String
Dim strExt As String
Dim zPos As Long
Dim zLEN As Long
Dim zMAX As Long
Dim blnFound As Boolean

ExtractNumeric = vbNullString

zLEN = Len(Nz(TextString))
If zLEN = 0 Then
Exit Function
End If

zPos = InStr(TextString, EXT)

Select Case EP
Case "E"

If (zPos > 0) And (zPos < zLEN) Then
blnFound = False

For x = zPos To zLEN
sDigit = Mid(TextString, x, 1)
If IsNumeric(sDigit) Then
blnFound = True
ExtractNumeric = ExtractNumeric & sDigit

ElseIf (blnFound) And Not IsNumeric(sDigit) Then
Exit Function

End If
Next
End If

Case "P"

If zPos > 0 Then
zMAX = zPos
Else
zMAX = zLEN
End If

For x = 1 To zMAX
sDigit = Mid(TextString, x, 1)

If IsNumeric(sDigit) Then
ExtractNumeric = ExtractNumeric & sDigit
End If
Next
End Select
End Function
</code>

[DetrieZ]

If you have the case of Ext 123 (999)-555-1234. let me know and I can tweak the code some more.

This "could" happen but I don't see the need to tweak...

This runs perfectly. I'm sorry to have taken so much of your time and not being clear in my first post for help. I've been working this for days.

I will now go back to understand what is actually going on in your code.

Dave and Paul.. thank you guys

[DetrieZ]

Ugh.. I posted too quickly...

It seems only to work if "Ext" appears in the initial field.

Because the point of entry for this data is free text, an extension can be entered with any number of text characters "ext", "x", "ex" etc.

sorry

[DaveT]

It's getting late. I can look at this more this weekend.
MEANWHILE, a quick flyby and you might try modifying the code as follows:

Const EXT As String = "x"
'Const DIGITS As String = "0123456789"

just search for x

DIGITS can be rem'd out since I didn't use it in the procedure

[DetrieZ]

I tried "Const EXT As String = "x" but the problem is it could any letter or any combination of letters.
I think the code you sent will strip all text characters, I just need to find the first on in the Const EXT part .. <Const EXT As String = "the first text character in a string"> .. RIght?