Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spliting a cell | Excel Discussion (Misc queries) | |||
Spliting data | Excel Discussion (Misc queries) | |||
help with spliting data cells | New Users to Excel | |||
spliting out data in one field into two | Excel Programming | |||
Reduce a value by spliting the data | Excel Programming |