Hello everyone,
I have a real doosie facing me at work tomorrow.
I have a spreadsheet with about 35,000 rows data and in one
particular
column, data like this can be found:
NAME OF COMPANY INC
DOE, JANE
NAME OF COMPANY LLC
DOE, JOHN EST
DOE, JOHN JR
NAME OF COMPANY LTD
DOE, JANE TRE
DOE, JOHN II
Assume widely varying company names and person names,
"EST" is short
for "estate" and "TRE" is short for
"trustee". Also, names always have a
comma, sometimes have "EST" or "TRE" but
companies never have a comma
or other punctuation marks. How can I format this column so
I looks like
this?
DOE, J
NAME OF COMPANY INC
NAME OF COMPANY LLC
JOHN DOE EST
JOHN DOE JR
NAME OF COMPANY LTD
JANE DOE TRE
I've been using
=LEFT(a1,FIND(" ",a1)-1)
and
=RIGHT(a1,LEN(a1)-FIND(" ",a1,1))
to split off the last name from the rest of the cell and
then I glue
things back together with
=a3&" "&a2 or =a2&"
"&a3 as appropriate.
This method works for names lacking suffixes such as
"JR" or "II" but it
munges company names and puts the "EST" and
"TRE" inside of the name,
like
OF COMPANY INC NAME
JANE DOE
OF COMPANY LLC NAME
JOHN EST DOE
JOHN JR DOE
OF COMPANY LTD NAME
JANE TRE DOE
JOHN II DOE
That was manageable when there were at most only a few dozen
entries
that needed correction but obviously, this is totally
unacceptable when
dealing with ~35,000 line items. A better formula is wanted.
Thoughts and suggestions appreciated,
Reese
--
Ink Works
http://www.inkworkswell.c
om
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|