checking a string

**daniig** · 07-05-2012, 05:43 AM

Hi,

I have a list of various strings I need to check and match them to the same format the correct format is:
_"something"(a mix of capital letters and numbers)_"another something"_"one letter(out of 4 optional letters)"_"number".pdf(has to be in small letters)
Example:_GTF392847_234_U_15.pdf, _YRTFC892659345_32A439854_B_373.pdf.

Is there a way to do this check?

Thanks,
Dani

**smuzoen** · 07-05-2012, 05:59 AM

Yes you could a number of ways (e.g. regular expressions) however you need to put this into context. Have you got these strings in separate cells or are they derived from something - could you perhaps upload a sample workbook showing your data and explain what exactly you want to do. Obviously you are constructing a pdf file name. Do you just want to check that the file name matches a specific format as in your examples. Could you elaborate and if you have a sample workbook with some sample data show exactly what you are trying to achieve. I guess what you want is - does this file string match a set of rules - we need to know the rules e.g. mix of capital letters and numbers that is how long? e.g. one letter (what are the 4 optional letters) e.g. what is range of 3 digit number
Once you construct a file string you could use a regular expression to see if it matches your rules however we need to know the rules to be able to help you and as I say if you have a sample workbook if would make things easier - you need to elaborate on your question as the easy answer is YES you can validate the string construction but without rules impossible to create code to check rules match.

**jindon** · 07-05-2012, 06:05 AM

UDF

=IsFormatCorrect(A1,"ABCU")

where 2nd arg is the option string.

Please Login or Register  to view this content.

**daniig** · 07-05-2012, 06:20 AM

Hi again,

Sorry for not being completely clear so again the basic format is:_A_B_C_D.pdf
A=a mix of capital letters and numbers. Length varies and is not consistent.
B=also a mix between capital letters and numbers can also include "-" and also varies in length.
C= one of 4 capital letters U,B,C,S
D=a number between 1-9999.

Also important ".pdf" has to be in small letters.

What I'm trying to achieve is listing those strings in column A and then knowing which ones are not in the right format in order to correct the format

A

_HDT257863100A1_2007-11-652_U_15.pdf
_1232658_235_C_289.pdf
_LLL21385400_2009-05-008_S_654.pdf
_TRAY873_353_B_89.pdf

I'm sorry I can't upload files from this computer...

**jindon** · 07-05-2012, 06:34 AM

Change the Pattern to

Please Login or Register  to view this content.

And formula should look like

=IsFormatCorrect(A1,"UBCS")

**daniig** · 07-05-2012, 06:55 AM

I'm probably doing something wrong. It gives me a "#NAME?" error

**jindon** · 07-05-2012, 06:59 AM

See attached

**smuzoen** · 07-05-2012, 07:00 AM

Jindon has written you a User Defined Function - it is like a new function that you can use in a worksheet - it is in a module - see attached workbook.