If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Extracting the numbers from the Text Field
I have a table in which in a text field is having Equipments models and data
entry is been not properly entered, the data is as below : 456ZX ZX965 ZX785M ZX956MN 45NM96ZX I need to retrieve only the numeric part like below 456 965 785 956 4596 I tried left, right, mid etc but failed. not sucess. Please advice a solution to pull/extract only the number portions from a text field Regards Irshad |
#2
|
|||
|
|||
Extracting the numbers from the Text Field
On 17 apr, 09:40, Irshad Alam
wrote: I have a table in which in a text field is having Equipments models and data entry is been not properly entered, the data is as below : 456ZX ZX965 ZX785M ZX956MN 45NM96ZX I need to retrieve only the numeric part like below 456 965 785 956 4596 I tried left, right, mid etc but failed. not sucess. Please advice a solution to pull/extract only the number portions from a text field Regards Irsha You could build a function for this and use it in your query. It looks like: Function NumbersInString(WithNumbers As String) As Variant Dim Temp As Variant Dim T As Integer For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End Function Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
Extracting the numbers from the Text Field
Sir,
Thanks for your reply. some more modification is required in the code. I pasted the below code to a new module. Called the function in the query as below : JobNum: NumbersInString([PDIJob]) Its Extracing the number from the text field , perfect. BUT showing result #Error if the referred PDIJob field is blank. I need if the field is blank, it should be blank instead of #Error Please do the ammendment in the code and help. Regards Irshad "XPS350" wrote: On 17 apr, 09:40, Irshad Alam wrote: I have a table in which in a text field is having Equipments models and data entry is been not properly entered, the data is as below : 456ZX ZX965 ZX785M ZX956MN 45NM96ZX I need to retrieve only the numeric part like below 456 965 785 956 4596 I tried left, right, mid etc but failed. not sucess. Please advice a solution to pull/extract only the number portions from a text field Regards Irsha You could build a function for this and use it in your query. It looks like: Function NumbersInString(WithNumbers As String) As Variant Dim Temp As Variant Dim T As Integer For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End Function Groeten, Peter http://access.xps350.com . |
#4
|
|||
|
|||
Extracting the numbers from the Text Field
Just change the first line to the following so the function can handle nulls
(blanks) as input. Function NumbersInString(WithNumbers) As Variant John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Irshad Alam wrote: Sir, Thanks for your reply. some more modification is required in the code. I pasted the below code to a new module. Called the function in the query as below : JobNum: NumbersInString([PDIJob]) Its Extracing the number from the text field , perfect. BUT showing result #Error if the referred PDIJob field is blank. I need if the field is blank, it should be blank instead of #Error Please do the ammendment in the code and help. Regards Irshad "XPS350" wrote: On 17 apr, 09:40, Irshad Alam wrote: I have a table in which in a text field is having Equipments models and data entry is been not properly entered, the data is as below : 456ZX ZX965 ZX785M ZX956MN 45NM96ZX I need to retrieve only the numeric part like below 456 965 785 956 4596 I tried left, right, mid etc but failed. not sucess. Please advice a solution to pull/extract only the number portions from a text field Regards Irsha You could build a function for this and use it in your query. It looks like: Function NumbersInString(WithNumbers As String) As Variant Dim Temp As Variant Dim T As Integer For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End Function Groeten, Peter http://access.xps350.com . |
#5
|
|||
|
|||
Extracting the numbers from the Text Field
Sir,
Thanks for your reply. I tried as you advised below, it produces error no. 94 Please recheck and advice. One additional question (just for my knowledge & learning) that if situation is reversed (means it need to pull only text part then what ammendment to be done in the above code. Regards Irshad "John Spencer" wrote: Just change the first line to the following so the function can handle nulls (blanks) as input. Function NumbersInString(WithNumbers) As Variant John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Irshad Alam wrote: Sir, Thanks for your reply. some more modification is required in the code. I pasted the below code to a new module. Called the function in the query as below : JobNum: NumbersInString([PDIJob]) Its Extracing the number from the text field , perfect. BUT showing result #Error if the referred PDIJob field is blank. I need if the field is blank, it should be blank instead of #Error Please do the ammendment in the code and help. Regards Irshad "XPS350" wrote: On 17 apr, 09:40, Irshad Alam wrote: I have a table in which in a text field is having Equipments models and data entry is been not properly entered, the data is as below : 456ZX ZX965 ZX785M ZX956MN 45NM96ZX I need to retrieve only the numeric part like below 456 965 785 956 4596 I tried left, right, mid etc but failed. not sucess. Please advice a solution to pull/extract only the number portions from a text field Regards Irsha You could build a function for this and use it in your query. It looks like: Function NumbersInString(WithNumbers As String) As Variant Dim Temp As Variant Dim T As Integer For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End Function Groeten, Peter http://access.xps350.com . . |
#6
|
|||
|
|||
Extracting the numbers from the Text Field
How about...
Function NumbersInString(Optional WithNumbers As String) As String Dim Temp As Variant Dim T As Integer If IsMissing(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it a little and it *seems* to work fine. ?numbersinstring("ABC1234DEF567G89H") 123456789 ?numbersinstring() returns Null -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201004/1 |
#7
|
|||
|
|||
Extracting the numbers from the Text Field
Sir,
I tried your code, it produces #Error, if the reffered field is blank. I used like below in query GetJobNo: NumbersInString([PDIJobNo]) It fetch the number and perfect, if data is there in the PDIJobNo field, if the field is blank, it show #Error. Thanks and best regards Irshad "PieterLinden via AccessMonster.com" wrote: How about... Function NumbersInString(Optional WithNumbers As String) As String Dim Temp As Variant Dim T As Integer If IsMissing(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it a little and it *seems* to work fine. ?numbersinstring("ABC1234DEF567G89H") 123456789 ?numbersinstring() returns Null -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201004/1 . |
#8
|
|||
|
|||
Extracting the numbers from the Text Field
Try this modification if you are passing in values that might be null
Function NumbersInString(WithNumbers As Variant) As Variant Dim Temp As String Dim T As Integer If IsNull(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County PieterLinden via AccessMonster.com wrote: How about... Function NumbersInString(Optional WithNumbers As String) As String Dim Temp As Variant Dim T As Integer If IsMissing(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function Unlike Ken, I'm far too lazy to rewrite the whole thing... so I modified it a little and it *seems* to work fine. ?numbersinstring("ABC1234DEF567G89H") 123456789 ?numbersinstring() returns Null |
#9
|
|||
|
|||
Extracting the numbers from the Text Field
John Spencer wrote:
Try this modification if you are passing in values that might be null Function NumbersInString(WithNumbers As Variant) As Variant Dim Temp As String Dim T As Integer If IsNull(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function I wonder if using Like "#" is faster or slower than IsNumeric? At least Like generalizes to other sets of characters more easily. -- Marsh MVP [MS Access] |
#10
|
|||
|
|||
Extracting the numbers from the Text Field
Also, IsNumeric will allow decimal points, etc. to remain as characters in
the string as well, so it sometimes is not a good choice when wanting just numbers to remain. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Marshall Barton" wrote in message ... John Spencer wrote: Try this modification if you are passing in values that might be null Function NumbersInString(WithNumbers As Variant) As Variant Dim Temp As String Dim T As Integer If IsNull(WithNumbers) Then NumbersInString = Null Else For T = 1 To Len(WithNumbers) If IsNumeric(Mid(WithNumbers, T, 1)) Then Temp = Temp & Mid(WithNumbers, T, 1) End If Next NumbersInString = Temp End If End Function I wonder if using Like "#" is faster or slower than IsNumeric? At least Like generalizes to other sets of characters more easily. -- Marsh MVP [MS Access] |
|
Thread Tools | |
Display Modes | |
|
|