data entered two lines in one cell

[sunnytahir]

Hello,

I have some data entered two lines in one cell in excell.(Alt+Enter) is used). I want to separate them. i.e.
separate the first part before Alt+Enter
separate the second part after Alt+Enter
in two two cells.

thanks in adv.

 

[xlbo]

For the left side of Alt+Enter, use:
=LEFT(A1,FIND(CHAR(10),A1)-1)
for the right, use:
=RIGHT(A1,LEN(A1)-FIND(CHAR(10),A1))

Or in code:

For each c in Range("A1:A100&quot
testStr = c.text
For i = 1 to len(testStr)
if chr(mid(testStr,i,1))=10 then
LeftStr = Left(testStr,i-1)
RightStr = Right(testStr,Len(testStr)-i)
else
end if
Next c

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[sunnytahir]

Dear Geoff,
the first part of your answer is helpful. thanks.
but the second part " code " does not work. it generates error message. invalid next variable control reference.
i want to seprate the data which is in one column. and i want to seprate it in two columns. first part in col. 1 and second part in col. 2

 

[xlbo]

oops - got lost in the loops:
For Each c In Range("A1:A3&quot
testStr = c.Text
For i = 1 To Len(testStr)
If Asc(Mid(testStr, i, 1)) = 10 Then
LeftStr = Left(testStr, i - 1)
RightStr = Right(testStr, Len(testStr) - i)
Else
End If
Next i
Next
MsgBox "Left side is: " & LeftStr & " and right side is: " & RightStr

should work but please bear in mind that this is not a personal helpdesk for you. You must be willing to take help from here and work some things out on your own - I missed out the last "Next" in my haste but you really should've been able to see that there were 2 loops started and only one finished at the end

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[sunnytahir]

Dear Geoff,

thanks for your kind help. i know this is not my personal helpdesk, but i think people are here to help each other.

regards, sunny