Need ca macro to remplace all line breaks inside all cells

Chibiberu · #1 03-28-2019, 11:30 PM

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

Pecoflyer · #2 03-29-2019, 12:48 AM

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)

Chibiberu · #3 03-29-2019, 02:22 AM

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.

Pecoflyer · #4 03-29-2019, 04:21 AM

Several techniques are described here

p45cal · #5 03-30-2019, 10:56 AM

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