How do I replace characters AND THEN count the length of the output?

[adlomb]

So I have a field that's quite messy. The text varies, but each entry is similar to "1;2;3;;; " or ";1;2;3;4 "

What I'm trying to do is (1) clean up the field so each entry is ONLY the numbers ("123", "12345", etc.). (2) I want to be able to count the number of numbers so that: if it's greater than or equal to 3 it returns "More Than 3", if it's less than 3 it shows "Less Than 3", or if it's blank it shows "Blank".

I've tried everything I can think of. When I use the Replace function for either the semicolon or the extra spaces, the Len function shows the number of characters of the input, NOT the output, as I would have expected it to.



Any help would be really appreciated!!!

[orange]

What purpose and why exactly are you using this input? Tell us what you are trying to accomplish--there may be some options.

[adlomb]

What purpose and why exactly are you using this input? Tell us what you are trying to accomplish--there may be some options.

This is ultimately for a report I'm working on, where one of the items shows the count of each of the three categories: "More Than 3", "Less Than 3", and "Blank".

[adlomb]

The input is just the way it is. I have no idea why there are all those semicolons and spaces!

[davegri]

Do the replaces, then call the function to get the answer string.
You didn't specify what you wanted if the length is exactly 3.

txtTarget = Replace(txtTarget,";","")
txtTarget = Replace(txtTarget," ","")
txtCount = fcnCount(txtTarget)

Function fcnCount(arg as string) as string
Select case len(arg & vbnullstring)
    Case 0
        fcnCount = "Blank"
    Case 1,2
        fcnCount = "Less than 3"
    Case 3
        fcnCount = "Exactly 3"
    Case > 3 
        fcnCount = "More than 3"
End Select