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
Bookmarks