Tuesday, October 18, 2011

yogi_Concatenate Data From A Range Of Columns Separated By Specified Delimiter Ignoring Blanks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Guy said:
Hello Group, I am working on data where I need to combine around 200 columns and put the data in 1 column for around 10,000 rows. I have tried concatenate.
The problem is if i use comma (,) between columns there will be too many commas in the New column I have created to display the data collectively.
If i dont use the comma's my data is displayed but you cannot differentiate for example 1, 0.65, 7 from 10.657 which is how it appears at the moment.
If any one understands this, I need advice on the best way to proceed
In the following proposed solution presented here I have concatenated data in columns C:IV and I have used ArrayFormula and RegExReplace functions that are available in Google Docs spreadsheet ... in EXCEL one can enter the ArrayFormula using CtrlShiftEnter rather than just Enter, and the RegExReplace may have to be deployed as a custom written function (see http://lispy.wordpress.com/2008/10/17/using-regex-functions-in-excel/)