That is really close! It works on about 50% of the builder names.
Examples:
Works on:
Beazer Homes 50' & 60'
D.R. Horton 40 & 50
Ryan - Greenbrier
Ryan - Aldridge
Ryan Townhomes
Westminster Townhomes
KB Home 40 & 50
Does not work on:
C.P. Morgan 40' & 50'
Lennar Homes 40, 50, 55 & 60
McCar Homes & McCar Townhomes
Mulvaney 60
Mulvaney Homes
Mulvaney Townhomes
Should convert to:
C.P. Morgan
Lennar Homes
McCar Homes
Mulvaney
Strange how it works on some similar and not others. This is really good!
Thanks!
"Domenic" wrote in message
...
Assuming that Column A contains your data, enter the following formula
that needs to be confirmed with CONTROL+SHIFT+ENTER in B1 and copy down:
=INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$ D$3,A1)),0))
....where D1:D3 contains the values to extract, such as Ryan, Mulvaney,
and KB Home.
Hope this helps!
In article ,
"Karl Burrows" wrote:
It is in a database, but am using Excel to pull some formatted reports.
Here is a better example, for multiple builders:
Ryan
Ryan Townhomes
Ryan 60'
Mulvaney - Greenbrier
Mulvaney - 80
KB Home
KB Home 70'
I need to extract the unique values to give me:
Ryan
Mulvaney
KB Home
What I am doing is about impossible in Access, as it is pulling lot data
into a formatted report spread over a 6 year rolling period. I would
still
have the same issue in Access as well if I were to query the data for
these
values.
Thanks!
wrote in message
oups.com...
what do you mean by reverse concatenation?
ps - you should be storing DATA in a DATABASE and not in excel.
-Aaron
|