I've got a spreadsheet with a description colunm. The current descriptions
have repeated partnumbers over and over throught the row. Is there something
I can use to leave the first two entries (left - right) and remove the rest.
Each row has a different part number duplicated. Example - HEWLETT PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black, 2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks

Hi,
Take a look at CPearson Web, go almost to the bottom to find your specific
case

http://www.cpearson.com/excel/deleting.htm

if this helps please click yes, thanks

"mccloud" wrote:

I've got a spreadsheet with a description colunm. The current descriptions
have repeated partnumbers over and over throught the row. Is there something
I can use to leave the first two entries (left - right) and remove the rest.
Each row has a different part number duplicated. Example - HEWLETT PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black, 2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks

this is not removing duplicate rows, but duplicates within a text string

its complex.
1) identify the 'code' you can use
MID( text, instrRev(text," ")+1)
this finds the first space from the end - assumes that the last piece of
text will be the code

2) count how many times the code appears in the text
3) replace all but the first and last code's with nulls

your example seems to have 3 codes still



"mccloud" wrote in message
...
I've got a spreadsheet with a description colunm. The current
descriptions
have repeated partnumbers over and over throught the row. Is there
something
I can use to leave the first two entries (left - right) and remove the
rest.
Each row has a different part number duplicated. Example - HEWLETT
PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black,
2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM
Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks

How regular is your text? By that I mean, does the text you want to remove
**always** follow the letters OEM? If not, in what way can the text to be
removed be identified? I'm guessing it is not always the 4th "word" in as I
would expect other vendor's names to vary. Also, will the text always be
repeated twice (as your example shows) with all following references
removed? In other words, a single example doesn't give us enough information
upon which to decide how to handle your other data... unless you give us
some kind of "rules" about how all your data is structured.

--
Rick (MVP - Excel)


"mccloud" wrote in message
...
I've got a spreadsheet with a description colunm. The current
descriptions
have repeated partnumbers over and over throught the row. Is there
something
I can use to leave the first two entries (left - right) and remove the
rest.
Each row has a different part number duplicated. Example - HEWLETT
PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black,
2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM
Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks

Data is random and there isn't any patterns.

"Rick Rothstein" wrote:

How regular is your text? By that I mean, does the text you want to remove
**always** follow the letters OEM? If not, in what way can the text to be
removed be identified? I'm guessing it is not always the 4th "word" in as I
would expect other vendor's names to vary. Also, will the text always be
repeated twice (as your example shows) with all following references
removed? In other words, a single example doesn't give us enough information
upon which to decide how to handle your other data... unless you give us
some kind of "rules" about how all your data is structured.

--
Rick (MVP - Excel)


"mccloud" wrote in message
...
I've got a spreadsheet with a description colunm. The current
descriptions
have repeated partnumbers over and over throught the row. Is there
something
I can use to leave the first two entries (left - right) and remove the
rest.
Each row has a different part number duplicated. Example - HEWLETT
PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black,
2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM
Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks

Then it will be impossible to write code to handle the variations that can
exist (code needs to be able to make decisions, based on pre-established
rules, in order to operate)... if you cannot identify where in the text your
duplicated value will exist, or what *unique fixed* text might exist in
front or behind it, (so that the code can find the first example of it), nor
how many of them to preserve, then there is no way to write code that can do
what you want.

--
Rick (MVP - Excel)


"mccloud" wrote in message
...
Data is random and there isn't any patterns.

"Rick Rothstein" wrote:

How regular is your text? By that I mean, does the text you want to
remove
**always** follow the letters OEM? If not, in what way can the text to be
removed be identified? I'm guessing it is not always the 4th "word" in as
I
would expect other vendor's names to vary. Also, will the text always be
repeated twice (as your example shows) with all following references
removed? In other words, a single example doesn't give us enough
information
upon which to decide how to handle your other data... unless you give us
some kind of "rules" about how all your data is structured.

--
Rick (MVP - Excel)


"mccloud" wrote in message
...
I've got a spreadsheet with a description colunm. The current
descriptions
have repeated partnumbers over and over throught the row. Is there
something
I can use to leave the first two entries (left - right) and remove the
rest.
Each row has a different part number duplicated. Example - HEWLETT
PACKARD
OEM Q2624A HP - Toner Cartridges for HP LaserJet 1150 - Q2624A, Black,
2500,
Print Cartridge, Model No. Q2624A NEW OEM HP LaserJet 1150 Series
Q2624A
HEWQ2624A Q2624A. I need it to look like this... HEWLETT PACKARD OEM
Q2624A
HP - Toner Cartridges for HP LaserJet 1150 - Q2624A Black, 2500, Print
Cartridge, Model No. NEW OEM HP LaserJet 1150 Series HEWQ2624A.

Is this possible in Excel or Access?

Thanks