Related Posts
Aloha. Autoincorrect strikes again.
Are y’all safe after the tornadoes?
Bowl Leader
Additional Posts in Consulting
Pumpkin spice basic season is upon us!
Top consulting firms right there!!
Anybody else watched "Animals" on HBO?
Chief
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
What's wrong with text to columns then copy/paste with transpose?
Jokes on you. I'm the global index match practice leader
Hope this helps
SOS what
This is sad...
If there is a delimiter in the values I would just pull it into powerquery and use the “split column by delimiter” function to break it into multiple rows. Takes all of 2 minutes
Like this: https://sfmagazine.com/post-entry/november-2017-excel-split-delimited-data-into-new-rows/
I know how can I get it in different column cells based on text to column but not sure if there’s a way to get it one below the other
If values are fixed length, use some variation of LEFT or RIGHT functions
Just copy and transpose, if I'm understanding the ask correctly
(1) Convert a,b,c to different cells using text to column as comma delimited, (2) then transpose columns to rows (3) populate abc value to A, B and C
You can use LEFT or RIGHT function to take the parts you want. But like they said, transpose is easiest
Thanks for the response folks. I won’t prefer transpose as there are multiple columns with such case in my database and that would lead to several column add. Further, I am trying to automate this process and hence it would unnecessarily complicate it
I’d recommend using a combination of LEFT, RIGHT, LEN and FIND functions to pull out what you need then. For example in instance of “A, B, C”
=LEFT(Cell, FIND(“,”, Cell, 1)-1) = A
=LEFT(RIGHT(Cell, FIND(“,”, Cell, 1)-1), 1) = B
=RIGHT(Cell, FIND(“,”, Cell, 1)-1) = C
You can mix LEN in there with the FIND function to make a bit more dynamic if your lists aren’t always the same length of characters