Mick

iTrader: (1)

A mate has a spreadsheet that needs the hard carrige returns within the cells (put in by Alt + Enter to get lines within the cell) removing.

Instead of deleting the offending text withing each cell he is under the impression he was once shown how to do it with 'find and replace'. The problem is what is the code for the hard carrige return that needs to be replaced. He thinks it begins with a '~'

Cheers

Mick

beemerboy

if you write a macro to do this, the alt and enter gives you this

& Chr(10)

not sure if this helps, i'm sure it prob doesnt.

BB

4lex L

I also believe the best way would be to use a macro. You used to be able to insert any ascii code in to text boxes by holding down alt and then typing the corrosponding code (e.g. 10) using the number keys on the keypad. I use a laptop so I don't have a keypad to test it with.

If your mate just wishes to replace the text he may want to try formatting the cells in a certain way and then use the find/repalce function to search for the specific format using a letter that always appears in the text as the search criteria.

Hope this helps if you were able to make any sense of that

Alex

Mick

iTrader: (1)

Thanks guys...

All he wants to do is get rid of the carriage returns to get the text as a continuous line. - I'm not sure how your advice fits in...

Mick

V5

You can usually do this - most laptops have the number keypad marked on the letters. You normally have to press and hold something like an "Fn" key, or similar.

4lex L

Your right, cheers V5 its been a long day , it didn't work anyway

The only way to remove the line breaks (I think) is programmatically using a macro. If I get a spare minute or two I'll write you a small function shouldn't take long.

Alex

4lex L

Ok try the following code, it will remove the carriage returns and replace them with spaces. It'll only work for the first carriage return and will not process subsequent occurrences in the same cell.

Public Function RemoveLine(area As Range)
Dim i, j
Dim str As String
Dim nPos As Integer

'loop through all cells in range
For i = 1 To area.Rows.Count
For j = 1 To area.Columns.Count
str = area(i, j).Value
'find position of the carriage return
nPos = InStr(1, str, Chr(10))
'if it exists replace it with a space
If nPos > 0 Then area(i, j).Value = Mid(str, 1, nPos - 1) & " " & Mid(str, nPos + 1, Len(str))
Next j
Next i
End Function

Private Sub CommandButton1_Click()
RemoveLine Worksheets("Sheet1").Range("A1:Z99")
End Sub

Mick

iTrader: (1)

Thanks Alex - I'll give it a go...

Presumably it can be run through a couple of times to clear all line breaks????

Cheers

Mick

4lex L

Yeah that should work!

4lex L

Run the code several times over Jason It'll only filter the first carriage return it comes across. If you run the code over again the first carriage return will have already been converted to a space, so what was the second return now becomes the first and so will be filtered. If the cells contain a maximum of, say, 10 carriage returns then the following amendment to the command button click event handler should suffice:

Private Sub CommandButton1_Click()
Dim i as Integer
For i = 0 To 9
RemoveLine Worksheets("Sheet1").Range("A1:Z99")
Next i
End Sub

4lex L

Sorry Jason, couldn't get that to work

4lex L

Aah cell A1, I was trying elsewhere on the sheet

Edited to say it still only seem to remove the 1st carriage return so you get:

10 20
30

Nevermind Jason , better luck at the quiz tonight hey

[Edited by 4lex L - 6/11/2003 6:57:15 PM]

[Edited by 4lex L - 6/11/2003 6:57:45 PM]

4lex L

Alright smart ****

Mick

iTrader: (1)

Jason

Excellent job, copied & pasted into Excel VB 'new module' and it works 'straight out of the box'. Very good - Gold star

Cheers

Mick

Dario

Can any of the experts on here help me with a problem I'm having with Access database import into Excel

- I want the hard carriage returns that are in the access database to be in the excel cells - at the moment the text is one continious line in excel and it's drving me crazy.

Regards

Dario