jitendralucky
New Member
- Joined
- Feb 20, 2021
- Messages
- 2
- Office Version
- 2007
- Platform
- Windows
Dear All,
I have spreadsheet with a column (attached) with expected result in yellow highlited however I have formula that is working but need some modification in this if possible some poitns are below.
All is perfect however some #VALUE and mismatch still here with this formula, please see below another request.
1. LLC is accounted for, however 50% of the LLCs in our list appear with a comma and 50% with out a comma, so we have
90 TELEMARK, LLC - Which produces Llc 90 Telemark. Can this be correctly accounted so that if there is an LLC it does NOT switch the LLC to the beginning?
and
HURD REICH LLC - Which Produces #Value. Can this Just show the proper case - IE Hurd Rech LLC?
2. Anything with the word 'trust' shows no value. Similar to above is it possible to just show the proper case - IE Alexander Family Trust
I have spreadsheet with a column (attached) with expected result in yellow highlited however I have formula that is working but need some modification in this if possible some poitns are below.
All is perfect however some #VALUE and mismatch still here with this formula, please see below another request.
1. LLC is accounted for, however 50% of the LLCs in our list appear with a comma and 50% with out a comma, so we have
90 TELEMARK, LLC - Which produces Llc 90 Telemark. Can this be correctly accounted so that if there is an LLC it does NOT switch the LLC to the beginning?
and
HURD REICH LLC - Which Produces #Value. Can this Just show the proper case - IE Hurd Rech LLC?
2. Anything with the word 'trust' shows no value. Similar to above is it possible to just show the proper case - IE Alexander Family Trust
| test_spreadsheet_Forum.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | ANDERSON, STEPHEN V. & PATRICIA N. (JT) | Stephen & Patricia Anderson | Stephen & Patricia Anderson | ||
| 2 | HASTEN, CODY S. & ANNE M. (JT) | Cody & Anne Hasten | Cody & Anne Hasten | ||
| 3 | LILLY, DAVID L. | David Lilly | David Lilly | ||
| 4 | RAINSBERGER, KRISTOPHER ELLIS (ETAL) | Kristopher Ellis Rainsberger | Kristopher Ellis Rainsberger | ||
| 5 | BADEN, STEPHAN G. & PAMELA WOLF-BADEN | Stephan & Pamela Wolf-Badenbaden | Stephan & Pamela Wolf-Baden Baden | ||
| 6 | ROSS, ELIZABETH B. | Elizabeth Ross | Elizabeth Ross | ||
| 7 | BROWN, FREDERICK C., JR. & JUDITH A.(JT) | Frederick & Judith Brown | Frederick & Judith Brown | ||
| 8 | REICHERT, JAMES D. | James Reichert | James Reichert | ||
| 9 | NELSON, RANDELL K. & KATHERINE T. (JT) | Randell & Katherine Nelson | Randell & Katherine Nelson | ||
| 10 | VERPLOEG, ERIC & OWENS, SUNSHINE | Eric & Owens Verploeg | Eric & Owens Verploeg | ||
| 11 | HEPPELMANN, ANDREW R. | Andrew Heppelmann | Andrew Heppelmann | ||
| 12 | JECKEL, CHARITY ANNE | Charity Anne Jeckel | Charity Anne Jeckel | ||
| 13 | TOLLES, MARIAN G | Marian Gtolles | Marian Tolles | ||
| 14 | BASILE, PAULA | Paula Basile | Paula Basile | ||
| 15 | HAUGH, JULIA A | Julia Haugh | Julia Haugh | ||
| 16 | ASHBAUGH, WANDA DEE | Wanda Dee Ashbaugh | Wanda Dee Ashbaugh | ||
| 17 | DAUBERT, RISA | Risa Daubert | Risa Daubert | ||
| 18 | RE, DENNIS | Dennisr Re | Dennis Re | ||
| 19 | MANLEY, JODIE LYNN | Jodie Lynn Manley | Jodie Lynn Manley | ||
| 20 | MILLER, DEAN | Dean Miller | Dean Miller | ||
| 21 | STETTNER LIVING TRUST DATED 5/15/01 | Stettner Living Trust | #VALUE! | ||
| 22 | 177 HILLSIDE LLC | 177 Hillside LLC | #VALUE! | ||
| 23 | BARBARA & PETE WITHER TRUST | Barbara & Pete Wither Trust | #VALUE! | ||
| 24 | CRESS LIVING TRUST 11/7/2014 | Cress Living Trust | #VALUE! | ||
| 25 | OLK FAMILY LLC | Olk Family LLC | #VALUE! | ||
| 26 | DAVID J. LUND REVOCABLE TRUST 7/20/01 | David Lund Revocable Trust | #VALUE! | ||
| 27 | LESTER J. KAPLAN TRUST EST. 10/23/98 | Lester Kaplan Trust | #VALUE! | ||
| 28 | JAMES J. BOYNE REVOCABLE TRUST AGREEMENT | James Boyne Revocable Trust | #VALUE! | ||
| 29 | SBSRE LLC | Sbsre LLC | #VALUE! | ||
| 30 | ELIZABETH B. HOAGLUND LIVING TRUST 5/21/04 & ROGER B. HOAGLUND LIVING TRUST 5/21/04 | Elizabeth Hoaglund Living Trust | #VALUE! | ||
| 31 | TC PROPERTIES OF WYOMING, LLC | TC Properties of Wyoming LLC | Llc Tc Properties Of Wyoming | ||
| 32 | 250 HILLSIDE, LLC | 250 Hillside LLC | Llc 250 Hillside | ||
| 33 | STEAMBOAT BOULEVARD, LLC | Steamboat Boulevard LLC | Llc Steamboat Boulevard | ||
| 34 | BJC MOUNTAIN HOME, LLC | BJC Mountain Home LLC | Llc Bjc Mountain Home | ||
| 35 | ADJ, LLC | ADJ LLC | Llc Adj | ||
| 36 | HURD REICH LLC | Hurd Reich LLC | #VALUE! | ||
| 37 | GLOBAL STEAMBOAT LLC | Global Steamboat LLC | |||
test_spreadsheet | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C1:C36 | C1 | =PROPER(TRIM(MID(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE(LEFT(A1,FIND("(",A1&" (")-2),SEARCH(" ?.",A1&". .."),3,),SEARCH(" ?.",A1&" .. ..",SEARCH(" ?.",A1&" ..")+1)-3,3,),", JR.",),",",REPT(" ",99)),99,99))&" "&LEFT(A1,FIND(",",A1)-1)) |
