LinkBack URL
About LinkBacks
Pumba007 
Hello Team ,
I need to split data in
cell A1 "1Apples Red 2Oranges Citrus 4Plums 167Rose Yellow" into column data with numbers as delimiters :
A B C
1 Apples Red
2 Oranges Citrus
4 Plums
167 Rose Yellow
Thanks in Advance.
Mani
Hello Team ,
I need to split data in
cell A1 "1Apples Red 2Oranges Citrus 4Plums 167Rose Yellow" into column data with numbers as delimiters :
A B C
1 Apples Red
2 Oranges Citrus
4 Plums
167 Rose Yellow
Thanks in Advance.
Mani
Use the Flash Fill functionality on the Ribbon
Look here---->http://forums.excelcentral.com/index...bers-in-excel/
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks Alan.
I have the data in A1 and for flash fill to work shouldn't this be transposed to columns. I don't find a way to split / transpose the data into columns with Numbers as starting string / delimiter.
i.e
A1 "1Apples Red 2Oranges Citrus 4Plums 167Rose Yellow"
=>
A
1 Apples Red
2 Oranges Citrus
167 Rose Yellow
Excel file with data as is and desired attached.
This link looks like it may be the solution you are looking for.
http://www.mrexcel.com/forum/excel-q...o-columns.html
Tested and worked for your example.
Last edited by alansidman; 04-28-2016 at 02:40 PM.
Alan Thanks a ton
This one is pretty crazy.
Text string is in A2
Enter this formula in B5 and copy down
Formula:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2,0,"|"),1,"|"),2,"|"),3,"|"),4,"|"),5,"|"),6,"|"),7,"|"),8,"|"),9,"|"),"|||","|"),"||","|"),"|",REPT(" ",99)),99*ROWS(A$2:A2),99))
then enter second formula in A5 and copy down
Formula:=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A$2,B$5,""),B$6,""),B$7,""),B$8,"")," ",REPT(" ",99)),99*(ROWS(A$2:A2)-1)+1,99))
v A B 5 1 Apples Red 6 2 Oranges Citrus 7 4 Plums 8 167 Rose Yellow
Last edited by AlKey; 04-28-2016 at 04:50 PM. Reason: Added Excel spreadsheet
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Alkey Thanks. It is crazy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks