Excel Question: Line Split / delimiter on an empty row

ESACAWIP

New Member
Joined
Nov 9, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
0
Example : I have a single cell (yes, single cell) that contains all this information.
+---------+
| ghijkl |
| aededd |
| |
| ededed |
| ededfe |
| |
| efefeef |
| efefeff |
+---------+

Is there anyway I can split this into 3 cells ?
+---------+ ---------+ --------- +
| ghijkl | ededed | efefeef |
| aededd | ededfe | efefeff |
| | | |
+---------+----------+----------+

I've tried using the delimiter tool and various vb code but all seem to split at each single line break but none which can split on an empty line break/row.
I apologize if I am using these terms incorrect.

Any help is appreciated, thank you!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

ESACAWIP

New Member
Joined
Nov 9, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
excel.JPG


I apologies, the text formatted very strangely. The first box is how the data is presented and the second box is how I need the data parsed.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
use XL2BB to post your example
read there how to use it
remember to post a representative example
 
Last edited:

ESACAWIP

New Member
Joined
Nov 9, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Thank you Sandy666,

I actually found a working code but unsure of how to turn this into a loop that goes down the column. Do you think you can help? Much appreciated!




Sub CellSplit()
Dim MyInput As String
Dim TempText As String, result As String
Dim sSplit

MyInput = Range("A1")
TempText = Replace(MyInput, Chr(10), "@")

sSplit = Split(TempText, "@@")

For i = LBound(sSplit) To UBound(sSplit)
Cells(1, i + 2) = Replace(sSplit(i), "@", Chr(10))
Next i

End Sub
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

sorry, I can't. I don't like vba

btw. I you want to post a code wrap the code in code tags

tags.png
 
Last edited:

navic

Active Member
Joined
Jun 14, 2015
Messages
302
Office Version
  1. 2013
Platform
  1. Windows
This is a modified VBA code that is publised by @p45call
Code:
'Option Explicit
Sub blah()
Set Destn = Range("D1")
For Each are In Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Areas
  are.Copy Destn
  Set Destn = Destn.Offset(, 1)
Next are
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
Try this with a copy of your data.
I have assumed data in column A

VBA Code:
Sub SplitEm()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Replace What:=vbLf & vbLf, Replacement:=";", LookAt:=xlPart
    .Replace What:=vbLf, Replacement:="#", LookAt:=xlPart
    .TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    .CurrentRegion.Replace What:="#", Replacement:=vbLf, LookAt:=xlPart
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,824
Messages
5,638,540
Members
417,033
Latest member
JKThai

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
Top