Hello,
I have a text file with a lot of data. There are 2 columns. One column is names of company. The other column is summary of conversations (long text with a lot of line break). The delimiter is ; and the separator is ". The problem is that when I try to import the data, excel understand that each line break is a new row which completely mess up my data. What should I do if I want excel to just skip the line break and stick to the given delimiter and the separator?
Dan
Try importing the txt file into Power Query (available as a free add in to Excel 2013). Removing the "Line Break" and then loading into Excel.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi, thank you for your reply. It sounded great until I realized I have Excel 2007 and 2013. Sorry for my mistake. Can I still apply your solution?
Power Query will work with 2013.
https://www.microsoft.com/en-us/down...7-8467cb45bdd9
I see. Is there any other way to do it without Power Query?
Without seeing an actual file, there is no way I can determine what can work.
Rule 03: Cross-posting Without Telling Us
Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.
Post a link to any other forums where you have asked the same question.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Read this to understand why we ask you to do this.
I have added the crosspost reference for you today. Please comply with this and all our rules in the future
https://www.mrexcel.com/forum/excel-...ine-break.html
Sorry. I did not know mrexcel was part of excel forum. Should I close the post on the other forum?
Here below a quick example of my issue: the first column is company name and the second column is conversation.
Upon import, excel would create a line for each line break which is wrong. Let me know if this is more clear.
"Company name";"Conversations"
"PAINT DECORATIONS INC";
"
No info
Called on 1/4
Called again on 2/4
Voicemail
"
"INC LIMITED";"
No info
Called on 1/4
Called again on 2/4
Voicemail
"
The forums are not related. This is common courtesy amongst forums. Did you bother to read the link in post as to why and how it all works. Do not close either post. Just let each know that you have posted elsewhere and the link to that posting.
Thank you for what you have posted, however, we cannot analyze what you have posted. We really need to see the text file so that it can be determined the hidden formatting and how it comes into excel so that it can be determined what has to happen to get you what you need.
First of all sorry for not being careful with the cross post instructions. I will be more careful from now on.
Then you will find here attached the txt file. Let me know.
Instead of opening in Excel. In a new excel file, click on Power Query/Get and Transform. Click on Get Data from Text/CSV and then click on Load. The file loads without CR or LF for me. See Attached.
Wow your excel looks absolutely amazing. This is exactly what I want to do!!!!
As stated before and unfortunately, I do not have Power Query on Excel 2007. Any other way around it?
I checked online and Power Query will not work with my Excel. I do not have later version of Excel. Do you see any other way to do it?
Oh, I thought that you had Version 2013 as noted in post #3.
Doing a little bit of research. I think that you may have to do this through Regex or DOS. I have not been able to create an Excel Solution for you. You may want to google
"Remove Line Feed or Carriage Return from .TXT file.
Last edited by alansidman; 05-23-2019 at 03:53 PM.
Unfortunately, no. It was typo mistakeWithout it, is there a way to still do it?
I found a way to do using notepad++ but it is much more complicated than what I thought because some line breaks should remain since they differentiate between each company and there are almost 3000 lines. I really don't know how to do this. If you have an idea, let me know
Try something like attached (Module1). Wasn't able to post code with securi warning.
Change Folder path (A2) and File Name (B2) in Parameter sheet as needed. Then press the button in Result sheet.
"Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
― Robert A. Heinlein
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks