spliting data in a cell

[Guest]

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

 

[Ron Rosenfeld]

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

 

[matt]

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

 

[Guest]

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

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

 

[Guest]

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

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

assume A1 holds 1206

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