October 18, 2018
This isn't strictly an Excel issue but there's a lot of talent on this forum and I thought that someone may have a solution.
I need to migrate data from SQL Server to Salesforce using csv files (unfortunately I'm stuck with csv). In one of my transformations I create a derived column from the values within several other columns, resulting in a 'long text' column (let's call this derived column 'AggText').
So for example say AggText is derived by concatenating the values from the columns Name, City and Gender: one record for Aggtext might be, say, 'Jack-Paris-male' (but ultimately I don't want the components to be separated by dashes - read on).
Once inserted into Salesforce I want the components of AggText to be on separate lines, so in the previous example the data on the page layout would look like this:
The problem is I can't get those components to end up on separate lines. I've tried various combinations of double quotes, single quotes, line feed characters, carriage return characters etc - no good. If I was starting with the csv then I would just use an 'Alt-Enter' at the end of each component, but starting with SQL Server I can't get any line break to come through to Excel.
My only solution (and its a bit agricultural) is to insert a character between the components that is unlikely to be in the data (say, the '^' character) and once the data is in Excel I do a global replace of that character with ctrl+j (ie. line feed). I'd rather not have to do this extra step at all.
Has anyone done this successfully before?
July 16, 2010
Once you've merged the columns with the hyphen, select the merged column > Transform tab > Replace Values. In the Advanced Options check 'replace using special characters' > Line Feed (see image below):
When you close & load to Excel you'll need to format the column with 'Wrap Text' so that the line feed is executed.
October 18, 2018
I'm not sure what you mean by "select the merged column > Transform tab ...". I'm on Excel 2010 so I don't have a Transform tab, and my Find/Replace function doesn't feature the advanced options you mention.
The point though was to see how I could make this happen within SQL Server and I guess its just one of things that can't be done until MS puts their mind to it.