Joining Cells

Occasional Visitor

Windows 10

MS 365 Version 2112 

 

I am looking to join cells in the following manner

The 2 cells look like this -  

3337022, 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 

2 Replies

@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.

Riny_van_Eekelen_0-1643118466879.png

 

 

@MelissaTaylor 

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.