Trimming Data

[Terry Bennett]

I have a lenghty list of data all within one column (ie; rows 1 - 2000).
All of the data consists of a branch name followed by some figures in
brackets, ie:

Southampton (12345-654)

Is there a way that I can 'trim' the data to remove everything within the
brackets, including the brackets themselves?

[vezerid]

Terry,

The following formula will remove the bracket part of a name in A2. It
allows for an optional space before the left parenthesis.

=IF(ISNUMBER(FIND(" (",A2)),LEFT(A2,FIND("
(",A2)-1),LEFT(A2,FIND("(",A2)-1))

If it is guaranteed that there will always be a space before the
parenthesis then you can use the simpler formula:

=LEFT(A2,FIND(" (",A2)-1)

HTH
Kostis Vezerides

[JE McGimpsey]

One way:

Select your data, choose Data/Text to Columns. Select Delimited and
click Next.

If your branch names are all one word, check the space checkbox. If not,
enter "(" (no quotes) in the Other input box. Click Finish. Delete the
unneeded columns.

If you chose "(", your branch names will have a trailing space. To
remove them (if you care to) you can enter

B1: =TRIM(A1)

copy down as far as necessary, then copy column B, select column A and
choose Edit/Paste Special/Values.






In article <[email protected]>,
"Terry Bennett" <[email protected]> wrote:

> I have a lenghty list of data all within one column (ie; rows 1 - 2000).
> All of the data consists of a branch name followed by some figures in
> brackets, ie:
>
> Southampton (12345-654)
>
> Is there a way that I can 'trim' the data to remove everything within the
> brackets, including the brackets themselves?

[starguy]

select the range your data resides in then
press Ctrl+H
in Find What: press a space then write this (*)
in Replace with: leave it blank
press Replace All
you are done.
hope this will serve your purpose.

I have a lenghty list of data all within one column (ie; rows 1 - 2000).
All of the data consists of a branch name followed by some figures in
brackets, ie:

Southampton (12345-654)

Is there a way that I can 'trim' the data to remove everything within the
brackets, including the brackets themselves?

[Terry Bennett]

Many thanks guys - all of these work great!

"starguy" <[email protected]> wrote in
message news:[email protected]...
>
> select the range your data resides in then
> press Ctrl+H
> in Find What: press a space then write this (*)
> in Replace with: leave it blank
> press Replace All
> you are done.
> hope this will serve your purpose.
>
> Terry Bennett Wrote:
>> I have a lenghty list of data all within one column (ie; rows 1 -
>> 2000).
>> All of the data consists of a branch name followed by some figures in
>> brackets, ie:
>>
>> Southampton (12345-654)
>>
>> Is there a way that I can 'trim' the data to remove everything within
>> the
>> brackets, including the brackets themselves?
>
>
> --
> starguy
> ------------------------------------------------------------------------
> starguy's Profile:
> http://www.excelforum.com/member.php...o&userid=32434
> View this thread: http://www.excelforum.com/showthread...hreadid=562032
>