Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
is there any way to split text content in a cell and copy it to another location of cells. for eg: Cell A1(text format) contains 123456789 and i want to split the content into three parts as 123, 456 & 789 and copy the three values into b1,C1 & D1 respectively. can anybody help me? via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
Have you tried the MID function? It should be able to give you what
you want. Rgds, ScottO "via135" wrote in message ... | | is there any way to split text content in a cell and copy it to another | location of cells. for eg: | | Cell A1(text format) contains 123456789 | and i want to split the content into three parts as 123, 456 & 789 and | copy the three values into b1,C1 & D1 respectively. | | can anybody help me? | | via135 | | | -- | via135 | ------------------------------------------------------------------- ----- | via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 | View this thread: http://www.excelforum.com/showthread...hreadid=492639 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
hi..SCOTTO i am able to get the result with the MID function. but the thing is, i want to split one text string into 3 different strings and copy those 3 strings into different cells at one stroke using a formula..! can u help me?! regds via135 ScottO Wrote: Have you tried the MID function? It should be able to give you what you want. Rgds, ScottO "via135" wrote in message ... | | is there any way to split text content in a cell and copy it to another | location of cells. for eg: | | Cell A1(text format) contains 123456789 | and i want to split the content into three parts as 123, 456 & 789 and | copy the three values into b1,C1 & D1 respectively. | | can anybody help me? | | via135 | | | -- | via135 | ------------------------------------------------------------------- ----- | via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 | View this thread: http://www.excelforum.com/showthread...hreadid=492639 | -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
You'll need 3 formulas ... one in each "receiving" cell.
Do you want that? In B1: =LEFT(A1,3) In C1: =MID(A1,4,3) In D1: =RIGHT(A1,3) You can also use TTC, if all your data is as you posted (equal length strings), which will copy the separated data into 3 contiguous columns with "one stroke". Select your column of data, then: <Data <TextToColumns Click on "Fixed Width", then <Next, Click in the "Preview" window and place the break lines where you wish to separate the data, then <Next, Click in the "Destination" box and enter the cell where you wish the "copy" to start, say B1, then <Finish. Is that what you want? Anything else will need code. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "via135" wrote in message ... hi..SCOTTO i am able to get the result with the MID function. but the thing is, i want to split one text string into 3 different strings and copy those 3 strings into different cells at one stroke using a formula..! can u help me?! regds via135 ScottO Wrote: Have you tried the MID function? It should be able to give you what you want. Rgds, ScottO "via135" wrote in message ... | | is there any way to split text content in a cell and copy it to another | location of cells. for eg: | | Cell A1(text format) contains 123456789 | and i want to split the content into three parts as 123, 456 & 789 and | copy the three values into b1,C1 & D1 respectively. | | can anybody help me? | | via135 | | | -- | via135 | ------------------------------------------------------------------- ----- | via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 | View this thread: http://www.excelforum.com/showthread...hreadid=492639 | -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
thks RAGDYER! TTC works fine for contiguous cells! can u explain the code for copying into non-contiguous cells? regds! via135 Ragdyer Wrote: You'll need 3 formulas ... one in each "receiving" cell. Do you want that? In B1: =LEFT(A1,3) In C1: =MID(A1,4,3) In D1: =RIGHT(A1,3) You can also use TTC, if all your data is as you posted (equal length strings), which will copy the separated data into 3 contiguous columns with "one stroke". Select your column of data, then: <Data <TextToColumns Click on "Fixed Width", then <Next, Click in the "Preview" window and place the break lines where you wish to separate the data, then <Next, Click in the "Destination" box and enter the cell where you wish the "copy" to start, say B1, then <Finish. Is that what you want? Anything else will need code. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "via135" wrote in message ... hi..SCOTTO i am able to get the result with the MID function. but the thing is, i want to split one text string into 3 different strings and copy those 3 strings into different cells at one stroke using a formula..! can u help me?! regds via135 ScottO Wrote: Have you tried the MID function? It should be able to give you what you want. Rgds, ScottO "via135" wrote in message ... | | is there any way to split text content in a cell and copy it to another | location of cells. for eg: | | Cell A1(text format) contains 123456789 | and i want to split the content into three parts as 123, 456 & 789 and | copy the three values into b1,C1 & D1 respectively. | | can anybody help me? | | via135 | | | -- | via135 | ------------------------------------------------------------------- ----- | via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 | View this thread: http://www.excelforum.com/showthread...hreadid=492639 | -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting text in a cell..
Code is a little out of my league.
You could use the formulas I posted and enter them in the columns you wish to use, then copy them down the columns. This does however, leave you with formulas returning data, and not the actual data itself. You can then remove the formulas and leave the data behind by copying the columns to themselves, and then "Paste Special", and check "Values". Not exactly a "one shot" procedure. You can also use TTC to copy to non-contiguous columns, but again, not a "one shot" deal. You'll have to do it 3 times, one for each column. After you place the break lines and hit <Next, The first separation (column) is selected by default. Hold down <Shift and click in the second separation so that both are selected. Then click in "Do Not Import". You'll see the headers change from General to Skip. Now, enter your first target column in the "Destination" box, then <Finish. You'll need to do this twice again for the other 2 separations, skipping the first and third, and then the last two. If you find both of these procedures unappealing, you can post to the programming group, and see if they can help you. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "via135" wrote in message ... thks RAGDYER! TTC works fine for contiguous cells! can u explain the code for copying into non-contiguous cells? regds! via135 Ragdyer Wrote: You'll need 3 formulas ... one in each "receiving" cell. Do you want that? In B1: =LEFT(A1,3) In C1: =MID(A1,4,3) In D1: =RIGHT(A1,3) You can also use TTC, if all your data is as you posted (equal length strings), which will copy the separated data into 3 contiguous columns with "one stroke". Select your column of data, then: <Data <TextToColumns Click on "Fixed Width", then <Next, Click in the "Preview" window and place the break lines where you wish to separate the data, then <Next, Click in the "Destination" box and enter the cell where you wish the "copy" to start, say B1, then <Finish. Is that what you want? Anything else will need code. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "via135" wrote in message ... hi..SCOTTO i am able to get the result with the MID function. but the thing is, i want to split one text string into 3 different strings and copy those 3 strings into different cells at one stroke using a formula..! can u help me?! regds via135 ScottO Wrote: Have you tried the MID function? It should be able to give you what you want. Rgds, ScottO "via135" wrote in message ... | | is there any way to split text content in a cell and copy it to another | location of cells. for eg: | | Cell A1(text format) contains 123456789 | and i want to split the content into three parts as 123, 456 & 789 and | copy the three values into b1,C1 & D1 respectively. | | can anybody help me? | | via135 | | | -- | via135 | ------------------------------------------------------------------- ----- | via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 | View this thread: http://www.excelforum.com/showthread...hreadid=492639 | -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=492639 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text to appear at top of cell | New Users to Excel | |||
Wrap text ceases to function in Excel if cell exceeds 9 lines | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Text wider than one cell is not displayed in the next empty cell | Excel Discussion (Misc queries) | |||
Moving text from one cell to another. | Excel Worksheet Functions |