# Tidy a list

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try:

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

copied down

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
ABCDEF
1FeeEarnerFreeholdLeaseholdFreeholdLeaseholdTotalExchanges
2AlyaYasmin(S&PFE-BRD)AlyaYasmin(S&PFE-BRD)FreeholdAlyaYasmin(S&PFE-BRD)Leasehold19120
Criteria Selection

Hi Mark

=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

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)

???

Thanks

Hi Mark

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

DominicB

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?

Hi Mark

Aaaaaahhh, got you!

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

HTH

DominicB

Replies
5
Views
486
Replies
0
Views
191
Replies
0
Views
132
Replies
12
Views
587
Replies
26
Views
873

### Forum statistics

1,221,053
Messages
6,157,640
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### 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.

### Which adblocker are you using?

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

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