How to give a string of data into different columns
in [Functions]
|
Prev: Import external data
Next: How do I unhide collom a?
From: Elton Law on 4 Jun 2010 01:56 Super expert, If I have cell containing a series of data like this .... 1,12,9 1,2,9 1,22,17,18 23,23,1,9 24,21,1 1,23,11 22,1 2,3 Is it possible to use function or command to split them into columns? 1 12 9 1 2 9 1 22 17 18 23 23 1 9 24 21 1 1 23 11 22 1 2 3 I don't want to use "TEXT TO COLUMN" as some of the addresses can be overwritten. Thanks so much, Regards, Elton
From: Jackpot on 4 Jun 2010 02:27 Hi 'Elton Law' Try the below =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",", REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255)) "Elton Law" wrote: > Super expert, > > If I have cell containing a series of data like this .... > > 1,12,9 > 1,2,9 > 1,22,17,18 > 23,23,1,9 > 24,21,1 > 1,23,11 > 22,1 > 2,3 > > > Is it possible to use function or command to split them into columns? > > 1 12 9 > 1 2 9 > 1 22 17 18 > 23 23 1 9 > 24 21 1 > 1 23 11 > 22 1 > 2 3 > > I don't want to use "TEXT TO COLUMN" as some of the addresses can be > overwritten. > > Thanks so much, > Regards, > Elton
From: Elton Law on 4 Jun 2010 02:27 Oh.. you are really super expert. Thanks so much. Thanks ... that's really helpful "Jackpot" wrote: > With data in cell A1; apply the below formula in cell B1 and copy to the > right as required.. > > "Jackpot" wrote: > > > Hi 'Elton Law' > > > > Try the below > > > > =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",", > > REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255)) > > > > > > "Elton Law" wrote: > > > > > Super expert, > > > > > > If I have cell containing a series of data like this .... > > > > > > 1,12,9 > > > 1,2,9 > > > 1,22,17,18 > > > 23,23,1,9 > > > 24,21,1 > > > 1,23,11 > > > 22,1 > > > 2,3 > > > > > > > > > Is it possible to use function or command to split them into columns? > > > > > > 1 12 9 > > > 1 2 9 > > > 1 22 17 18 > > > 23 23 1 9 > > > 24 21 1 > > > 1 23 11 > > > 22 1 > > > 2 3 > > > > > > I don't want to use "TEXT TO COLUMN" as some of the addresses can be > > > overwritten. > > > > > > Thanks so much, > > > Regards, > > > Elton
From: Roger Govier on 4 Jun 2010 02:42 Hi Elton Mark your block of data>Data>Text to columns>Delimited>select Comma as delimited>Finish -- Regards Roger Govier "Elton Law" <EltonLaw(a)discussions.microsoft.com> wrote in message news:471706BF-00B0-426C-AEF1-9D9B11813937(a)microsoft.com... > Super expert, > > If I have cell containing a series of data like this .... > > 1,12,9 > 1,2,9 > 1,22,17,18 > 23,23,1,9 > 24,21,1 > 1,23,11 > 22,1 > 2,3 > > > Is it possible to use function or command to split them into columns? > > 1 12 9 > 1 2 9 > 1 22 17 18 > 23 23 1 9 > 24 21 1 > 1 23 11 > 22 1 > 2 3 > > I don't want to use "TEXT TO COLUMN" as some of the addresses can be > overwritten. > > Thanks so much, > Regards, > Elton > > __________ Information from ESET Smart Security, version of virus > signature database 5170 (20100603) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5170 (20100603) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: Ms-Exl-Learner on 4 Jun 2010 03:00
Jacob Sir small correction is required. =TRIM(MID(SUBSTITUTE("," & INDIRECT(ADDRESS(ROW(),1))& REPT(",",6),",",REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255)) May I know the reason, why you have changed your name? When I see the formula I guessed the formula should be provided by the real experts like you. But here I am trying to get the result for more than half an hour but I can't able to make it in single formula. Today I have learned 1 more from your post. -------------------- (Ms-Exl-Learner) -------------------- "Jackpot" wrote: > Hi 'Elton Law' > > Try the below > > =TRIM(MID(SUBSTITUTE("," & $A$1& REPT(",",6),",", > REPT(CHAR(32),255)),COLUMNS($B$1:B$1)*255,255)) > > > "Elton Law" wrote: > > > Super expert, > > > > If I have cell containing a series of data like this .... > > > > 1,12,9 > > 1,2,9 > > 1,22,17,18 > > 23,23,1,9 > > 24,21,1 > > 1,23,11 > > 22,1 > > 2,3 > > > > > > Is it possible to use function or command to split them into columns? > > > > 1 12 9 > > 1 2 9 > > 1 22 17 18 > > 23 23 1 9 > > 24 21 1 > > 1 23 11 > > 22 1 > > 2 3 > > > > I don't want to use "TEXT TO COLUMN" as some of the addresses can be > > overwritten. > > > > Thanks so much, > > Regards, > > Elton |