Regular Expressions & Middle Name

[pkaraffa]

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)+")

 

[Dave Thomas]

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.

 

[Peo Sjoblom]

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

 

[pkaraffa]

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








- Show quoted text -

Thank you very much Peo!

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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*","")

 

[pkaraffa]

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(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.