VBA replace / remove text/line breaks

[halobender]

Hey guys I am trying to write a macro to remove extra line breaks in blocks of text and hope to accomplish this in a loop as opposed to writing each line by hand.

The issue is this, the users that have used this spreadsheet have word wrap turned on and to make a different entry inside the same text box they have used many spaces to drop down a line instead of alt+enter

the ideal macro will search for 3 or more spaces and replace with a vbLf

I also do not need empty line breaks like below

05/21- i need to write this macro because i need to print this correctly

05/23- I really need to figure this out with your help

Ideally it would look like this instead

05/21- i need to write this macro because i need to print this correctly
05/23- I really need to figure this out with your help

My next question is about replacing text/numbers, some of the dates in the text are 5/11/2008, 05/11/2008, 05/11/08, 05-11-08, 05-11-2008, etc

I want all dates to be formatted 05/11 dd/mm and attempted to do this with strings using wildcard characters however my wildcards end up being the replacement text as well

Please help

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[halobender]

well i tried a simple do loop for the spaces but am unsure how to get it to work with 3-** spaces

as far as the dates i tried to make wildcard strings like

Dim date1 as string

date1 = ??/??/????

but when i reference it it actually inputs ??/??/????

[script]

Sub TextReplacer()

Dim DataCount As Integer

DataCount = 2

Do
If ActiveSheet.Cells(DataCount, 1).Value = "" Then
Exit Do
End If

If ActiveSheet.Cells(DataCount, 19).Value = " " Then
ActiveSheet.Cells(DataCount, 19).Value = vbLf
End If

DataCount = DataCount + 1

Loop

End Sub

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[halobender]

umm i guess my previous post did not work.. While what you have offered is a good read it does me absolutely no good as far as getting this loop up and running. thanks for the great info though

 

[PHV]

As you insist to keep your loop:

Sub TextReplacer()
Dim DataCount As Long, x As String
DataCount = 2
Do
  If ActiveSheet.Cells(DataCount, 1).Value = "" Then
    Exit Do
  End If
  x = Trim(ActiveSheet.Cells(DataCount, 19).Value)
  While InStr(x, "    ") > 1
    x = Replace(x, "    ", "   ")
  WEnd
  ActiveSheet.Cells(DataCount, 19).Value = Replace(x, "   ", vbLf)
  DataCount = DataCount + 1
Loop
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[halobender]

works brilliantly, now maybe you can assist with the other issue. when i try to double click in between cell rows to make the row height adjust to the proper necessary size occasionally does not fit all text. this happens even when the cell height is not at its maximum of 546 pixels.

any help here would be great too as this one has me stumped.. i originally thought it was because of all of the extra spaces the users added, but still some are not functioning properly