How to modify Split Text on New Line

[Zahid Ansari]

Hi,

I want to modify Split Text on New Line provided by you for all rows of first column please help me.


Sub splitText()
'splits Text active cell using ALT+10 char as separator
Dim splitVals As Variant
Dim totalVals As Long

splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals

End Sub

 

[Debraj]

Hi Zahid,

Welcome to the forum..

Try this one..

Sub Macro1()
Columns("A:A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    OtherChar:="" & Chr(10) & ""
End Sub

 

[Zahid Ansari]

Hi Debraj,

Thanks a lot for helping me I will try it I tell you the result later.

Thanks,
Best Regards

 

[Zahid Ansari]

Hi Debraj,

Yes this code is work but for first row only when I select multiple rows it dose not work for all selected rows. What can I do if I want to more than 100 or 500 rows split data together.

Thanks,
Best Regards

 

[Zahid Ansari]

Hi Debraj,

I apologies for the post

"Yes this code is work but for first row only when I select multiple rows it dose not work for all selected rows. What can I do if I want to more than 100 or 500 rows split data together."

Your code is really work for all column sorry for that.

Thanks,
Best Regards

 

[Debraj]

Hi Zahid..

Thanks.. for the feedback..

 

[Zahid Ansari]

Hi Debraj,

I have a problem with macro now previously it was working well but now I am not getting desired result I am unable to resolve it pleas help me. I have attached the file where I show the result of macro only Name is split out please check and tell me the problem what is happened wrong to me.

Thanks,
Best Regards

 

[Debraj]

Hi Zahid..

I am also stumped..
I dont know why above code is not working..

BTW.. I just changed the code to below.. and its working..

Sub Macro1()
Columns("A:A").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
  OtherChar:=Chr(10)
End Sub

PS: Can someone please look into this.. and suggest.. what is the difference b/w

OtherChar:="" & Chr(10) & "" and OtherChar:=Chr(10)

 

[Debraj]

gulp!!

 

[Zahid Ansari]

Hi Debraj,

Same result I have found from your new code. Which version of excel you are using I am working on 2007.

 

[NARAYANK991]

Hi Deb ,

If you do a manual Text to Columns , you will see that when you select Other , you can enter only one character ; thus , you cannot have something like "" & CHR(10) & "" ; of course I do not know why you need to use this construct ; any specific reason ?

Narayan

 

[Zahid Ansari]

Hi Debraj,

Thanks a lot for helping me but I don't know why this happened I try to find out but I am unable to find. This macro code is now running smoothly. Now I have saved it I think there will be no problem in future to me. Thanks ton on ton.

Thanks,
Best Regards