Removing Parentheses and Data

[Saxman]

I have the following data in column D of a worksheet.

Foreign Rhythm (IRE)
Perilously (USA)

and so forth.....

I need to remove the parentheses and the data contained within, so I'm
left with the name.

 

[Praxx]

I have the following data in column D of a worksheet.

Foreign Rhythm (IRE)
Perilously (USA)

and so forth.....

I need to remove the parentheses and the data contained within, so I'm  
left with the name.

=LEFT(A1,SEARCH("(",A1)-1)

 

[Praxx]

I have the following data in column D of a worksheet.

Foreign Rhythm (IRE)
Perilously (USA)

and so forth.....

I need to remove the parentheses and the data contained within, so I'm  
left with the name.

Make that =LEFT(A1,SEARCH("(",A1)-2). You have to take 2 off the
results of the SEARCH or there will be a blank space on the end of
each of your results.

Sorry about that.

 

[Claus Busch]

or Data => Text to columns, seperator "(" and don't import the right
column (step 3 of the assistent)


Regards
Claus Busch

 

[Praxx]

or Data => Text to columns, seperator  "(" and don't import the right
column (step 3 of the assistent)

Regards
Claus Busch

This would still leave you with a space after the name though. Not a
huge deal but can caused problems down the road if any lookups are
done against the list.

 

[Claus Busch]

This would still leave you with a space after the name though. Not a
huge deal but can caused problems down the road if any lookups are
done against the list.

then another time Data => Text to columns => fixed width => ok
and the space is gone


Regards
Claus Busch

 

[Pete_UK]

Not sure how fixed width would help.

How about Find & Replace first to change <space><open bracket> with
something else (eg pipe character "|"), and then use Text-to-columns
with pipe as the delimiter?

Hope this helps.

Pete

 

[Ron Rosenfeld]

I have the following data in column D of a worksheet.

Foreign Rhythm (IRE)
Perilously (USA)

and so forth.....

I need to remove the parentheses and the data contained within, so I'm
left with the name.

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

 

[Saxman]

then another time Data => Text to columns => fixed width => ok
and the space is gone

Thank you.

I'm not too bothered about any blank spaces.

Could you be a bit more explicit with the above method? Got to 'fixed
width', but where do I type =>?

I meant to reply, but I lost my Internet connection.

 

[Claus Busch]

Am Tue, 23 Aug 2011 07:29:02 +0100 schrieb Saxman:

Could you be a bit more explicit with the above method? Got to 'fixed
width', but where do I type =>?

you can choose it in step 1 of the assistent.
Data => Text to columns with fixed width works like TRIM


Regards
Claus Busch

 

[Saxman]

Not sure how fixed width would help.

How about Find & Replace first to change <space><open bracket> with
something else (eg pipe character "|"), and then use Text-to-columns
with pipe as the delimiter?

If I select space then double barreled names get split.

I can only enter one character in the window.

 

[Saxman]

you can choose it in step 1 of the assistent.
Data => Text to columns with fixed width works like TRIM

I highlight the column.

Click on the icon Text to Columns.

Select delimited/next.

Which check box from the five?

 

[Claus Busch]

Am Tue, 23 Aug 2011 12:28:32 +0100 schrieb Saxman:

Select delimited/next.

I don't know how the english version looks. But in step 1 there is
beneath delimited another option.


Regards
Claus Busch

 

[Dave Peterson]

If you want to get rid of everything after (and including) the " (" (space, open
paren), then you could:

Select the range
Edit|Replace
what: _(* (space character, open paren, asterisk)
with: (leave blank)
replace all

 

[Saxman]

I get a 'value' error with the above.

Below is an example of today's data.


Barons Spy (IRE)
Dorback
Spanish Bounty
Tamagin (USA)
Avon Light
Sophies Beau (USA)
Moufatango (FR)
Bandanaman (IRE)
Stand Clear
For A Dancer (IRE)
Zahara Joy
Andorn (GER)
Dream Risk (FR)

Sorry 'value' was created because I didn't select the correct column 'D'
in the above formula.

However, it does remove the parentheses and contents, but puts a #VALUE!
remark in all cells with no parentheses.

 

[Pete_UK]

John,

forget the Text-to-columns.

Highlight the column by clicking the identifier. Then CTRL-H will
bring up the Find and Replace dialogue box. Fill in as follows:

Find what: <space>(*
Replace with: leave blank

Click Replace All.

Note: <space>(* means type a single space followed by open bracket
followed by asterisk (wildcard character), and after you click Replace
All then all those characters will be removed in situ.

Hope this helps.

Pete

 

[Saxman]

Am Tue, 23 Aug 2011 12:28:32 +0100 schrieb Saxman:



I don't know how the english version looks. But in step 1 there is
beneath delimited another option.

See my reply to Pete.

Thanks.

 

[Saxman]

John,

forget the Text-to-columns.

Highlight the column by clicking the identifier. Then CTRL-H will
bring up the Find and Replace dialogue box. Fill in as follows:

Find what: <space>(*
Replace with: leave blank

Thank you Pete. That is simple and effective.

I use a VG add on called ActiveData. It is able to merge worksheets by
any variable (as long as they are in both worksheets and identical).
Without the horse's breeding location, it can do that admirably.

 

[Pete_UK]

Glad to be able to help you again, John - thanks for feeding back.

Pete