Text in Concatenated cell, Need add comma after every 4th character

Hi Guys,

Hoping someone can help me here.

I have a spreadsheet with approx. 3000 rows of data.

There is a number (formatted as text) in column A of each row.

Columns B through to G contain a 4 character reference (can consist of letters or both letters and numbers).

Reading across each row, it is unlikely that their is an entry in each column (ie some could be blank).

I would like a formula that will read across reach row (columns B to G) and put them in a cell on the same row (ie column I) as comma separated values.

If a particular column is blank to don't want to two (or more) adjacent commas.

The way I have thought of (and I know there is must be a simpler way), is to use the concatenate function in column H (for row values of the columns G to H). Then using the MID function on the Concatenated value in H whilst inserting a comma after every fourth character and placing the result in column I.

Although, shown in my example (attached spreadsheet) I don't want a comma after the last set of four characters (ie I don't want a comma on the very end of the string in column I).

Any help or guidance anyone can offer would be great.

Kind regards

Michael

=concatenate(if($b2<>"",$b2&",",""),if($c2<>"",$c2&",",""),if($d2<>"",$d2&",",""),if($e2<>"",$e2&",",""),if($f2<>"",$f2&",",""),if($g2<>"",$g2&",",""))

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

lengthy...but in one cell

Formula:

Please Login or Register  to view this content.

=substitute(concatenate(if($b2<>"",$b2&",",""),if($c2<>"",$c2&",",""),if($d2<>"",$d2&",",""),if($e2<>"",$e2&",",""),if($f2<>"",$f2&",",""),if($g2<>"",$g2&",","")),",","",counta(b2:g2))

Please Login or Register  to view this content.

Are you able/willing to use a VBA function?

The resultant formula would be as simple as:

=concatall(B2:G2,",")