Help removing line breaks of 2 kinds from Excel file 2

[DannyTEM]

Hello,

I have an excel database which was exported from a query in business. Line breaks are present but in addition to windows line breaks which apart from the obvious are invisible, those square block symbols are also present. Normally the windows line breaks are no problem and I wold export to csv which is the required format.

However it seems those squares are forcing double breaks so in the CSV files there are extra line break which is causing my php script which processes the csv to fall over.

This has been driving me nuts so any help gratefully received. Thanks, Dan

Dan Morgan -

http://www.ski-review.com

 

[PaulTEG]

Select the 2 characters, CTRL+H (replace), all with blanks

HTH
--Paul

 

[DannyTEM]

Hi Paul,

Thanks, I tried that however, and if you cut and paste the squares they paste as nothing so the F&R never works.

Dan

Dan Morgan -

http://www.ski-review.com

 

[DannyTEM]

In addition a F&R also throws up a "Your formula is too long" error.

Dan Morgan -

http://www.ski-review.com

 

[TonyJollans]

Hi DannyTEM,

The squares are Carriage Returns (ASCII 13 characters). Your source data presumably has Carriage Return / Line Feed combinations and data in Excel cells uses only the Line Feed, leaving the Carriage Return as you see it.

Copy and Paste usually takes care of these things behind the scenes (which is, in a sense, what is happening with your csv creation), but you're not getting your data that way. I don't think there's any way to change them in Excel without using VBA. Basic code would be like ..

[blue]For Each Cel in [i]Your_Range[/i]
    Cel.Value = Replace(Cel, Chr(13),"")
Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DannyTEM]

Hi Tony, thanks for that!

One question (I am very Macro illiterate) where you have Your_Range, is that a range as in the range of cells in the column like F1:F9999 or the workbook name?

Thanks

Dan

Dan Morgan -

http://www.ski-review.com

 

[TonyJollans]

Hi Dan,

It is a range of cells - replace it with, as in your example, [blue]Range("F1:F9999")[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DannyTEM]

Hi Tony,

Thanks, still getting a compile error for some reason.

Is there prehaps someother value I am assuming is a reserved name/function when in fact it is a user (me) filled in value?

For Each Cel In Range("F1:F689")
    Cel.Value = Replace(Cel, Chr(13), "")
Next

Dan Morgan -

http://www.ski-review.com

 

[TonyJollans]

Hi Dan,

What is the error you are getting?

And where have you put the code?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DannyTEM]

Hi Tony,

The error is

Compile Error:
Invalid outside procedure, and it highlights the first line of the macro.

I am Alt+F11 to get to VBA editor, clicking on my sheet on the left, pasting the code in (below), then going to Debug > Compile. it is not showing up in excel either when I go to run a Macro.

Sorry very limited VBA/Macro knowledge.

Dan

Dan Morgan -

http://www.ski-review.com

 

[TonyJollans]

Hi Dan,

The code should be alright where you put it although it would be better in a code module (to create one, Select Insert > Module from the Menu Bar).

The problem you have is because I just posted a snippet of code and not a whole procedure; it needs to be inside a Sub. Cut and paste this in its entirety to replace what you have ..

[blue]Sub RemoveCR()

Dim Cel as Range

For Each Cel In Range("F1:F689")
    Cel.Value = Replace(Cel, Chr(13), "")
Next

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DannyTEM]

Sorry code here:

For Each Cel In Range("F1:F689")
    Cel.Value = Replace(Cel, Chr(13), "")
Next

Dan Morgan -

http://www.ski-review.com

 

[DannyTEM]

Sorry I did not see you reply, please ignore my last post.

I will check out your suggestion, many many thanks for your help!

Dan

Dan Morgan -

http://www.ski-review.com

 

[DannyTEM]

Wow it only went and worked!

Cheers Tony, cannot thank you enough!

Dan Morgan -

http://www.ski-review.com

 

[DannyTEM]

Okay, it is still leaving squares in some places in the actual csv export viewed in a text editor.

Whats the line break code so I can use that marco to remove all of the windows carriage returns within excel?

I figure if I lose a bit of paragraph formatting it is not the end of the world.

Thanks

Dan

Dan Morgan -

http://www.ski-review.com

 

[TonyJollans]

Hi Dan,

Well, Line Feeds are Chr(10) but I don't know whether removing all of them will give what you want.

If you get further issues, can you cut and paste text here (including the squares) and, with luck, we'll be able to identify them.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[lmcate2]

Thank you. I have used your suggestion and It works