Removing Parentheses and Data

  • Thread starter Thread starter Saxman
  • Start date Start date
S

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.
 
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.
Click to expand...

=LEFT(A1,SEARCH("(",A1)-1)
 
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.
Click to expand...

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.
 
or Data => Text to columns, seperator "(" and don't import the right
column (step 3 of the assistent)


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

Regards
Claus Busch
Click to expand...

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.
 
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.
Click to expand...

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


Regards
Claus Busch
 
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
 
then another time Data => Text to columns => fixed width => ok
and the space is gone
Click to expand...


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.
 
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 =>?
Click to expand...

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


Regards
Claus Busch
 
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?
Click to expand...

If I select space then double barreled names get split.

I can only enter one character in the window.
 
you can choose it in step 1 of the assistent.
Data => Text to columns with fixed width works like TRIM
Click to expand...

I highlight the column.

Click on the icon Text to Columns.

Select delimited/next.

Which check box from the five?
 
Am Tue, 23 Aug 2011 12:28:32 +0100 schrieb Saxman:

Select delimited/next.
Click to expand...

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


Regards
Claus Busch
 
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
 
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)
Click to expand...

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.
 
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
 
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.
Click to expand...

See my reply to Pete.

Thanks.
 
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 to expand...

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.
 
Back
Top