Regular Express

[ishwaar]

Hi Friends,

I want a VBA Macro to find the value of the string ("100 X 250 X 500 mms") is matching with a pattern.if it is not matching with the pattern is it possible to detect in which part it is not matching and how to correct it?

For Example - > Original String Value is - > 100 X 250 X 500 mms

Pattern Matching using RegEx is some thing like this - > ^\d{1,4}\ \X\d{1,4}\ \X\d{1,4}\ \[a-z] {1,3}

it is easier to match the string with the pattern and find out whether the string is correct or not.

If the value of the string is not correct then how to find which part is wrong and how to correct it? (Is there any specific object and properties are there in RegEx)


If there is some other string (For Ex. \or ! or * etc.) other than numbers and characters and space it should give a message "Invalid Stirng".

Is is possible to find and replace using RegEx?

If the string contains proper value but small mistakes like - > this "100X250X500 mms" or
100X 250 X500 mms occured then it should detect the mistake and correct the value using VBA Macro.

Kindly reply to this question ASAP.

Thanks and Regards,
Mahesh.

[Bob Phillips]

That bit easy should it be as you describe, but what if there is something else in there, some rogue string? And is it 3 @ 3digit numbers only around the Xs.

[Simon Lloyd]

Just use excels find and replace, from the toolbar EDIT, FIND click replace in find enter x in replace press space bar then x then space bar now click replace all, do the same for mms.

Easy fix!

[ishwaar]

Hi Friends,

I want a VBA Macro to find the value of the string ("100 X 250 X 500 mms") is matching with a pattern.if it is not matching with the pattern is it possible to detect in which part it is not matching and how to correct it?

For Example - > Original String Value is - > 100 X 250 X 500 mms

Pattern Matching using RegEx is some thing like this - > ^\d{1,4}\ \X\d{1,4}\ \X\d{1,4}\ \[a-z] {1,3}

it is easier to match the string with the pattern and find out whether the string is correct or not.

If the value of the string is not correct then how to find which part is wrong and how to correct it? (Is there any specific object and properties are there in RegEx)


If there is some other string (For Ex. \or ! or * etc.) other than numbers and characters and space it should give a message "Invalid Stirng".

Is is possible to find and replace using RegEx?

If the string contains proper value but small mistakes like - > this "100X250X500 mms" or
100X 250 X500 mms occured then it should detect the mistake and correct the value using VBA Macro.

Kindly reply to this question ASAP.

Thanks and Regards,
Mahesh.

[lucas]

Hi Mahesh,
Wondering why you just reposted your original question ver batim instead of trying to answer some of the legitimate questions that were asked after you posted the first one. This shows disrespect to those from whom you are seeking help.

In addition I would add that requesting answers ASAP will likely turn people off to your problem. Everyones questions are important and if you are in a crunch for time then maybe you should consider hiring someone here as a consultant. Click here to find out how.

[ishwaar]

Hi,

Sorry for the inconvenience.Instead of replying to the XLD's question I have reposted it.

Thanks and Regards,
Mahesh

[Bob Phillips]

We know that, and it doesn't help one iota, so you have wasted your post, Steve's reminder, and this blast from me.

[lucas]

I didn't say it was an inconvenience, I said it was disrespectful to those from whom you seek help.

How can reposting the original question help us understand your problem? You imply that we either didn't read it or that it contains the information required to answer your question which it obviously does not.

If you need help with this then we need some clarification.

Thanks for your cooperation.

[Simon Lloyd]

I still stand with.....use the "Find & Replace" its far more efficient!, you can record all the actions needed to space the string out correctly (4 find and replaces i think), you are wanting to use a sledgehammer to crack a nut!

[Norie]

Mahesh

You could probably use RegExp to extract the numbers but then you would probably need to use standard VBA functions to reconstruct to give the correct string.

Simon

Have you ever used regular expressions?

I think you might find they are a lot more powerful than find and replace.

And with find and replace you'd have to consider all possible combinations.

[Simon Lloyd]

Simon
Have you ever used regular expressions?
I think you might find they are a lot more powerful than find and replace.
And with find and replace you'd have to consider all possible combinations.

Norie, no i haven't used regular expressions, i have never explored them. I understand what you say about the possible combinations for find 'n' replace but in this instance it doesn't seem too many and although i don't understand regex what you have just explained seems like a lot of work.

But of course i don't have the experience there so

[Norie]

Simon

How does what I explain seem like a lot of work?

[lucas]

Norie, the OP seems to have some understanding of regex but if we can't get to the bottom of the problem it is hard to help....

regex is complicated for beginners by the way......it has it's own learning curve you have to admit.

[Simon Lloyd]

Norie, maybe i see it like that because the VBA (a UDF) for it would have to include Find, Replace, Match along with setting references to Microsoft VBScript Regular Expressions and create a vbscript object.

Anyway to be more helpful to the Op here's some help in this forum:
Brettj's RegEx and a discussion here

[Bob Phillips]

Simon

How does what I explain seem like a lot of work?

Learning regular expressions is hard work. Copying a provided example and using it is simple, learning to properly use regular expressions to parse a string to perform the required result requires an understanding that does not come easily.

If you think it is so simple, give me a regular expression that extracts the proper value to address someone by in a named expression, where you can have values such as

John Smith
Dick Van Dyke
Peter Alan Coe
Baron Hubert de Coubertin
Ian St John
Peter Gray Snr.

etc. etc.

[Norie]

lucas

I realise it can be complicated using RegExp, I'm not expert on it myself.

As to what the expression posted by the OP, it just looks over-complicated.

It's easy to extract all the numbers, if that's what's required, from a text string using RegExp.

[Norie]

Simon

Why would it need to include Find/Replace?

What about using VBA text functions like Join?

xld

I'm not saying it's easy to learn how to use RegExp 'properly' or 'efficiently'.

[Simon Lloyd]

I know this is adding fuel to the fire but in RegEx do you not need to know the combinations of what to look for and what to replace it with?

[Bob Phillips]

Indeed you do, but you can string the different combinations together in a much neater, but totally incomprehensible, expression, just the one.

[ishwaar]

Hi Steve,

It was not intentionally done. By mistake I did it. I am sorry.

Regards,
Mahesh.