Separate text and numbers from text field

[kkson]

Hi,
Did a search but couldn't find the answer. I have a combo box that has a recordset from a table that has over 80,000 records. Of course not all records can be listed in the dropdown. So I was going to break the combo box into two combo boxes.

The first box will contain the text and the second will contain just the numbers.

The text field contains some of the following:
TM 9-234-456-5
JOINT PUBS 346-65.67
DA FORM 34654

As you can see the text part is not a set length. However the text part will always be first. So I would like the first combo box to show the text part only, and the second box would show only the number part that relates to the text part. I know how to do multi combo boxes to limit the list on the second box using the criteria from the first box.

I don't know how to separate the text portion from the number portion though.

Thanks for any help

 

[bboffin]

Assuming that there are no numeric digits in the text part, that is the first numeric digit is always where the number part starts try:

Dim p As Integer
Dim t as string ' text part
Dim n as string ' number part
' assume value to be split is in s
for p = 1 to len(s)
    if isnumeric(mid(s,p,1)) then
        exit for
    end if
next
if p < len(s) then
    t = trim(mid(s, 1, p))   ' remove trailing space
    n = mid(s, p)
else
    t = s
    n = ""
end if

Alternatively if you know how to use the RegEx ActiveX component and regular expressions an expression of the form:
([A-Z ]+)([0-9\-\.])*
would produce two elements in the matches list that would correspond to the text part and the number part.



Bob Boffin

 

[kkson]

the code would work in a combo box? Which event would I put it in?

Unfortunately I don't know how to use the activex components. I put the programs I write on different machines and with the activex components don't you have to setup each machine to run them?

Thanks for the help,