Macro to Remove/Replace Line Breaks within a Cell

mstuf

Active Member
Joined
Feb 4, 2003
Messages
321
Office Version
  1. 2016
Platform
  1. Windows
Good Afternoon -

I've been trying to Record and Edit a Macro to Remove ALL Line Break Characters from the Text ( NOT FORMULAS ) in a Active Cell and replace them with - ( Space DASH Space ) - Leaving the Cell Active. I want the Macro to Only apply to the Cell thats Active when the Macro is Run. ( Unless its possible to apply it to a highlighted Group of cells as well )

I read some past Treads Here and Tried Using Find and Replace - Control + J to record my starter macro but that moves me onto the next instance. I cant seem to find much information on how to contain the Code to just the Active Cell.

Code:
Sub RemoveLineBreakAttempt7()
'
' RemoveLineBreakAttempt7 Macro
' Macro recorded 5/25/2009 by Mike
'

'
    Range("B9462").Select
    Cells.Find(What:="" & Chr(10) & "" & Chr(10) & "", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCell.Replace What:="" & Chr(10) & "" & Chr(10) & "", Replacement:=" - ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

Manually Using Find and Replace - Control + J - Replace with - does sort of Work. It finds Some of the Line Breaks but NOT ALL. ?? I saw in the Previous Search Threads that there are other Characters that initiate Line Breaks ?
How can I tell what Character is causing the Line Breaks in my Cells ?? and incorporate them in my removal.
One Type of Text with Visible Line Breaks in Particular with imported information often has 4 to 12 Line Breaks in it that the Find and Repace routine Skips Right Over.


My Skills with VBA are Beginner -- I usually Record something and Edit it. I'm pretty good with static routines but have a hard time with variables - Constantly reading to Try to Learn.


As Always -- Any Help Appreciated !!

I am available to Post/Provide any information that I can.

Thanks for Looking
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Code:
Dim oneCell As Range
For Each oneCell In Selection
    oneCell.Value = Application.Substitute(Application.Substitute(CStr(oneCell.Value), vbLf, vbCr), vbCr, "-")
Next oneCell
 
Upvote 0
Thanks Mike -- Works Great -- Looks So Simple Now. I read Mr Excel Boards Each Day Before Dinner. I search for Items within my Comprehension or Needs to read. It leads me to new areas to read and learn.

Much of what knowledge I have has been gathered in battle. I've Learned so much here but I kind of have the Cart Before the Horse. I really need to find a Class or Tutorial to Better Understand the Basics of VBA to apply what I dabble to record and edit.

If you have time -- can you tell me why its written , vbLf, vbCr), vbCr,

Thank You again.
 
Last edited:
Upvote 0
The outer formula substitutes "-" for vbCR, the inner formula changes all the vbLf to vbCr so they also will be changed to "-"
 
Upvote 0
This code will remove various AZCII characters form the strings in a single slected cell or a range of selected cells.

Code:
Option Explicit
Sub RemoveCarraigeReturnsAndLineFeeds()
    Dim rng As Range
    Dim c As Range
    Set rng = Selection
    For Each c In rng
        c = Replace(c, Chr(8), "")    'Backspace
        c = Replace(c, Chr(9), "")    'Tab
        c = Replace(c, Chr(10), "")   'Line feed 
        c = Replace(c, Chr(13), "")   'Carriage Return
        c = Replace(c, Chr(127), "") 'Spaecial Space
    Next c
End Sub
This code will parse a string in a single selected cell and dispaly each character.
Code:
Sub DisplayCharacters()
    Dim i As Integer
    Dim Character As String
    Dim Char As String
    Dim rng As Range
    Set rng = ActiveCell
    For i = 1 To Len(rng)
        Character = Mid(rng, i, 1)
        Char = Character
        Select Case Asc(Character)
        Case 8: Character = "Back Space"
        Case 9: Character = "Tab"
        Case 10: Character = "Line Feed"
        Case 13: Character = "Charriage Return"
        Case 32: Character = "Space"
        Case 127: Character = "Special Space"
        Case Is < 32: Character = "Special Character"
        End Select
        MsgBox rng & Chr(13) & Chr(13) & "Character: " & Chr(9) & Character & Chr(13) & "ASCII Code: " & Chr(9) & Asc(Char)
    Next i
End Sub
 
Upvote 0
Thanks again Mike

and Thank you Bill -- Some of the Code / Commands are beyond me so far - but thats how I learn. Finding something useable and playing with it. I see several Commands there that I have not attempted before. One that I have been looking for a Working example of to get started with for a Project I have in mind.

As I have stated before, I really need to go back as Start from the Beginning Somewhere. The Record Macro thing is Wonderful and then learning edit to modify and get back to Work - but its left me without many of the Basics I need to use what knowledge I have. ( I always say I know just enough to be Dangerous ) The Nearest Actual LIVE Class to Attend is a 50+ mile trip -- I have looked at a Couple of the Tutorial Programs -- Many seem to go from Basic to Brain Surgery in Two Steps.

I know you are both Beyond this but if you or any other Readers have a Suggestion of a Tutorial / Course that would be comprehensive enough on the basics before moving on, to fill in Foundation that I need ?? Or should I look to a Book ?


Thanks
 
Upvote 0
Code:
Dim oneCell As Range
For Each oneCell In Selection
    oneCell.Value = Application.Substitute(Application.Substitute(CStr(oneCell.Value), vbLf, vbCr), vbCr, "-")
Next oneCell

mike thank you really. how can we apply it only to A2?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top