Adding Next Line Character in a Cell

[surya prakash]

Hi,

I have the following text in a excel sheet in seperate cells




SampleText1 101 501
SampleText1 102 502
SampleText1 103 503
SampleText1 104 504


This text is generated in Excel using formulaes; this data has to be made available to another program in this TEXT format..




SampleText1
101 501
SampleText1
102 502
SampleText1
103 503
SampleText1
104 504



Presently, I am pasting the text from the excel sheet into Notepad and and am using "Enter Key" to create a new line.

As the data is large, I am wondering if it is possible to automate either in excel by adding a "Next Line Character" or using VBA

Any help will be highly appreciated.

thanks

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Cells(i, "A").Value = .Cells(i, "A").Value & vbLf & _
.Cells(i, "B").Value & " " & _
.Cells(i, "C").Value
.Cells(i, "B").Resize(, 2).ClearContents
Next i
End With
End Sub

[surya prakash]

Hello XLD,

Many thanks for your prompt response..

I have run your code in my program, though it is appending the data as required.
But when I pasted it into notepad, I am getting a small rectangle symbol (which I suppose is next line characted) instead of next line..

I am wondering if it is possible to send the text appended text into a text directly instead of modifying it in Excel...

thanks

[unmarkedhelicopter]

You probably need to do them as separate lines in Excel too.

[surya prakash]

yeah, I considered that;
But was not able to do that as there is some sorting that needs to be done before splitting the text...

[Bob Phillips]

As UMH says, if you don't want the character, you need separate lines


Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = .Cells(i, "B").Value & " " & .Cells(i, "C").Value
.Cells(i, "B").Resize(, 2).ClearContents
Next i
End With
End Sub

[RichardSchollar]

Hi

You could give the following a go (it adds a new worksheet containing the output):


Sub Split_Data()
Dim InpArr As Variant, OutArr As Variant
Dim i As Long
InpArr = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim OutArr(1 To UBound(InpArr, 1) * 2, 1 To 1)
For i = LBound(InpArr, 1) To UBound(InpArr, 1) Step 1
OutArr(i * 2 - 1, 1) = InpArr(i, 1)
OutArr(i * 2, 1) = InpArr(i, 2) & " " & InpArr(i, 3)
Next
With Worksheets.Add
.Range("A1:A" & UBound(OutArr, 1)).Value = OutArr
End With
End Sub

Best regards

Richard

[unmarkedhelicopter]

You should sort it and then run the code.

[surya prakash]

Hi,
Thanks alot..

The screen is refreshing very rapidly; is there someway of stopping that??

thanks

[unmarkedhelicopter]

Yeah but you have to be careful ...
put
application.screenupdating = false
before your main code, and
application.screenupdating = true
at the end

[RichardSchollar]

Hi,
Thanks alot..

The screen is refreshing very rapidly; is there someway of stopping that??

thanks

This particular issue shouldn't be a problem with the code I posted as it only writes to the screen once...

Richard

[surya prakash]

hey thanks..
btw, is it possible to add tab instead of " "

[surya prakash]

This particular issue shouldn't be a problem with the code I posted as it only writes to the screen once...

Richard

I have large number of rows, so I set screenrefreshing=false to overcome the problem.

The function even runs faster then before...

[RichardSchollar]

The following inserts a tab instead of a space:



Sub Split_Data()
Dim InpArr As Variant, OutArr As Variant
Dim i As Long
InpArr = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim OutArr(1 To UBound(InpArr, 1) * 2, 1 To 1)
For i = LBound(InpArr, 1) To UBound(InpArr, 1) Step 1
OutArr(i * 2 - 1, 1) = InpArr(i, 1)
OutArr(i * 2, 1) = InpArr(i, 2) & Chr$(9) & InpArr(i, 3)
Next
With Worksheets.Add
.Range("A1:A" & UBound(OutArr, 1)).Value = OutArr
End With
End Sub


Richard

[surya prakash]

Thanks alot XLD, Richard & unmarkedhelicopter for your prompt response...

[surya prakash]

really appreciate your prompt response...