# How to transpose a column in Google Sheets with varying 'n' values, based on specific text being present in a cell

I have a Google Sheet list of contact details. Unfortunately, they are all in a single Column.

Moreover, each set has a varying number of Rows.

I'd like to Transpose the Column but don't know how!

He is a sample of the current data:

Note that I've highlighted the final cell in each set in Green. And that each of those cells contains the word "Followers".

I'd like to Transpose based on that keyword, Followers, as being the final cell for each set.

So that my output would be something like this:

I'm sure there's a simple way to do it, but I'm at a total loss how!

• Please share a spreadsheet with realistic sample data for the volunteer contributors here to work with. Commented Mar 21, 2022 at 18:50
• @ErikTyler, you're right, I'm very sorry. I'm new to here and haven't quite worked out what I'm doing properly. Next time I shall. Thanks for the suggestion. Commented Mar 22, 2022 at 9:12

You can try this formula with REGEX-TRANSPOSE-SPLIT:

``````=index(
split(
transpose(
split(
regexreplace(
textjoin("~",1,A:A),
"(Followers.*?)~",
"\$1%"),
"%")),"~"))
``````

...but depending on how big your dataset is, you may or may not get the following error:

So here's another possible solution:

``````=index(
ifna(
vlookup(
if(
sequence(1,20),
sequence(countif(A:A,"Followers*"),1,0)
)&sequence(1,20),
{
countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A))&
countifs(
countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A)),
countifs(A:A,"Followers*",row(A:A)+1,"<="&row(A:A)),
row(A:A),"<="&row(A:A)
),
A:A
},2,
)
)
)
``````

* If there are sets that contain more than 20 rows, increase the two occurrences of 20.

If you want a dragdown solution, use:

``````=transpose(
filter(
A:A,
isbetween(
row(A:A),
index(filter({0;row(A:A)},regexmatch({"Followers";A:A},"Followers")),row(A1))+1,
index(filter({0;row(A:A)},regexmatch({"Followers";A:A},"Followers")),row(A1)+1)
)
)
)
``````
• you are an absolute legend! Thank you! I couldn't get the first two to work (I had a circular error message) but I'm sure that's most likely down to my own inadequacies than your solution! But the third solution worked liked a charm! Thank you so much for sharing your time and skill in answering my question, it's much appreciated. LEGEND! :D Commented Mar 22, 2022 at 9:09
• I'm glad I could help!
– user290033
Commented Mar 25, 2022 at 23:36