Microsoft Office Forums Need ca macro to remplace all line breaks inside all cells

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-28-2019, 11:30 PM
Chibiberu Chibiberu is offline Need ca macro to remplace all line breaks inside all cells Windows 7 64bit Need ca macro to remplace all line breaks inside all cells Office 2013
Novice
Need ca macro to remplace all line breaks inside all cells
 
Join Date: May 2014
Location: Japan
Posts: 11
Chibiberu is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-29-2019, 12:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need ca macro to remplace all line breaks inside all cells Windows 7 64bit Need ca macro to remplace all line breaks inside all cells Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 03-29-2019, 02:22 AM
Chibiberu Chibiberu is offline Need ca macro to remplace all line breaks inside all cells Mac OS X Need ca macro to remplace all line breaks inside all cells Office 2016 for Mac
Novice
Need ca macro to remplace all line breaks inside all cells
 
Join Date: May 2014
Location: Japan
Posts: 11
Chibiberu is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Book2.xlsx (26.7 KB, 7 views)
Reply With Quote
  #4  
Old 03-29-2019, 04:21 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need ca macro to remplace all line breaks inside all cells Windows 7 64bit Need ca macro to remplace all line breaks inside all cells Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Several techniques are described here
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 03-30-2019, 10:56 AM
p45cal p45cal is offline Need ca macro to remplace all line breaks inside all cells Windows 10 Need ca macro to remplace all line breaks inside all cells Office 2016
Expert
 
Join Date: Apr 2014
Posts: 280
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Issue with line breaks and paragraph breaks in bullet point document philiphunter7 Word 6 04-06-2018 12:31 AM
Need ca macro to remplace all line breaks inside all cells 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
Need ca macro to remplace all line breaks inside all cells Line breaks at each line in Web text ahazelwood Word 5 01-06-2011 02:37 PM
Need ca macro to remplace all line breaks inside all cells Unwanted line breaks P. Alkuin Word 2 03-15-2010 09:33 AM


All times are GMT -7. The time now is 04:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft