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?

 

[Zot]

It is easier to use UDF

Function GetName(cell As Range) As String

Dim LocEnd&
Dim FirstRow$(), FirstWord$()

FirstRow = Split(cell, vbLf)
FirstWord = Split(FirstRow(0), " ")

LocEnd = InStr(Len(FirstWord(0)), cell, FirstWord(0)) - 1
If Not LocEnd = -1 Then
    GetName = Left(cell.Text, LocEnd)
Else
    GetName = FirstRow(0)
End If

End Function

 

[sandy666]

just for fun...

Name		Custom
Precision EngineeringPrecision Engineering 3900 Waterloo Steet London WGGE15		Precision Engineering
California Health ClinicCalifornia Health Clinic 45 Upper Town Ave California, CA 94102		California Health Clinic
Townsend Mousetraps 1460 Sussex Street Sussex WEGG5		Townsend Mousetraps

let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 1, 1),
    SplitLF = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Name", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name"),
    Group1 = Table.Group(SplitLF, {"Index"}, {{"Count", each _, type table}}),
    ListFirst = Table.AddColumn(Group1, "Custom", each List.First([Count][Name])),
    SplitSpace = Table.ExpandListColumn(Table.TransformColumns(ListFirst, {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    Distinct = Table.Distinct(SplitSpace, {"Custom"}),
    TS = Table.AddColumn(Distinct, "Custom.1", each Text.Select([Custom], {"A".."Z"})),
    Length = Table.TransformColumns(TS,{{"Custom.1", Text.Length, Int64.Type}}),
    Filter = Table.SelectRows(Length, each ([Custom.1] = 1)),
    TSC1 = Table.SelectColumns(Filter,{"Index", "Custom"}),
    Group2 = Table.Group(TSC1, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group2, "Custom", each [Count][Custom]),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC2 = Table.SelectColumns(Extract,{"Custom"})
in
    TSC2

 

[Zot]

Wow .... Not familiar with Power Query to be fun ?

 

[sandy666]

same with vba ?

 

[aRandomHelper]

This can replace your "helper" column

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

Cell Formulas
Range	Formula
B2:B4	B2	=IF(LEFT(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2)=MID(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2+1,LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))),LEFT(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2),LEFT(A2,SEARCH(CHAR(10),A2)-1))

 

[aRandomHelper]

Another UDF method, if you prefer this (no helper column as well).

Function UniqText(cell As Range) As String
    UniqText = Split(cell.Value, vbLf)(0)
    If Left(UniqText, Len(UniqText) / 2) = Mid(UniqText, Len(UniqText) / 2 + 1) Then UniqText = Left(UniqText, Len(UniqText) / 2)
End Function

UniqText(A2)

 

[alz]

How about this!

TempO.xlsx
	A	B
1	Name	Formula
2	Precision EngineeringPrecision Engineering 3900 Waterloo Steet London WGGE15	Precision Engineering
3	California Health ClinicCalifornia Health Clinic 45 Upper Town Ave California, CA 94102	California Health Clinic
4	Townsend Mousetraps 1460 Sussex Street Sussex WEGG5	Townsend Mousetraps
Sheet9

Cell Formulas
Range	Formula
B2:B4	B2	=IF(LEFT(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2)=RIGHT(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2), LEFT(LEFT(A2,SEARCH(CHAR(10),A2)-1),LEN(LEFT(A2,SEARCH(CHAR(10),A2)-1))/2), LEFT(A2,SEARCH(CHAR(10),A2)-1))

 

[jtakw]

Hi,

Maybe this might work for you:

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

Cell Formulas
Range	Formula
C2:C4	C2	=TRIM(LEFT(A2,IFERROR(FIND(LEFT(A2,FIND(" ",A2)-1),A2,FIND(" ",A2))-1,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17))-1)))

C2 formula copied down.

 

[jtakw]

Too late to edit, updated below:

Book3.xlsx
	A	B	C
1	Name	Desired Result
2	Precision EngineeringPrecision Engineering 3900 Waterloo Steet London WGGE15	Precision Engineering	Precision Engineering
3	California Health ClinicCalifornia Health Clinic 45 Upper Town Ave California, CA 94102	California Health Clinic	California Health Clinic
4	Townsend Mousetraps 1460 Sussex Street Sussex WEGG5	Townsend Mousetraps	Townsend Mousetraps
5	PizzaHut 123 A Street Small Town, CA 12345	PizzaHut	PizzaHut
Sheet713

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(LEFT(A2,IFERROR(FIND(LEFT(A2,FIND(" ",A2)-1),A2,FIND(" ",A2))-1,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17))-1)))
C2:C5	C2	=TRIM(LEFT(A2,IFERROR(FIND(LEFT(A2,FIND(" ",A2)-1),A2,FIND(" ",A2))-1,FIND(CHAR(10),A2))))

If data within Name cell may or may not contain carriage return, Char(10), use B2 formula.
If data within Name cell Always contain carriage return, use C2 formula.

Either formula copied down column.