Excel VBA Make sure a string follows a pattern and the ones that don't make them follow it

So, like the title says... I think

Let me explain the situation a little bit:

I've made a compiler that takes a bunch of files. All the files are supposed to be named something like 123456 - Q.ABC-A - Person's Name.txt

Problem I'm facing is that it seems like people can't seem to follow simple instructions, and I end up with files with names like 123456-Q.ABC-A-Person's Name.txt, which was easy to get around in my code by removing spaces as each file is processed and then adding them where I actually want them, which is around the first and third Hyphens.

My problem is the people that seem to have a harder time following instructions and I end up with files named something like 123456 - Q.ABC.A - Person's Name.txt or 123456 - Q.ABC-A Person's Name.txt, and have even had people using em dashes instead of hyphens and any number of other things that throws wrenches into my code.

I've been reading about things like using the "like" function, but that would only return true or false on whether or not the string matches the pattern, right? I'd still have to try and guess all the ways someone might mess up naming their file.

TL;DR

Is there a way to code a macro so that it will force a string into a specified pattern? So that I always get 123456 - Q.ABC-A - Person's Name.txt no matter if someone replaces a dot for a hyphen or a hyphen for a dot or whatever else they might do?

I'm trying to figure out how to import all the files and have the string get formatted to the proper pattern no matter how people seem to try and break things by naming things wrong without having to try and guess all the ways they might try to do so ahead of time.

Note:

• 123456 isn't always a six digit number. Sometimes there are less, but 6 should be the max.
• Sometimes the Q.ABC.A part has a double letter such as Q.ABC-AA

Thanks in advance for any help. I've been wracking my brain over this for a while. I'm no VBA pro, so I thought it was about time I asked for some help.

Try something like this

Please Login or Register  to view this content.

Basically the sheet is a hardware inventory tool. People place text files that are supposed to be named a certain way. From each of the text files it imports it uses two parts of the file name to create a temporary tab and populates the first two columns. Then it searches for certain information in the temporary tab that was made from the text file and populates the rest of the columns with the information then moves onto the next line, which is the next file in the folder.

The problem I'm running into is that people can't seem to always name their text files correctly, which causes errors when trying the code is trying to do it's thing on them (Can't find where to split, etc). I've gotten around the issue mostly by trying to guess what kind of mistakes people might make and have the code correct for it or just ignore errors altogether.

To use the sheet, run a DXdiag on your machine, and then save the results to a text file in a folder. Then on the sheet, click the button at the top left and navigate to the folder where you saved the DXDiag file and click ok. The file should be named something like:

[Assest Tag] - Q.[Desk number]-[Desk Letter] - [Person's Name]
123456 - Q.123-B - John Smith.txt or
000587 - Q.456-AQ - John Die.txt


Errors happen when people do things like:
123456 - Q.123B - John Smith.txt or
123456 - Q.123.B- John Smith.txt or
123456 — Q.123 — B — John Smith.txt (I don't know why/how they get em dashes by mistake, which makes me think they are trying to be funny) or
any number of other mistakes, people not paying enough attention and/or ways someone might try to be funny that I haven't thought of yet.

This is why I wanted to try and make the code force things into the correct pattern no matter what, although there might be a better solution that I haven't thought of.

The correct pattern is always

[Asset tag of up to six digits] - Q.[Desk number]-[Desk Letter] - [The person's name]

• The Asset tag is between 2 and six digits.
• The Desk number always starts with Q, followed by a 3 digit number
• Desk letter is usually a single letter [A-Z], but in a few cases the Desk Letter is a double letter such as AB or HW
• The file name has the person's name, but I don't use it for my tracking, so the code just doesn't do anything with it.

Here's the sheet:

EDIT: I don't know why, but when clicking on Go Advanced the post I had written up appears completely blank.