checking the format of a string in a cell

[Corbin]

Hi all,

First thanks to Pikus for your help last time.

I got help here earlier about importing and seperating a long strin
that included model numbers and serial numbers and Pikus helped me ou
a lot but I have run into another small snag. This is the form of th
data that I am importing:

00B123445,S/N 04A12345,03C345566,01G3456,9H23456

The whole string is coming in in one cell. The first section befor
the first comma is the model number followed by a "S/N" and then on
space then the serial numbers which are seperated by one comma only.
99% of the incoming data is in this exact form but every once in
while there is a typo where the / is left out of the "S/N" or a spac
is missing or a comma is missing. When this happens the code used t
seperate everything breaks down and crashes the script. It doesn'
happen very often so what I thought I would do is to examine eac
string as it came in to see if it was in the correct format for th
code to work with and if it isn't put up an input box with a message t
change the format to the correct format and then return that value t
the code. I can easily write the statements for the input box an
everything but I can't seem to write a statement that checks to see i
the data is in the right format. This is difficult because the commas
spaces and S/N are not always in the exact same position in the string
the model numbers or serial numbers may vary in lengths from cell t
cell and there are not always the same number of serial numbers.
sometimes one, sometimes more than 10. so far I have tried variou
forms of wildcard combinations but I'm not even sure if VB recognize
that. Is there anyway to check to make sure that the data is in thi
format: "*,S/N *,*,*..." or maybe even just to check that the firs
",S/N " is there whithout extra or missing spaces before or after it?
If I can get that check to work I can do everything else that I need t
do. Thanks so much for your help.

Corbi

 

[SunTzuComm]

Hello, Corbin!

The best way to simplify input validation is to isolate the components and
examine them separately.

For example, you could use the Split function with each cell value to return a
zero-based, one-dimensional array of substrings using the comma as the
delimiter. This would give you an array like the one below.

00B123445
S/N 04A12345
03C345566
01G3456
9H23456

Your first test would be to see if the array has too few or too many elements.
The UBound function should return a value of 4 for a 5-element array like this
one.

Then you could loop through the array and apply tests for the format you
require in each component -- maybe even creating a separate subroutine for each
array element.

Earlier versions of Excel do not support the Split function, so if you have,
say, Excel 97, you'll have to create your own version of it.

Data validation is always a lot of work. I hope this helps.

Regards,
Wes

 

[brettdj]

Hi Corbin,

You can use the Test method of the RegExp object to verify you
strings

The code below loops through a selection of cells and returns True o
False to the immediate right cell for the pattern test result

The pattern is

alphanumeri
.comma..S\N..singlespace..alphanumeric..comma..alphanumeric..comma..alphanumeric..comm
..alphanumeric

It can be refined further to test for alphanumeric length and/or digi
and alphabetic position


Code
-------------------

Sub TestInput()
Dim Regex As Object, C As Range
Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "\w+,S\/N\s{1}(\w+,){3}\w+"
For Each C In Selection
C.Offset(0, 1) = Regex.test(C.Value)
Next
End Sub

-------------------


Cheers

Dav

 

[Corbin]

Dave,

Thanks for your help. Im pretty sure this will work great.

Corbi