Split a cell into multiple rows

ab1275

New Member
Joined
Jul 19, 2016
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Below is a mocked up file format that I have. The length is variable. Number of rows within each cell is also variable. Each row within the cell doesn't have a delimiter such as a comma. I had used Alt+Enter to create a row within each cell.
My end goal is to get a count in column B which based on the sample below should be 9.
Is there an easy way to get that information without macro?

Row 1A5678
Cheatsheet
D8796555
Row 2Snapshot5667
Pic345654332
Row 3Hello097685
F1234587
Row 4ABC
R8697089
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,025
Office Version
  1. 365
Platform
  1. Windows
How about this?

Book1
ABC
1A5678 D8796555 D8796559
2Snapshot5667 Pic345654332
3Hello097685 F12345678
4ABC R8697089
Sheet1
Cell Formulas
RangeFormula
C1C1=COUNTLINES(A1:A4)


VBA Code:
Function COUNTLINES(r As Range) As Integer
Dim AR() As Variant: AR = r.Value
Dim BA() As Byte

For Each a In AR
    BA = a
    For i = 0 To UBound(BA) Step 2
        If BA(i) = 10 Then COUNTLINES = COUNTLINES + 1
    Next i
    If COUNTLINES > 0 Then COUNTLINES = COUNTLINES + 1
Next a
End Function
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
First Select Column Were you have Data

Press Ctrl+H and in Find Button Press Ctrl+J
in Replace Button Insert a Space.

Now you have a " " Between two words and which can be counted as follows
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
=LEN(J9)-LEN(SUBSTITUTE(J9," ",""))+1 to find no of words in each cell
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No need to replace the line feed with a space, you can just use.
=len(a2)-len(substitute(a2,char(10),""))+1
but that will only give the number of rows for one cell, not the entire column.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,025
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I missed the bit about 'no macro'. Here's a way using PowerQuery.

Book1
AB
1Column1Table1
2A5678 D8796555 D8796559
3Snapshot5667 Pic345654332
4Hello097685 F12345678
5ABC R8697089
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Count = Table.RowCount(Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"))
in
    Count
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
@ab1275
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
Two formula options, depending on version
+Fluff New.xlsm
HIJK
1Data365 only2019/365
2A5678 D8796555 D87965599
3Snapshot5667 Pic345654332
4Hello097685 F12345678
5ABC R8697089
6
Master
Cell Formulas
RangeFormula
J2J2=LEN(TEXTJOIN(CHAR(10),,FILTER(H2:H11,H2:H11<>"")))-LEN(SUBSTITUTE(TEXTJOIN(CHAR(10),,FILTER(H2:H11,H2:H11<>"")),CHAR(10),""))+1
K2K2=LEN(TEXTJOIN(CHAR(10),,H2:INDEX(H:H,COUNTA(H:H))))-LEN(SUBSTITUTE(TEXTJOIN(CHAR(10),,H2:INDEX(H:H,COUNTA(H:H))),CHAR(10),""))+1
 

ab1275

New Member
Joined
Jul 19, 2016
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Thank you so much everyone! I tried all the suggestions here and they worked perfectly! You'll are awesome!
lrobbo314 - No worries. I googled and learnt how to copy a vba script into excel and use it.
Fluff - Good point, didn't think about that. I am using Microsoft Office 365 ProPlus for this exercise.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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