How to combine multiple lines contained within once cell into one actual line of text

[Woody Pecker]

I have information which I need to import from an external company, but when we receive it sometimes there are text contents within a single cell which appear on separate line (shown in the attached picture).

I need a formula that will concatenate this information onto one single line of text, so in this example "COLLECTION. 0 758 207 1156 - CALL BEFORE NO HGV USE SMALLER 25/5/2020" is required.

Regretfully I'm not up to speed on VBA, so formula would be better.

 

[Fluff]

Just select the column affected, Ctrl H then In the find what type Ctrl J then in the replace box put a space, make sure that "Match entire cell" is not checked & replace all.

 

[Woody Pecker]

Excellent thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Woody Pecker]

@Fluff regretfully I am still having some issues with my spreadsheet. I just couldn't get the Ctrl H Ctrl J method to work enough due to volume of instances within the sheet.

Someone suggested using the following formula, which certainly sorts out the criteria I needed for checking aspects of the text contained within that line of text:-

=SUBSTITUTE(IF('Paste Here'!K2="","",'Paste Here'!K2),CHAR(10)," ")

However, when I come to save the Excel spreadsheet down in CSV format, the single line alignment reverts back to the carriage return format.

Any ideas?

 

[Fluff]

A csv file is a form of text file & as such does not hold formatting or formulae.
You will either have to save as an Excel file, or copy paste the formulae as values.

 

[Woody Pecker]

Buggar