Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Spliting the contents of a cell to other cells
Hello,
I have a formula/function question. Say I have a cell, For this question I'll Call it Cell A1 and in that cell the is a list of product numbers 312.00/ 541.0, 541-100\ the only common separator is a space between each number, and the numbers range between 4 and seven digits. Is the a formula of function that would separate the product number into three different columns? Thanks, Lime |
#2
|
|||
|
|||
check out <Data<Text to columns
"Lime" wrote: Hello, I have a formula/function question. Say I have a cell, For this question I'll Call it Cell A1 and in that cell the is a list of product numbers 312.00/ 541.0, 541-100\ the only common separator is a space between each number, and the numbers range between 4 and seven digits. Is the a formula of function that would separate the product number into three different columns? Thanks, Lime |
#3
|
|||
|
|||
On Thu, 13 Oct 2005 11:39:06 -0700, "Lime"
wrote: Hello, I have a formula/function question. Say I have a cell, For this question I'll Call it Cell A1 and in that cell the is a list of product numbers 312.00/ 541.0, 541-100\ the only common separator is a space between each number, and the numbers range between 4 and seven digits. Is the a formula of function that would separate the product number into three different columns? Thanks, Lime The following splits the 23 character A1 cell into 8, 7 & 8 characters in B1:D1. B1 = =LEFT(A1,FIND(" ",A1)) C1= =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))) D1= =RIGHT(A1,LEN(A1)-FIND(" ",A1,LEN(B1)+LEN(C1))) This includes the comma in the middle which you may want to avoid. In which case change C1 to: =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)-2))) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
Thank you very much guys, you've been very helpful as usal,.
Thanks, Lime "Richard Buttrey" wrote: On Thu, 13 Oct 2005 11:39:06 -0700, "Lime" wrote: Hello, I have a formula/function question. Say I have a cell, For this question I'll Call it Cell A1 and in that cell the is a list of product numbers 312.00/ 541.0, 541-100\ the only common separator is a space between each number, and the numbers range between 4 and seven digits. Is the a formula of function that would separate the product number into three different columns? Thanks, Lime The following splits the 23 character A1 cell into 8, 7 & 8 characters in B1:D1. B1 = =LEFT(A1,FIND(" ",A1)) C1= =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))) D1= =RIGHT(A1,LEN(A1)-FIND(" ",A1,LEN(B1)+LEN(C1))) This includes the comma in the middle which you may want to avoid. In which case change C1 to: =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)-2))) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
|
|||
|
|||
On the main menu, click DataText to Columns, choose Delimited, click Next, choose 'Space' and click Finish. Your data should now be three columns. Good Luck -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=475981 |
#6
|
|||
|
|||
This does not work when you have mulitpal rows containg numers with different
lenths "swatsp0p" wrote: On the main menu, click DataText to Columns, choose Delimited, click Next, choose 'Space' and click Finish. Your data should now be three columns. Good Luck -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=475981 |
#7
|
|||
|
|||
As long as each has a SPACE between them, Text To Columns WILL work. Follow these instructions: Select the range of data On the main menu, click DataText to Columns... In the box that opens, make sure "Delimited" is selected as "Original data type" Click Next In the Delimiters section, Uncheck all EXCEPT "Space" (if you want to eliminate the commas or slash, you can list those as well (you can have only one 'other' delimiter-you could rerun TTC to eliminate the backslash)) Click Next Verify the Destination meets your needs Click Finish Regardless of the number of characters between spaces, each space will begin a new column. Good Luck -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=475981 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display contents of a specific cell even if a group of cells is mo | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
How do I link many cells to one particular cell? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |