How to give a string of data into different columns
in [Functions]
|
Prev: Import external data
Next: How do I unhide collom a?
From: Jackpot on 4 Jun 2010 03:30 Thanks mate..or change $A$1 to $A1 "Ms-Exl-Learner" wrote: > 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
From: Ms-Exl-Learner on 4 Jun 2010 03:30 Wow!!! Unnecessarily I have used Indirect, Address & Row functions. I am laughing myself for my correction method. -------------------- (Ms-Exl-Learner) -------------------- "Jackpot" wrote: > Thanks mate..or change $A$1 to $A1 > > "Ms-Exl-Learner" wrote: > > > 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
From: Teethless mama on 4 Jun 2010 09:24
Text to column > select comma as your delimited if you preferred formula then try this: =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),(COLUMN(A$1)-1)*99+1,99)) copy across and down "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 |