Jan 25 2022 05:37 AM
Windows 10
MS 365 Version 2112
I am looking to join cells in the following manner
The 2 cells look like this -
33370 | 22, 23, 47, 51, 52, 52, 53, 54, 55, 56, 58, 58, 59, 59, 63, 76, 76, 77, 77, 78, 78, 79, 79, 80, 81, 82, 99, AI, AQ, AR, AS, CC, CR, ET, EY, GA, GC, GJ, GK, GR, GU, GY, GZ, KX, PT, PT, Q5, Q6, QJ, XE, XE, XP, XP, XS, XS, XU, XU |
And I want the result set to look like this :
3337022 |
3337023 |
3337047 |
3337051 |
etc
Jan 25 2022 05:49 AM
@MelissaTaylor With PowerQuery, this is a piece of cake. See attached file. Press the "Enable Content" button if you want to look into the query. I notice that there are duplicates in the second column. I left them for now, but they can be easily removed befor or after merging with the number in the first column.
Jan 25 2022 05:52 AM
Alternatively, with a formula, assuming that the cells are A1 and B1:
=A1&MID(B1,4*SEQUENCE(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1)-3,2)
This will automatically spill to as many cells as needed.