Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
I have a cell that looks like this "123, 456, 789, 234". Is there a formula
that I can use that to seperate them instead of doing text-to-column? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
You could use a combination of LEFT, MID and RIGHT functions, for
example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. Pete On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
Is there any way to do that in a single cell?
"Pete_UK" wrote: You could use a combination of LEFT, MID and RIGHT functions, for example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. Pete On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
But haven't you already got the values joined together in a single
cell, and you want to split them out? That's what you would normally use Text-to-columns for, and that's what I thought you wanted to do !! Pete On Mar 8, 1:17 pm, linglc wrote: Is there any way to do that in a single cell? "Pete_UK" wrote: You could use a combination of LEFT, MID and RIGHT functions, for example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. Pete On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
Sorry. Let me clarify. Cell A1 has "123, 456, 789, 234". The method you
suggested requires me to input four formulas in four cells to get the result I want. Is there a formula (array formula maybe?) I can input in cell B1 that can get me the same result as the four formulas you have suggested i.e. B1 = 123, C1 = 456, D1 = 789 and E1 = 234? "Pete_UK" wrote: But haven't you already got the values joined together in a single cell, and you want to split them out? That's what you would normally use Text-to-columns for, and that's what I thought you wanted to do !! Pete On Mar 8, 1:17 pm, linglc wrote: Is there any way to do that in a single cell? "Pete_UK" wrote: You could use a combination of LEFT, MID and RIGHT functions, for example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. Pete On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
You can't put anything in E1 by using a formula in B1, but if you want to
put the same formula in B1 to E1, use =MID($A1,5*COLUMN()-9,3) -- David Biddulph "linglc" wrote in message ... Sorry. Let me clarify. Cell A1 has "123, 456, 789, 234". The method you suggested requires me to input four formulas in four cells to get the result I want. Is there a formula (array formula maybe?) I can input in cell B1 that can get me the same result as the four formulas you have suggested i.e. B1 = 123, C1 = 456, D1 = 789 and E1 = 234? "Pete_UK" wrote: But haven't you already got the values joined together in a single cell, and you want to split them out? That's what you would normally use Text-to-columns for, and that's what I thought you wanted to do !! On Mar 8, 1:17 pm, linglc wrote: Is there any way to do that in a single cell? "Pete_UK" wrote: You could use a combination of LEFT, MID and RIGHT functions, for example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column?- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that works like text-to-column
Thanks for the answers. Whilst the question is not answered, they give me
ideas on how to solve my problem. "David Biddulph" wrote: You can't put anything in E1 by using a formula in B1, but if you want to put the same formula in B1 to E1, use =MID($A1,5*COLUMN()-9,3) -- David Biddulph "linglc" wrote in message ... Sorry. Let me clarify. Cell A1 has "123, 456, 789, 234". The method you suggested requires me to input four formulas in four cells to get the result I want. Is there a formula (array formula maybe?) I can input in cell B1 that can get me the same result as the four formulas you have suggested i.e. B1 = 123, C1 = 456, D1 = 789 and E1 = 234? "Pete_UK" wrote: But haven't you already got the values joined together in a single cell, and you want to split them out? That's what you would normally use Text-to-columns for, and that's what I thought you wanted to do !! On Mar 8, 1:17 pm, linglc wrote: Is there any way to do that in a single cell? "Pete_UK" wrote: You could use a combination of LEFT, MID and RIGHT functions, for example if your text is in A1, then: B1: =LEFT(A1,3) C1: =MID(A1,6,3) D1: =MID(A1,11,3) E1: =RIGHT(A1,3) These can then be copied down the columns. Hope this helps. On Mar 8, 12:45 pm, linglc wrote: I have a cell that looks like this "123, 456, 789, 234". Is there a formula that I can use that to seperate them instead of doing text-to-column?- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works but formula shows in cell??? | Excel Worksheet Functions | |||
combine 111 in one column & .jpg in another thru out entire works | Excel Worksheet Functions | |||
carriage return that works when pasted into a text file (eg. Notep | Excel Discussion (Misc queries) | |||
IF formula works one way and not the other | Excel Worksheet Functions | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) |