How to deconcatinate
I need to be able to separate out the five sections separated by commas into five adjacent blank columns
ED Class, Wednesday, 01/20/21, 9:00 am - 12:30 pm, Happy Hospital
Thanks, Laura
Answers
-
Hi @laura.buchanan
Hope you are fine, try the following formula for each column:
1- column 1 =MID(g@row, 1, 8)
2- column 2 =MID(g@row, 10, 10)
3- Column 3 =MID(g@row, 21, 9)
4- Column 4 =MID(g@row, 31, 19)
5- Colum 5 =MID(g@row, 51, 19)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam, you're brilliant! Thanks so much!! I do need a tad more guidance. The number of letters in some of the data is different in length, so it caused things to get "off" a little.
My formula worked great for New provider class on Tuesday, but when I dragged it up the entire column, Wednesday took up more letters, so the formula got off.
Similarly the ED class is shorter, so those got off as well.
Is there an adjustment you recommend to the formula?
-
Hey @laura.buchanan
See if this works for you. Sorry for the delay but it took a bit to work out.
Column 1 =LEFT(g@row, FIND((","), g@row) - 1)
Column 2 =MID(g@row, FIND(", ", g@row) + 1, FIND(", ", g@row, FIND(", ", g@row) + 1) - LEN([1]@row) - 2)
Column 3 =MID(g@row, FIND(",", g@row, FIND(",", g@row) + 1) + 1, FIND(",", g@row, FIND(",", g@row, FIND(",", g@row) + 1) + 1) - (FIND(",", g@row, FIND(",", g@row) + 1) + 1))
Column 4 =MID(g@row, FIND(",", g@row, FIND(",", g@row, FIND(",", g@row) + 1) + 1) + 2, FIND(",", g@row, FIND(",", g@row, FIND(",", g@row, FIND(",", g@row) + 1) + 1) + 1) - 1 - FIND(",", g@row, FIND(",", g@row, FIND(",", g@row) + 1) + 1) - 1)
Column 5 =RIGHT(g@row, LEN(g@row) - (LEN([1]@row) + LEN([2]@row) + LEN([3]@row) + LEN([4]@row) + 8))
The '8' in column 5 is the sum of the extra character spaces between each of the comma's and the beginning of the word. All the heavy lifting came from Paul's post here
cheers,
Kelly
-
Kelly, Thanks SO much. I'm am saving your notes for the next time I have to jump through deconcatenate hoops. I actually engaged Rich Taglieri from the customer support line to help come up with a better strategy for what I was trying to accomplish and as it turns out I'm not going to have to do the deconcatinating. BUT I really do appreciate your time and I am for sure saving your guidance in my secret smartsheet tip sheet. Take care, Laura
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!