Seperating text

Hi,

I have a column that contains part numbers and a brief, one word,
description. I would like to seperate the description from the part numbers.
The list is pretty long and would rather use a function than to do it
manually. So, is there a function or formula that will do this?


Thank You,

Richard

It all depends on how the data is formatted. If the part number is all
numbers and the description is all alpha-characters, then ASAP Utilities is a
free Add-in available at www.asap-utilities.com that has features that will
strip out all the Alpha and/or all the numerical characters from a string.
If the two groups are always separated by a space or other unique character
then LEFT, Mid, and FIND can be used to separate the groups, or even Data >
TextToColumns could be used..........please post back with more specifics and
someone will be glad to help....

Vaya con Dios,
Chuck, CABGx3


"Richard" wrote:

> Hi,
>
> I have a column that contains part numbers and a brief, one word,
> description. I would like to seperate the description from the part numbers.
> The list is pretty long and would rather use a function than to do it
> manually. So, is there a function or formula that will do this?
>
>
> Thank You,
>
> Richard

Are the part numbers always the same length? An example of some of the data
would help. you could use text to columns, or the RIGHT, LEFT, MID funtions
but it all depends on what the data looks like.

"Richard" wrote:

> Hi,
>
> I have a column that contains part numbers and a brief, one word,
> description. I would like to seperate the description from the part numbers.
> The list is pretty long and would rather use a function than to do it
> manually. So, is there a function or formula that will do this?
>
>
> Thank You,
>
> Richard

Part numbers have numerals and letters, but they are all seperated by a
space. Is this what you are talking about?

Richard

"CLR" wrote:

>
> It all depends on how the data is formatted. If the part number is all
> numbers and the description is all alpha-characters, then ASAP Utilities is a
> free Add-in available at www.asap-utilities.com that has features that will
> strip out all the Alpha and/or all the numerical characters from a string.
> If the two groups are always separated by a space or other unique character
> then LEFT, Mid, and FIND can be used to separate the groups, or even Data >
> TextToColumns could be used..........please post back with more specifics and
> someone will be glad to help....
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Richard" wrote:
>
> > Hi,
> >
> > I have a column that contains part numbers and a brief, one word,
> > description. I would like to seperate the description from the part numbers.
> > The list is pretty long and would rather use a function than to do it
> > manually. So, is there a function or formula that will do this?
> >
> >
> > Thank You,
> >
> > Richard

Yes..........Please provide a few examples....preferably to include both the
normal, and any extremes different from the most of them.

Vaya con Dios,
Chuck, CABGx3

"Richard" wrote:

> Part numbers have numerals and letters, but they are all seperated by a
> space. Is this what you are talking about?
>
> Richard
>
> "CLR" wrote:
>
> >
> > It all depends on how the data is formatted. If the part number is all
> > numbers and the description is all alpha-characters, then ASAP Utilities is a
> > free Add-in available at www.asap-utilities.com that has features that will
> > strip out all the Alpha and/or all the numerical characters from a string.
> > If the two groups are always separated by a space or other unique character
> > then LEFT, Mid, and FIND can be used to separate the groups, or even Data >
> > TextToColumns could be used..........please post back with more specifics and
> > someone will be glad to help....
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Richard" wrote:
> >
> > > Hi,
> > >
> > > I have a column that contains part numbers and a brief, one word,
> > > description. I would like to seperate the description from the part numbers.
> > > The list is pretty long and would rather use a function than to do it
> > > manually. So, is there a function or formula that will do this?
> > >
> > >
> > > Thank You,
> > >
> > > Richard

Are you up for some VBA?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
'' except for decimal points and hyphens.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.-]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

On Thu, 29 Jun 2006 11:49:01 -0700, Richard <[email protected]>
wrote:

>Hi,
>
>I have a column that contains part numbers and a brief, one word,
>description. I would like to seperate the description from the part numbers.
>The list is pretty long and would rather use a function than to do it
>manually. So, is there a function or formula that will do this?
>
>
>Thank You,
>
>Richard

Here are some examples:

CYAN C4815A
MAGENTA C4816A
YELLOW C4817A

Color description and Part number need to go into seperate columns.

Richard



"CLR" wrote:

> Yes..........Please provide a few examples....preferably to include both the
> normal, and any extremes different from the most of them.
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Richard" wrote:
>
> > Part numbers have numerals and letters, but they are all seperated by a
> > space. Is this what you are talking about?
> >
> > Richard
> >
> > "CLR" wrote:
> >
> > >
> > > It all depends on how the data is formatted. If the part number is all
> > > numbers and the description is all alpha-characters, then ASAP Utilities is a
> > > free Add-in available at www.asap-utilities.com that has features that will
> > > strip out all the Alpha and/or all the numerical characters from a string.
> > > If the two groups are always separated by a space or other unique character
> > > then LEFT, Mid, and FIND can be used to separate the groups, or even Data >
> > > TextToColumns could be used..........please post back with more specifics and
> > > someone will be glad to help....
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Richard" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a column that contains part numbers and a brief, one word,
> > > > description. I would like to seperate the description from the part numbers.
> > > > The list is pretty long and would rather use a function than to do it
> > > > manually. So, is there a function or formula that will do this?
> > > >
> > > >
> > > > Thank You,
> > > >
> > > > Richard

If it is always colour followed by space and part number you would probably
be best off using text to columns but if you need a trail you can use

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

for the colour, assume you put that formula in B2, then in C2 use

=TRIM(SUBSTITUTE(A2,B2,""))

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Richard" <[email protected]> wrote in message
news:[email protected]...
> Here are some examples:
>
> CYAN C4815A
> MAGENTA C4816A
> YELLOW C4817A
>
> Color description and Part number need to go into seperate columns.
>
> Richard
>
>
>
> "CLR" wrote:
>
>> Yes..........Please provide a few examples....preferably to include both
>> the
>> normal, and any extremes different from the most of them.
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>> "Richard" wrote:
>>
>> > Part numbers have numerals and letters, but they are all seperated by a
>> > space. Is this what you are talking about?
>> >
>> > Richard
>> >
>> > "CLR" wrote:
>> >
>> > >
>> > > It all depends on how the data is formatted. If the part number is
>> > > all
>> > > numbers and the description is all alpha-characters, then ASAP
>> > > Utilities is a
>> > > free Add-in available at www.asap-utilities.com that has features
>> > > that will
>> > > strip out all the Alpha and/or all the numerical characters from a
>> > > string.
>> > > If the two groups are always separated by a space or other unique
>> > > character
>> > > then LEFT, Mid, and FIND can be used to separate the groups, or even
>> > > Data >
>> > > TextToColumns could be used..........please post back with more
>> > > specifics and
>> > > someone will be glad to help....
>> > >
>> > > Vaya con Dios,
>> > > Chuck, CABGx3
>> > >
>> > >
>> > > "Richard" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I have a column that contains part numbers and a brief, one word,
>> > > > description. I would like to seperate the description from the part
>> > > > numbers.
>> > > > The list is pretty long and would rather use a function than to do
>> > > > it
>> > > > manually. So, is there a function or formula that will do this?
>> > > >
>> > > >
>> > > > Thank You,
>> > > >
>> > > > Richard