spliting data in a cell
 

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

spliting data in a cell
 

On Mon, 12 Mar 2007 08:06:54 -0700, Maax
wrote:

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

1. You could use, instead of a formula, Data/Text to Columns and specify the
"/" as the delimiter.

If you require formulas:

Part Number: =LEFT(A1,FIND("/",A1)-1)
Revision: =MID(A1,FIND("/",A1)+1,255)


--ron

spliting data in a cell
 

On Mar 12, 9:06 am, Maax wrote:
Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

In Excel, there are a few ways that you can do this. I'll mention two
ways to do it in Excel and then one function in VBA that will do it as
well.

"Text to Columns" (Menu Bar: Data/Text to Columns/Delimited/Other:
"/").

You could also do a =SEARCH() to find the place where "/" is located.
Then use that placement number in a =LEFT to return whatever is to the
left of the "/" (i.e. LEFT(string, # returned by SEARCH - 1)) and then
use a =RIGHT(string, LEN(string) - # returned by SEARCH). option will
do what you are looking for.

In VBA the delimit function is SPLIT, so you could do something like
this:

myLeft = split(string, "/")(0)
myRight = split(string, "/")(1)

Hopefully this helps.

Matt

spliting data in a cell
 

Hi Ron, many thanks i will give that a try.

"Ron Rosenfeld" wrote:

On Mon, 12 Mar 2007 08:06:54 -0700, Maax
wrote:

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

1. You could use, instead of a formula, Data/Text to Columns and specify the
"/" as the delimiter.

If you require formulas:

Part Number: =LEFT(A1,FIND("/",A1)-1)
Revision: =MID(A1,FIND("/",A1)+1,255)


--ron

spliting data in a cell
 

Hi Matt, I tried rons formula but i got a circular reference error. I was
probably putting it in the wrong cell.
I tried the text to columns and that worked a treat.

many thanks

"matt" wrote:

On Mar 12, 9:06 am, Maax wrote:
Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

In Excel, there are a few ways that you can do this. I'll mention two
ways to do it in Excel and then one function in VBA that will do it as
well.

"Text to Columns" (Menu Bar: Data/Text to Columns/Delimited/Other:
"/").

You could also do a =SEARCH() to find the place where "/" is located.
Then use that placement number in a =LEFT to return whatever is to the
left of the "/" (i.e. LEFT(string, # returned by SEARCH - 1)) and then
use a =RIGHT(string, LEN(string) - # returned by SEARCH). option will
do what you are looking for.

In VBA the delimit function is SPLIT, so you could do something like
this:

myLeft = split(string, "/")(0)
myRight = split(string, "/")(1)

Hopefully this helps.

Matt

spliting data in a cell
 

Keep it simple! Let's say the part/rev text is in A1. Then here are your
formulas:

Part: =LEFT(A1,FIND("/",A1)-1)
Rev: =MID(A1,FIND("/",A1)+1,999)

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC



"Maax" wrote:

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

spliting data in a cell
 

Hi Andy, yes that one worked fine for me also. Many thanks.

I have another requirement. In a cell i have dates that have been imported
from another system. They are entered as follows,

1206 would be December 2006
605 would be June 2005
0101 would be January 2001
There are no date older than 0101.

I would like to convert the cells or copy to new cells in the following
format 01/12/2006. Because there is no specific day of the month in the
original data i am happy to have 01 as the default.

is this easily achievable?

many thanks












"Andy Smith" wrote:

Keep it simple! Let's say the part/rev text is in A1. Then here are your
formulas:

Part: =LEFT(A1,FIND("/",A1)-1)
Rev: =MID(A1,FIND("/",A1)+1,999)

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC



"Maax" wrote:

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks

spliting data in a cell
 

assume A1 holds 1206

in another cell
="01/"&left(A1,len(A1)-2)&"/20"&right(A1,2)

--
Regards,
Tom Ogilvy


"Maax" wrote:

Hi Andy, yes that one worked fine for me also. Many thanks.

I have another requirement. In a cell i have dates that have been imported
from another system. They are entered as follows,

1206 would be December 2006
605 would be June 2005
0101 would be January 2001
There are no date older than 0101.

I would like to convert the cells or copy to new cells in the following
format 01/12/2006. Because there is no specific day of the month in the
original data i am happy to have 01 as the default.

is this easily achievable?

many thanks












"Andy Smith" wrote:

Keep it simple! Let's say the part/rev text is in A1. Then here are your
formulas:

Part: =LEFT(A1,FIND("/",A1)-1)
Rev: =MID(A1,FIND("/",A1)+1,999)

--
* Please click Yes if this was helpful *
Andy Smith
Senior Systems Analyst
Standard and Poor''s, NYC



"Maax" wrote:

Hello all, i have a cell that has a partnumber in it and a revision
number. As in "1234567/A". The part number and issue can be in any format and
any length(numbers or letters in any order). There is always a "/" to
seperate the part number from the revision. I require a formula/code that
will copy the part number prior to the "/" in to a cell and also i require
the revision after the "/" to another cell.

Any help would be appreciated.

many thanks