Multiple newline in single cell

T

Thinkn

New Member
Joined
Oct 10, 2018
Messages
4
Hi there. I've been trying to format a cell which was made from a screen scrape from an oracledb. Unfortunately it took the screen text and copied into a one line text in the cell. It’s still holding the white space but it doesn’t look right with lines merging into each other. I have been trying to research anything that will loop in the cell to insert new lines in the cell. These are notes I could't get right.


The screen has 24 rows and 80 spaces wide (which translates well to text characters in excelusing the Len formula)There are nospecific text to reference before and after using Chr(10) like other examples. I need this to insert a new line every 80 characters x 24 . Any ideas are appreciated
Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]SubFormatCell()[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana] 'splits Text active cell using ALT+10 char asseparator[/FONT][/COLOR]




[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]  Dim FixForm As Range[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    i = 24[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]  Const LineLength As Long = 80[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]   Dim str As String[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]




[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]




[COLOR=#222222][FONT=Verdana]    'str = "Line 1" & vbLf &"Line 2" & vbLf & "Line 3"[/FONT][/COLOR]




[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line1 = Mid(cl.Value, 1, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line2 = Mid(cl.Value, 81, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line3 = Mid(cl.Value, 161, 80)[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line4 = Mid(cl.Value, 241, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line5 = Mid(cl.Value, 321, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line6 = Mid(cl.Value, 401, 80)[/FONT][/COLOR]



[COLOR=#222222][FONT=Verdana]    Line7 = Mid(cl.Value, 481, 80)[/FONT][/COLOR]



[COLOR=#222222][FONT=Verdana]    Line8 = Mid(cl.Value, 561, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line9 = Mid(cl.Value, 641, 80)[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line10 = Mid(cl.Value, 721, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line11 = Mid(cl.Value, 801, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line12 = Mid(cl.Value, 881, 80)[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line13 = Mid(cl.Value, 961, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line14 = Mid(cl.Value, 1041, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line15 = Mid(cl.Value, 1121, 80)[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line16 = Mid(cl.Value, 1201, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line17 = Mid(cl.Value, 1281, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line18 = Mid(cl.Value, 1361, 80)[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]    Line19 = Mid(cl.Value, 1441, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line20 = Mid(cl.Value, 1521, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line21 = Mid(cl.Value, 1601, 80)[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]    Line22 = Mid(cl.Value, 1681, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line23 = Mid(cl.Value, 1761, 80)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    Line24 = Mid(cl.Value, 1841, 80)[/FONT][/COLOR]



[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]    For Each cl InActiveSheet.Range("FixForm")[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]


[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]        If InStr(cl.Value, " ") >0 Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]         'SUBstitute(FixForm,""," "/n,80)[/FONT][/COLOR]




[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]            'cl.Value = Replace(cl.Value,Mid(cl.Value, 500, 1), " " & vbNewLine, 1, 1, vbTextCompare)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]



[COLOR=#222222][FONT=Verdana]            'WorksheetFunction.Substitute(cl,"Old_string", "Replacement_string", Instance_num)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]            cl.Value = InStr(cl.Value, Line1)& Chr(10) _[/FONT][/COLOR]


[COLOR=#222222][FONT=Verdana]            & InStr(cl.Value, Line2) &Chr(10) _[/FONT][/COLOR]



[COLOR=#222222][FONT=Verdana]            & InStr(cl.Value, Line3) &Chr(10) _[/FONT][/COLOR]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]           End If[/FONT][/COLOR]



[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana] 'Statements to be executed inside the loop[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]



[COLOR=#222222][FONT=Verdana]        Next cl[/FONT][/COLOR]




[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]



[COLOR=#222222][FONT=Verdana]       ' ActiveCell.FormulaR1C1 = ""& Chr(10) & ""[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]



[COLOR=#222222][FONT=Verdana] 'totalloop = UBound(i)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana] 'Range(ActiveCell.Row, ActiveCell.Column)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]





[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]
 
Last edited by a moderator:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Hi & welcome to the board.
Is this what you want
Code: 
Sub AddLF()
Dim i As Long
Dim s As String
   For i = 1 To 1920 Step 80
   s = s & Mid(Range("A1"), i, 80) & Chr(10)
   Next i
   Range("A1").Value = s
End Sub
 
Upvote 0
Fluff said:
Hi & welcome to the board.
Is this what you want
Code: 
Sub AddLF()
Dim i As Long
Dim s As String
   For i = 1 To 1920 Step 80
   s = s & Mid(Range("A1"), i, 80) & Chr(10)
   Next i
   Range("A1").Value = s
End Sub
Click to expand...

This works great! I knew I was over thinking it. How do Imake the Range more variable? I plan to callAddLF directly after each screen scrape, so it depends on the active cellwe’re in vs. a specific cell.

Or if we use a defined name range which I would be able tomodify the range from outside of the code? And just run this once after thescrapes runs. But I guess it would have to be looped for multiple cell ranges. KIn either case this will greatly improve the tedious testing.
 
Upvote 0
This will loop through the selected range
Code: 
Sub AddLF()
   Dim i As Long
   Dim s As String
   Dim Cl As Range
   For Each Cl In Selection
      For i = 1 To 1920 Step 80
         s = s & Mid(Cl, i, 80) & Chr(10)
      Next i
      Cl.Value = s
   Next Cl
End Sub
 
Upvote 0
Fluff said:
This will loop through the selected range
Code: 
Sub AddLF()
   Dim i As Long
   Dim s As String
   Dim Cl As Range
   For Each Cl In Selection
      For i = 1 To 1920 Step 80
         s = s & Mid(Cl, i, 80) & Chr(10)
      Next i
      Cl.Value = s
   Next Cl
End Sub
Click to expand...


Thank you so much Fluff!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Excel macro to insert newline before specific text.
Replies
3
Views
702
rlv01
R
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
236
SBExcel123
S
M
  • Question Question
Need to add _AfterUpdate events to a dynamically-created class
Replies
1
Views
1K
Matrovsky
M
M
  • Question Question
Cell containing multiple lines of text - rebuilding the same structure with VBA - something is not working
Replies
3
Views
351
Fluff
Fluff
Mackeral
  • Question Question
Problem with code working in one version and same code not working in another version.
Replies
1
Views
1K
Mackeral
Mackeral

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top