Extract Unique Text from cell

[pto160]

I have this report that I download and export from my ERP system into Excel. I have a column with the name and address in one cell on multiple lines. The name is in line 1. The problem I am having is that the name is repeated on the first line from some of the rows. I want the unique text from the name line, which is on the first line. I want to get rid of the text starting with when the first word repeats itself.
This is a bit tricky because the second time the first word repeats itself, there is no space between the last word of the unique text and the second occurrence of the first word.

Book1
	A	B	C
1	Name	Formula	Desired Result
2	Precision EngineeringPrecision Engineering 3900 Waterloo Steet London WGGE15	Precision EngineeringPrecision Engineering	Precision Engineering
3	California Health ClinicCalifornia Health Clinic 45 Upper Town Ave California, CA 94102	California Health ClinicCalifornia Health Clinic	California Health Clinic
4	Townsend Mousetraps 1460 Sussex Street Sussex WEGG5	Townsend Mousetraps	Townsend Mousetraps
Sheet1

Is there a way to do this?

 

[pto160]

Everyone thanks for all your help. The VBA, power query and formulas all work great. This will save me a lot of time with working with names.

 

[sandy666]

You are welcome

update for post#3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    ETBD = Table.TransformColumns(Index, {{"Name", each Text.BeforeDelimiter(_, Character.FromNumber(10)), type text}}),
    SplitSpace = Table.ExpandListColumn(Table.TransformColumns(ETBD, {{"Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    CountUpper = Table.AddColumn(SplitSpace, "Custom", each Text.Length(Text.Select([Name], {"A".."Z"}))),
    Distinct = Table.Distinct(Table.SelectRows(CountUpper, each ([Custom] = 1)), {"Name"}),
    List = Table.AddColumn(Table.Group(Distinct, {"Index"}, {{"Count", each _, type table}}), "Custom", each [Count][Name]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom"})
in
    TSC

 

[pto160]

Thanks Sandy666 for the power query update. I've started to use the Power Query Microsoft tutorial to teach myself more about this feature.

 

[sandy666]

sure
here is almost everything about PQ

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com