Split a cell into multiple rows

[ab1275]

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 1	A5678 Cheatsheet D8796555
Row 2	Snapshot5667 Pic345654332
Row 3	Hello097685 F1234587
Row 4	ABC R8697089

 

[lrobbo314]

How about this?

Book1
	A	B	C
1	A5678 D8796555 D879655		9
2	Snapshot5667 Pic345654332
3	Hello097685 F12345678
4	ABC R8697089
Sheet1

Cell Formulas
Range	Formula
C1	C1	=COUNTLINES(A1:A4)

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]

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]

=LEN(J9)-LEN(SUBSTITUTE(J9," ",""))+1 to find no of words in each cell

 

[Fluff]

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.

 

[CA_Punit]

Char(10) Thanks @Fluff

 

[lrobbo314]

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

Book1
	A	B
1	Column1	Table1
2	A5678 D8796555 D879655	9
3	Snapshot5667 Pic345654332
4	Hello097685 F12345678
5	ABC R8697089
Sheet1

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]

@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]

Two formula options, depending on version

+Fluff New.xlsm
	H	I	J	K
1	Data		365 only	2019/365
2	A5678 D8796555 D879655		9	9
3	Snapshot5667 Pic345654332
4	Hello097685 F12345678
5	ABC R8697089
6
Master

Cell Formulas
Range	Formula
J2	J2	=LEN(TEXTJOIN(CHAR(10),,FILTER(H2:H11,H2:H11<>"")))-LEN(SUBSTITUTE(TEXTJOIN(CHAR(10),,FILTER(H2:H11,H2:H11<>"")),CHAR(10),""))+1
K2	K2	=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]

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.