98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Merge break line on text file
Merge break line on text file
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:How to select certain data in an Excel 2010 workbookIf 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. |