merge break line text

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Merge break line on text file

Merge break line on text file

resolvedResolved · Medium Priority · Version 2010

Bin has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Access Intermediate course
Access Advanced course
Excel VBA Advanced course

Merge break line on text file

Hi I have some data on the text file and there are some breaking lines. I wonder if there were anyway to merge the content.
Please let me know
Thanks
Bin

RE: merge break line on text file

Hi Bin

Thanks for getting in touch. The simplest way is to Find and Replace the line break character. If you record this as a macro you will have the code for it.

Here's a great tutorial for this process:

http://blog.contextures.com/archives/2013/05/28/find-and-replace-line-breaks-in-excel/

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: merge break line on text file

Hi Gary,
Thanks for your notes. But the "line break" i mean is slightly different.

My raw data was in text format and there is a note column. When i open the exported data, the notes in the column has been broken in different lines so in this case when i do texttocolumn to import the data in excel the notes in the column will be brokendown vertically to servarl cells instead of having all contents in one cell.

For example:
my raw data shows the number of transations have been made. Column P is the "Note" column for each transactions. when i export the raw data. the note has been brokendown like below in text format

Note showed in the system:
"A has not pay the transaction due to ..." (all in one line)
in the exported text file:
"A has not pay the transaction
due to
..." (broken down to three lines)

So if i import it into excel, the notes will be TextToColumn in three different cells. i.e. P2 "A has not pay the transaction", P3 "due to", P3"...".

I was trying to ask if there was anyway to merge the three cell content into one cell in P2.

Hope i have made it clear. Please let me know
Thanks
Bin

RE: merge break line on text file

Hi Bin

Depending where the data is, you could concatenate it like this (assuming the data is in A1, A2 and A3):

Range("A1") = Range("A1") & Range("A2") & Range("A3")

and variations of that.

With Offset it would look like:

ActiveCell = ActiveCell.Offset(1, 0) & ActiveCell.Offset(2, 0) & ActiveCell.Offset(3, 0)

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 9 Sep 2014: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

How to select certain data in an Excel 2010 workbook

If you want to select the correct data set in a page full of data, the most accurate and efficient way of doing this is to use the ''Shift and Click'' technique.

For example: If you want to select all data in cells A2 to E10, then click on cell A2, hold down the Shift key and click on cell E10 and all the data you want to see is highlighted.

Keep holding down the Shift key and you can move from cell E10 to any other cell in the spreadsheet.

View all Excel hints and tips


Server loaded in 0.08 secs.