Regular Expressions & Middle Name

  • Thread starter Thread starter pkaraffa
  • Start date Start date
P

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)+")
 
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.
 
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
 
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 -
Click to expand...

Thank you very much Peo!
 
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)+")
Click to expand...

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
 
Dave Thomas said:
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.
Click to expand...
....

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.
 
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)+")
Click to expand...

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*","")
 
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)+")
Click to expand...

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*","")
Click to expand...

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