Removing last three chars

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

Hi,

if the data in A1 then try

=TRIM(LEFT(A1,FIND("(",A1)-1))

hope this helps

jindon

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

Hi,
I thought some data may not have no space in between the character and the bracket.

rgds,
jindon

Originally Posted by Fredrik Wahlgren

"teresa" <[email protected]> wrote in message
news:[email protected]...[color=blue]

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(IF(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