Separate only names with formatting within column

jitendralucky

New Member
Joined
Feb 20, 2021
Messages
2
Office Version
  1. 2007
Platform
  1. 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


test_spreadsheet_Forum.xlsx
ABC
1ANDERSON, STEPHEN V. & PATRICIA N. (JT)Stephen & Patricia AndersonStephen & Patricia Anderson
2HASTEN, CODY S. & ANNE M. (JT)Cody & Anne HastenCody & Anne Hasten
3LILLY, DAVID L.David LillyDavid Lilly
4RAINSBERGER, KRISTOPHER ELLIS (ETAL)Kristopher Ellis RainsbergerKristopher Ellis Rainsberger
5BADEN, STEPHAN G. & PAMELA WOLF-BADENStephan & Pamela Wolf-BadenbadenStephan & Pamela Wolf-Baden Baden
6ROSS, ELIZABETH B.Elizabeth RossElizabeth Ross
7BROWN, FREDERICK C., JR. & JUDITH A.(JT)Frederick & Judith BrownFrederick & Judith Brown
8REICHERT, JAMES D.James ReichertJames Reichert
9NELSON, RANDELL K. & KATHERINE T. (JT)Randell & Katherine NelsonRandell & Katherine Nelson
10VERPLOEG, ERIC & OWENS, SUNSHINEEric & Owens VerploegEric & Owens Verploeg
11HEPPELMANN, ANDREW R.Andrew HeppelmannAndrew Heppelmann
12JECKEL, CHARITY ANNECharity Anne JeckelCharity Anne Jeckel
13TOLLES, MARIAN GMarian GtollesMarian Tolles
14BASILE, PAULAPaula BasilePaula Basile
15HAUGH, JULIA AJulia HaughJulia Haugh
16ASHBAUGH, WANDA DEEWanda Dee AshbaughWanda Dee Ashbaugh
17DAUBERT, RISARisa DaubertRisa Daubert
18RE, DENNISDennisr ReDennis Re
19MANLEY, JODIE LYNNJodie Lynn ManleyJodie Lynn Manley
20MILLER, DEANDean MillerDean Miller
21STETTNER LIVING TRUST DATED 5/15/01Stettner Living Trust#VALUE!
22177 HILLSIDE LLC177 Hillside LLC#VALUE!
23BARBARA & PETE WITHER TRUSTBarbara & Pete Wither Trust#VALUE!
24CRESS LIVING TRUST 11/7/2014Cress Living Trust#VALUE!
25OLK FAMILY LLCOlk Family LLC#VALUE!
26DAVID J. LUND REVOCABLE TRUST 7/20/01David Lund Revocable Trust#VALUE!
27LESTER J. KAPLAN TRUST EST. 10/23/98Lester Kaplan Trust#VALUE!
28JAMES J. BOYNE REVOCABLE TRUST AGREEMENTJames Boyne Revocable Trust#VALUE!
29SBSRE LLCSbsre LLC#VALUE!
30ELIZABETH B. HOAGLUND LIVING TRUST 5/21/04 & ROGER B. HOAGLUND LIVING TRUST 5/21/04Elizabeth Hoaglund Living Trust#VALUE!
31TC PROPERTIES OF WYOMING, LLCTC Properties of Wyoming LLCLlc Tc Properties Of Wyoming
32250 HILLSIDE, LLC250 Hillside LLCLlc 250 Hillside
33STEAMBOAT BOULEVARD, LLCSteamboat Boulevard LLCLlc Steamboat Boulevard
34BJC MOUNTAIN HOME, LLCBJC Mountain Home LLCLlc Bjc Mountain Home
35ADJ, LLCADJ LLCLlc Adj
36HURD REICH LLCHurd Reich LLC#VALUE!
37GLOBAL STEAMBOAT LLCGlobal Steamboat LLC
test_spreadsheet
Cell Formulas
RangeFormula
C1:C36C1=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))
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
How about:
sandbox101.xlsb
ABC
1ANDERSON, STEPHEN V. & PATRICIA N. (JT)Stephen & Patricia AndersonStephen & Patricia Anderson
2HASTEN, CODY S. & ANNE M. (JT)Cody & Anne HastenCody & Anne Hasten
3LILLY, DAVID L.David LillyDavid Lilly
4RAINSBERGER, KRISTOPHER ELLIS (ETAL)Kristopher Ellis RainsbergerKristopher Ellis Rainsberger
5BADEN, STEPHAN G. & PAMELA WOLF-BADENStephan & Pamela Wolf-BadenbadenStephan & Pamela Wolf-Baden Baden
6ROSS, ELIZABETH B.Elizabeth RossElizabeth Ross
7BROWN, FREDERICK C., JR. & JUDITH A.(JT)Frederick & Judith BrownFrederick & Judith Brown
8REICHERT, JAMES D.James ReichertJames Reichert
9NELSON, RANDELL K. & KATHERINE T. (JT)Randell & Katherine NelsonRandell & Katherine Nelson
10VERPLOEG, ERIC & OWENS, SUNSHINEEric & Owens VerploegEric & Owens Verploeg
11HEPPELMANN, ANDREW R.Andrew HeppelmannAndrew Heppelmann
12JECKEL, CHARITY ANNECharity Anne JeckelCharity Anne Jeckel
13TOLLES, MARIAN GMarian GtollesMarian Tolles
14BASILE, PAULAPaula BasilePaula Basile
15HAUGH, JULIA AJulia HaughJulia Haugh
16ASHBAUGH, WANDA DEEWanda Dee AshbaughWanda Dee Ashbaugh
17DAUBERT, RISARisa DaubertRisa Daubert
18RE, DENNISDennisr ReDennis Re
19MANLEY, JODIE LYNNJodie Lynn ManleyJodie Lynn Manley
20MILLER, DEANDean MillerDean Miller
21STETTNER LIVING TRUST DATED 5/15/01Stettner Living TrustStettner Living Trust
22177 HILLSIDE LLC177 Hillside LLC177 Hillside Llc
23BARBARA & PETE WITHER TRUSTBarbara & Pete Wither TrustBarbara & Pete Wither Trust
24CRESS LIVING TRUST 11/7/2014Cress Living TrustCress Living Trust
25OLK FAMILY LLCOlk Family LLCOlk Family Llc
26DAVID J. LUND REVOCABLE TRUST 7/20/01David Lund Revocable TrustDavid J. Lund Revocable Trust
27LESTER J. KAPLAN TRUST EST. 10/23/98Lester Kaplan TrustLester J. Kaplan Trust
28JAMES J. BOYNE REVOCABLE TRUST AGREEMENTJames Boyne Revocable TrustJames J. Boyne Revocable Trust
29SBSRE LLCSbsre LLCSbsre Llc
30ELIZABETH B. HOAGLUND LIVING TRUST 5/21/04 & ROGER B. HOAGLUND LIVING TRUST 5/21/04Elizabeth Hoaglund Living TrustElizabeth B. Hoaglund Living Trust
31TC PROPERTIES OF WYOMING, LLCTC Properties of Wyoming LLCTc Properties Of Wyoming, Llc
32250 HILLSIDE, LLC250 Hillside LLC250 Hillside, Llc
33STEAMBOAT BOULEVARD, LLCSteamboat Boulevard LLCSteamboat Boulevard, Llc
34BJC MOUNTAIN HOME, LLCBJC Mountain Home LLCBjc Mountain Home, Llc
35ADJ, LLCADJ LLCAdj, Llc
36HURD REICH LLCHurd Reich LLCHurd Reich Llc
37GLOBAL STEAMBOAT LLCGlobal Steamboat LLCGlobal Steamboat Llc
Sheet13
Cell Formulas
RangeFormula
C1:C37C1=IFERROR(PROPER(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(REPLACE(LEFT(A1,FIND("(",A1&" (")-2),SEARCH(" ?.",A1&". .."),3,),SEARCH(" ?.",A1&" .. ..",SEARCH(" ?.",A1&" ..")+1)-3,3,),", JR.",),", LLC"," LLC"),",",REPT(" ",99)),99,99))&" "&LEFT(A1,FIND(",",SUBSTITUTE(A1,", LLC"," LLC"))-1)),PROPER(IFERROR(LEFT(A1,FIND("TRUST",A1)+5),A1)))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,288
Messages
5,635,334
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top