I have a list of 100 countries with a bracket and number
e.g. China (23)

I want to replace the cells so that
China (23) China

Thanks for your help

Dim cell as Range
Dim i as Long, sStr as String
for each cell in selection
i = instr(cell,"(")
if i 0 then
sStr = trim(left(cell,i))
cell.value = sStr
End if
Next

--
regards,
Tom Ogilvy


"teresa" wrote in message
...
I have a list of 100 countries with a bracket and number
e.g. China (23)

I want to replace the cells so that
China (23) China

Thanks for your help

"teresa" wrote in message
...
I have a list of 100 countries with a bracket and number
e.g. China (23)

I want to replace the cells so that
China (23) China

Thanks for your help

If this text is in A1, you can enter this formula in, say, B1:
=LEFT(A1,FIND(" ",A1,1))
Unfortunately, I have the Swedish version of Excel, I think the translation
is correct

If you have something like this "Sweden(46)" this function will search for
the bracket
=TRIM(LEFT(A1,FIND("(",A1,1)-1))

Now, do a copy and then Paste|Special, select the "Values" option in the
dialog

/Fredrik

Came From Chip's web page under first&last

A B C D
e.g. China (23) 2 1 =MID(A9,SMALL(IF(MID("
"&A9,ROW(INDIRECT("1:"&LEN(A9)+1)),1)="
",ROW(INDIRECT("1:"&LEN(A9)+1))),B9),SUM(SMALL(IF( MID(" "&A9&"
",ROW(INDIRECT("1:"&LEN(A9)+2)),1)="
",ROW(INDIRECT("1:"&LEN(A9)+2))),B9+C9*{0,1})* {-1,1})-1)

A B C D
China (23) 1 1 =MID(A12,SMALL(IF(MID("
"&A12,ROW(INDIRECT("1:"&LEN(A12)+1)),1)="
",ROW(INDIRECT("1:"&LEN(A12)+1))),B12),SUM(SMALL(I F(MID(" "&A12&"
",ROW(INDIRECT("1:"&LEN(A12)+2)),1)="
",ROW(INDIRECT("1:"&LEN(A12)+2))),B12+C12*{0,1 })*{-1,1})-1)

Hope it helps
Esau

"teresa" wrote:

I have a list of 100 countries with a bracket and number
e.g. China (23)

I want to replace the cells so that
China (23) China

Thanks for your help