I download a list of data each day, and a lot of it comes with a suffix in
brackets showing the country of origin of the piece of data - (IRE) as an
example.
Because of the suffix, my data doesn't recognise the two things as the same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

Showing a couple examples will insure a more specific suggestion.
Is the data 1234567(IRE) or ABCDEFG(IRE) for example?
What you want it to look like afterwards? $12,345.67 and/or Abcdefg??


"Dark Horse" wrote in message
...
I download a list of data each day, and a lot of it comes with a suffix in
brackets showing the country of origin of the piece of data - (IRE) as an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

Check out Data | Text to Columns.
Set the Delimiter to the close paren, ), using the other box.

tj

"Dark Horse" wrote:

I download a list of data each day, and a lot of it comes with a suffix in
brackets showing the country of origin of the piece of data - (IRE) as an
example.
Because of the suffix, my data doesn't recognise the two things as the same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

I don't know my suffixes from my prefixes . . .
Use the Open paren as the Delimiter, instead, if you are removing a suffix
and there are no spaces between the number and the (IRE).
If there is a space, use that as the Delimiter.

tj

"tjtjjtjt" wrote:

Check out Data | Text to Columns.
Set the Delimiter to the close paren, ), using the other box.

tj

"Dark Horse" wrote:

I download a list of data each day, and a lot of it comes with a suffix in
brackets showing the country of origin of the piece of data - (IRE) as an
example.
Because of the suffix, my data doesn't recognise the two things as the same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.



"JMay" wrote in message news:gqFsd.425$ln.325@lakeread06...
Showing a couple examples will insure a more specific suggestion.
Is the data 1234567(IRE) or ABCDEFG(IRE) for example?
What you want it to look like afterwards? $12,345.67 and/or Abcdefg??


"Dark Horse" wrote in message
...
I download a list of data each day, and a lot of it comes with a suffix
in
brackets showing the country of origin of the piece of data - (IRE) as
an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

I have no idea at all what this is about, as someone said to me just now
"show a couple of examples"
There is a name, then a space and then the opening parenthesis - no numbers
of any kind are involved.
I can't use a space as a delimiter as there as spaces in the data too - and
if I use the parenthesis that will leave a space after the data which will
stop the data being recognised.


"tjtjjtjt" wrote in message
...
I don't know my suffixes from my prefixes . . .
Use the Open paren as the Delimiter, instead, if you are removing a suffix
and there are no spaces between the number and the (IRE).
If there is a space, use that as the Delimiter.

tj

"tjtjjtjt" wrote:

Check out Data | Text to Columns.
Set the Delimiter to the close paren, ), using the other box.

tj

"Dark Horse" wrote:

I download a list of data each day, and a lot of it comes with a
suffix in
brackets showing the country of origin of the piece of data - (IRE) as
an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

On Sun, 5 Dec 2004 21:03:50 -0000, "Dark Horse"
wrote:

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.




If there is only one <space between the end of the name and the first
parenthesis, then:

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

If there are a variable number of spaces, then perhaps:

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

might work. However, if there is more than one space between any of the names,
TRIM will remove these, also.


--ron

Another way is to use data|text to columns.
Delimited by (
(Only if it isn't used in the actual name)

Then choose to skip (do not import) that field.



Dark Horse wrote:

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.

"JMay" wrote in message news:gqFsd.425$ln.325@lakeread06...
Showing a couple examples will insure a more specific suggestion.
Is the data 1234567(IRE) or ABCDEFG(IRE) for example?
What you want it to look like afterwards? $12,345.67 and/or Abcdefg??


"Dark Horse" wrote in message
...
I download a list of data each day, and a lot of it comes with a suffix
in
brackets showing the country of origin of the piece of data - (IRE) as
an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave





--

Dave Peterson

As Ron pointed out above...use the "Text to Columns" function, then apply the
Trim function if there is only one space between the last name and the open
paren.

Here's how:

First make sure you have some empty columns to the right of your data. I
used Col "A" for the data, Col "B" & "C" for the delimited data and Col "D"
for the Trim function. You, of course, can adjust these to suit your Sheet.

Highlight all the data in Col "A" that you want to manipulate. Then under
the "Data" menu on the upper tool bar, click on "Text to Columns". Step
through the various windows. In the opening window make sure the option
"Delimited" is ticked. Next window choose "Other" and insert "(" in the
blank window next to "Other". (Without the quotes). Next window, the column
data format should be General and I chose to have the data sent to Col "B",
thus retaining the original data for now. Then click "Finish". Now in Col
"B" should be the name (first and last and middle initial if there was one)
and the suffix should be in Col "C".

Now in Col "D" enter this formula:

=TRIM(B1)

Copy this down as far as your data is. Then copy Col "D" and "Paste
Special/Value" back onto itself. The names should then be as you want,
assuming there is only one space between names and between the name and the
suffix. Then delete the unwanted Col's.

HTH,

"Dark Horse" wrote:

I have no idea at all what this is about, as someone said to me just now
"show a couple of examples"
There is a name, then a space and then the opening parenthesis - no numbers
of any kind are involved.
I can't use a space as a delimiter as there as spaces in the data too - and
if I use the parenthesis that will leave a space after the data which will
stop the data being recognised.


"tjtjjtjt" wrote in message
...
I don't know my suffixes from my prefixes . . .
Use the Open paren as the Delimiter, instead, if you are removing a suffix
and there are no spaces between the number and the (IRE).
If there is a space, use that as the Delimiter.

tj

"tjtjjtjt" wrote:

Check out Data | Text to Columns.
Set the Delimiter to the close paren, ), using the other box.

tj

"Dark Horse" wrote:

I download a list of data each day, and a lot of it comes with a
suffix in
brackets showing the country of origin of the piece of data - (IRE) as
an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

oooooooops.....as long as that post was, I still missed something that should
be self apparent but just in case:

Enter the Trim function in Cell "D1" and copy down.

"Don" wrote:

As Ron pointed out above...use the "Text to Columns" function, then apply the
Trim function if there is only one space between the last name and the open
paren.

Here's how:

First make sure you have some empty columns to the right of your data. I
used Col "A" for the data, Col "B" & "C" for the delimited data and Col "D"
for the Trim function. You, of course, can adjust these to suit your Sheet.

Highlight all the data in Col "A" that you want to manipulate. Then under
the "Data" menu on the upper tool bar, click on "Text to Columns". Step
through the various windows. In the opening window make sure the option
"Delimited" is ticked. Next window choose "Other" and insert "(" in the
blank window next to "Other". (Without the quotes). Next window, the column
data format should be General and I chose to have the data sent to Col "B",
thus retaining the original data for now. Then click "Finish". Now in Col
"B" should be the name (first and last and middle initial if there was one)
and the suffix should be in Col "C".

Now in Col "D" enter this formula:

=TRIM(B1)

Copy this down as far as your data is. Then copy Col "D" and "Paste
Special/Value" back onto itself. The names should then be as you want,
assuming there is only one space between names and between the name and the
suffix. Then delete the unwanted Col's.

HTH,

"Dark Horse" wrote:

I have no idea at all what this is about, as someone said to me just now
"show a couple of examples"
There is a name, then a space and then the opening parenthesis - no numbers
of any kind are involved.
I can't use a space as a delimiter as there as spaces in the data too - and
if I use the parenthesis that will leave a space after the data which will
stop the data being recognised.


"tjtjjtjt" wrote in message
...
I don't know my suffixes from my prefixes . . .
Use the Open paren as the Delimiter, instead, if you are removing a suffix
and there are no spaces between the number and the (IRE).
If there is a space, use that as the Delimiter.

tj

"tjtjjtjt" wrote:

Check out Data | Text to Columns.
Set the Delimiter to the close paren, ), using the other box.

tj

"Dark Horse" wrote:

I download a list of data each day, and a lot of it comes with a
suffix in
brackets showing the country of origin of the piece of data - (IRE) as
an
example.
Because of the suffix, my data doesn't recognise the two things as the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave

That solution leaves IRE) and takes out what I want.
Else it takes the first ( out and shifts the rest into a separate column
which is easy to delete but still leaves the space after the name which is
as if I hadn't changed a thing.

"Dave Peterson" wrote in message
...
Another way is to use data|text to columns.
Delimited by (
(Only if it isn't used in the actual name)

Then choose to skip (do not import) that field.



Dark Horse wrote:

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the
same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.

"JMay" wrote in message
news:gqFsd.425$ln.325@lakeread06...
Showing a couple examples will insure a more specific suggestion.
Is the data 1234567(IRE) or ABCDEFG(IRE) for example?
What you want it to look like afterwards? $12,345.67 and/or
Abcdefg??


"Dark Horse" wrote in message
...
I download a list of data each day, and a lot of it comes with a
suffix
in
brackets showing the country of origin of the piece of data - (IRE)
as
an
example.
Because of the suffix, my data doesn't recognise the two things as
the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave





--

Dave Peterson

and where do I enter this, and how?
I'm an almost complete beginner, and some of the solutions offered seem to
be taking longer than doing it by hand - and are far more complicated.

"Ron Rosenfeld" wrote in message
...
On Sun, 5 Dec 2004 21:03:50 -0000, "Dark Horse"
wrote:

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the
same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.




If there is only one <space between the end of the name and the first
parenthesis, then:

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

If there are a variable number of spaces, then perhaps:

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

might work. However, if there is more than one space between any of the
names,
TRIM will remove these, also.


--ron

On Mon, 6 Dec 2004 11:36:52 -0000, "Dark Horse"
wrote:

and where do I enter this, and how?
I'm an almost complete beginner, and some of the solutions offered seem to
be taking longer than doing it by hand - and are far more complicated.

You can enter it in some blank column in the same first row as your data. Then
copy/drag the formula down so the cell reference will change appropriately.

Substitute for A1 the first cell of your data.

Then you can either use this new column for the source of your now corrected
data, or you can copy it (Edit/Copy) and do a Edit/Paste Special/ Values over
the original data.

Another method would be to use a VBA macro.

Right click on the sheet tab and select View Code.
From the VB Editor main menu, select Insert/Module and paste the code below
into the window that opens.

On your worksheet, select the range of data to modify.
<alt-F8 and select the name of the macro; then Run.

This code perhaps has an advantage of not removing extra spaces that are WITHIN
the name, although it will remove both leading and trailing spaces.

======================================
Sub TrimCountry()
Dim c As Range
For Each c In Selection
If InStr(c.Text, " (") 0 Then
c = Left(c.Text, InStr(c.Text, " ("))
c = RTrim(LTrim(c.Text))
End If
Next c
End Sub
=======================


--ron

You didn't choose the "do not import column (skip)" button for that 2nd field.

And you could do another text to columns, but use Fixed width (instead of
delimited).

Delete any lines that excel guessed and don't add any yourself.

the leading/trailing spaces will be gone.



Dark Horse wrote:

That solution leaves IRE) and takes out what I want.
Else it takes the first ( out and shifts the rest into a separate column
which is easy to delete but still leaves the space after the name which is
as if I hadn't changed a thing.

"Dave Peterson" wrote in message
...
Another way is to use data|text to columns.
Delimited by (
(Only if it isn't used in the actual name)

Then choose to skip (do not import) that field.



Dark Horse wrote:

The data is as John Conroy (IRE) and James Edgar (USA) all down one
column - and I'd like it to look like John Conroy and James Edgar, the
same
as it began but without the brackets and suffix. There are no numbers or
special characters whatsoever involved in this column, just text.

"JMay" wrote in message
news:gqFsd.425$ln.325@lakeread06...
Showing a couple examples will insure a more specific suggestion.
Is the data 1234567(IRE) or ABCDEFG(IRE) for example?
What you want it to look like afterwards? $12,345.67 and/or
Abcdefg??


"Dark Horse" wrote in message
...
I download a list of data each day, and a lot of it comes with a
suffix
in
brackets showing the country of origin of the piece of data - (IRE)
as
an
example.
Because of the suffix, my data doesn't recognise the two things as
the
same
so I end up having to remove all the suffixes by hand.
Is there any way of accomplishing this in a neater and quicker way?
Thanks in advance
Cheers
Dave





--

Dave Peterson

--

Dave Peterson