Tidy a list

[MarkAndrews]

I have the following list which i would like to just show the names

Is there an easy way to do this other than using Text To Columns?

thanks

Generic Exchanges.xls
	E	F	G	H
2	AlyaYasmin(S&PFE-BRD)
3	BarbaraVickers(S&PFE-BRD)
4	BenKelly(S&PFE-BRD)
5	CaroleDishman(S&PFE-BRD
6	CavelNichols(S&PFE-BRD)
7	DamianHarrison(S&PFE-BRD
8	DanielleCruise(S&PFE-MAN)
9	DaoudHussain(S&PFE-BRD)
10	EstherYoung(S&PFE-MAN)
11	GwilymDavies(S&PFE-MAN)
12	HelenHorner(S&PFE-BRDH/
13	HelenManning(S&PFE-BRD)
14	HelenaReeve(S&PFE-BRD)
15	JamieCruise(S&PFE-MAN)
16	JaniceCopley(S&PFE-BRDH
Workings

 

[NBVC]

Try:

=LEFT(E2,SEARCH("(",E2)-2)

copied down

 

[MarkAndrews]

Try:

=LEFT(E2,SEARCH("(",E2)-2)

copied down

Fantastic, thanks

I have another query following on from this

I have a sheet which looks like the following

What I could like to do is end up with the following result using (Result in G2)

20(1LH)

F2 (total = 20)
E2 (Leasehold = 1LH)

How can this be achieved - nooed to copy down a number of cells

Generic Exchanges.xls
	A	B	C	D	E	F
1	FeeEarner	Freehold	Leasehold	Freehold	Leasehold	TotalExchanges
2	AlyaYasmin(S&PFE-BRD)	AlyaYasmin(S&PFE-BRD)Freehold	AlyaYasmin(S&PFE-BRD)Leasehold	19	1	20
Criteria Selection

 

[DominicB]

Hi Mark

How about :

=VALUE(LEFT(G2,2))

To give the "20" - this will return a "proper" number that can be used in subsequent formulae.

=MID(G2,FIND("(",G2,1)+1,3)

To return the "1LH".

HTH

DominicB

 

[MarkAndrews]

Thanks Dominic - I used this

=CONCATENATE(F2,E2,H1)

H1 - Being 'LH'

However, I cant get the cell to format with the brackets around the 1LH to achieve the below value

20(1LH)

???

 

[MarkAndrews]

I'm missing something, can anyone please advise?

Thanks

 

[DominicB]

Hi Mark

What does F2,E2 and H1 contain? What result are you getting and what result do you want?

DominicB

 

[MarkAndrews]

Hi Mark

What does F2,E2 and H1 contain? What result are you getting and what result do you want?

DominicB

H1 = LH (entered in the cell)

F2 = Total (D2+E2)

What i need is the total (F2) in G2, inclusive of E2 & the letters LH, in brackets

EG

20(1LH) = 1LH is E2(1) & LH = H1 (fixed cell)

Hope this makes sense?

 

[MarkAndrews]

Sample of data

Generic Exchanges.xls
	A	B	C	D	E	F	G
1	FeeEarner	Freehold	Leasehold	Freehold	Leasehold	TotalExchanges
2	AlyaYasmin(S&PFE-BRD)	AlyaYasmin(S&PFE-BRD)Freehold	AlyaYasmin(S&PFE-BRD)Leasehold	19	1	20	201LH
Criteria Selection

 

[DominicB]

Hi Mark

Aaaaaahhh, got you!

=CONCATENATE(F2,"(" &E2,$H$1&")")

HTH

DominicB