#1
|
|||
|
|||
Need ca macro to remplace all line breaks inside all cells
Hi, I am totally novice in Excel macros and VBA. I download every day an Excel file from a web site In this file, almost all cells contain 2 data separated by a line break. I need to change all these line breaks in order to go on on my work, but I cannot do it manually because there are thousands. So, I want to create a macro that will change all line breaks in all cells to a semicolon character (or just deleting all line breaks will be fine too). But I don't know how to do it. Many thanks in advance for any help. I use Office Excel 16.24 (Office 365) on MacOS |
#2
|
||||
|
||||
Hi and welcome
do you mean a carriage return ? (CHAR(10)) Perhaps post a small sample sheet ( no pics) to make sure ( click "Go advanced - Manage attachments)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Yes, sure!
In fact, as I am novice in VBA and I have no time to learn VBA enough, I was planing to split the job between Excel and Word. But of course, if I can do everything in Excel, it will be better for me. Yes, what I call "line break" is char(10) character Here is a sample file that explains exactly what I want to do. |
#5
|
||||
|
||||
Code:
Sub blah() 'Sheets("Before").Copy After:=Sheets(Sheets.Count)'only used to copy the sheet to preserve original Set myRange = Intersect(Columns("B:J"), ActiveSheet.UsedRange) Application.DisplayAlerts = False For colm = myRange.Columns.Count To 1 Step -1 If colm <> 2 Then myRange.Columns(colm + 1).EntireColumn.Insert 'Let Excel decide what to do with with strings that look like numbers: myRange.Columns(colm).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:=Chr(10), FieldInfo:=Array(Array(1, 1), Array(2, 1)) 'or: 'Treat the numbers as text and keep them as such (this will preserve any leading zeroes - you might do this if the numbers are account numbers or numbers you neveer need to do arithmetic on: 'myRange.Columns(colm).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:=Chr(10), FieldInfo:=Array(Array(1, 2), Array(2, 2)) End If Next colm Columns("B").Insert myRange.Offset(, -2).Resize(, 1).TextToColumns , DataType:=xlDelimited, Other:=True, OtherChar:="T", FieldInfo:=Array(Array(1, 5), Array(2, 1)) Application.DisplayAlerts = True Range("A1:S1").Value = Array("Date", "Time", "01 Fonds", "01 Fonds 2", "02 Mbres", "03 YUPR", "03 YUPR 2", "04 JLMY", "04 JLMY 2", "05 RuffY", "05 RuffY 2", "06 LeFY", "06 LeFY 2", "07 TatiaY", "07 TatiaY 2", "08 MediaY", "08 MediaY 2", "09 RTFY", "09 RTFY 2") End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Issue with line breaks and paragraph breaks in bullet point document | philiphunter7 | Word | 8 | 05-10-2020 11:51 PM |
Paragraph line breaks | muymalestado | Word | 3 | 09-25-2011 05:06 AM |
Invoking a command line from inside Word | bolpom | Word | 1 | 03-16-2011 03:03 AM |
Line breaks at each line in Web text | ahazelwood | Word | 5 | 01-06-2011 02:37 PM |
Unwanted line breaks | P. Alkuin | Word | 2 | 03-15-2010 09:33 AM |