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