Regular Expressions & Middle Name
 

I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?

Thanks in advance
PJ

Jim A. Jones
Jim A Jones


=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

Regular Expressions & Middle Name
 

REGEX.MID is not an Excel function. It is a function someone created to be
used in Excel. We have no idea what this function does.
Perhaps if you can show us what you want to accomplish, we can show you a
way to do that, using standard Excel functions.

wrote in message
ps.com...
I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?

Thanks in advance
PJ

Jim A. Jones
Jim A Jones


=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

Regular Expressions & Middle Name
 

Since you have MOREFUNC installed you can lookup REGEX.MID in the help
and lookup regular expressions

I am not an expert but this seems to work in both cases

=REGEX.MID(TRIM(A2),"(?<=\s)(\S+\s)+")

It's one of the best add-ins



--
Regards,

Peo Sjoblom






wrote in message
ps.com...
I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?

Thanks in advance
PJ

Jim A. Jones
Jim A Jones


=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

Regular Expressions & Middle Name
 

On Jul 20, 2:41 pm, "Peo Sjoblom" wrote:
Since you have MOREFUNC installed you can lookup REGEX.MID in the help
and lookup regular expressions

I am not an expert but this seems to work in both cases

=REGEX.MID(TRIM(A2),"(?<=\s)(\S+\s)+")

It's one of the best add-ins

--
Regards,

Peo Sjoblom

wrote in message

ps.com...



I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?

Thanks in advance
PJ

Jim A. Jones
Jim A Jones

=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")- Hide quoted text -

- Show quoted text -

Thank you very much Peo!

Regular Expressions & Middle Name
 

On Fri, 20 Jul 2007 10:14:13 -0700, wrote:

I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?

Thanks in advance
PJ

Jim A. Jones
Jim A Jones


=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

Well, here is a function that works by removing the First and Last words in the
string. So it should return middle name(s) or initial(s) with or without the
'.'

But there are many variables. For example:

John Paul de la Hoya

John P. X. de la Hoya

My wife has a first name, a conjunction, three middle names and a last name.

It's almost impossible to define a routine unless you can precisely define all
the variations.

--ron

Regular Expressions & Middle Name
 

"Dave Thomas" wrote...
REGEX.MID is not an Excel function. It is a function someone created to be
used in Excel. We have no idea what this function does.
....

You may not know what it does, but that doesn't mean many or even most of
the rest of us share your ignorance. If you don't have a clue how to
respond, don't respond.

Regular Expressions & Middle Name
 

wrote...
I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?
....
Jim A. Jones
Jim A Jones

=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

These aren't robust, and they include trailing spaces. The following will
give all middle initials, names, whatever, without trailing spaces.

=REGEX.SUBSTITUTE(A1,"^\s*\S+\s*(.+)*\s+\S+\s*$","[1]")

If you want only initials,

=REGEX.SUBSTITUTE(REGEX.SUBSTITUTE(A1,"^\s*\S+\s*( .+)*\s+\S+\s*$","[1]"),
"\S+[^\s.]\s*","")

Regular Expressions & Middle Name
 

On Jul 21, 8:00 pm, "Harlan Grove" wrote:
wrote...
I am new to regular expressions and trying to understand a little
better. I have a formula that Ron Rosenfeld wrote The first one will
Parse the middle initial followed by a dot (A.) and the second one
will parse the middle initial the (A) .How do I get one formula to
parse a middle initial if it has a . or not?
...
Jim A. Jones
Jim A Jones

=REGEX.MID(TRIM(A2),"(?<=\s)(\w.+\s)+")

=REGEX.MID(TRIM(A3),"(?<=\s)(\w+\s)+")

These aren't robust, and they include trailing spaces. The following will
give all middle initials, names, whatever, without trailing spaces.

=REGEX.SUBSTITUTE(A1,"^\s*\S+\s*(.+)*\s+\S+\s*$","[1]")

If you want only initials,

=REGEX.SUBSTITUTE(REGEX.SUBSTITUTE(A1,"^\s*\S+\s*( .+)*\s+\S+\s*$","[1]"),
"\S+[^\s.]\s*","")

Thank you very much Harlan. I appreciate all of your knowledge on this
and any other subject.