Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|