Is there a formula (ideally) that can take some semi-structured data in one column and split it into multiple columns?

[statbid]

I have a data set with around 10,000 rows in one column that contains approximately 1,500 individual profiles.

https://monosnap.com/file/kInwZwsYoJuvhBsY9pQESDxGGZgZ3r

I need to transform this data into the following columns

Twitter Handle
Following
Followers
Avg. Likes
Avg. Retweets
SparkScore

The data has some repetitive patterns that I would think could be used for parsing the data (example below). The end of each profile contains the following string:

"% of profiles matching the search query follow this account."

The numbers above Following, Followers, Avg. Likes, Avg. Retweets, and SparkScore are the data that pertains to those fields that we need to turn into columns.

Two rows below the "% of profiles matching this search query follow this account" is the Twitter Handle for the next profile.

I've tried recording a Macro to do this work, but sometimes there is an extra row if there is a location or not listed in the profile and I am having a hard time figure out how to get around that.

I'd love any help that folks could figure out. It would be great if there was a formula that could do this work since I understand formulas better than macros.

As always, thank you for your guidance.

Shilo

Twitter Verified Account
Eric Fisher@EricFisherSBJ
NY/NJ
MLB/Digital Media/Technology writer for SportsBusiness Journal (@SBJSBD)
786
Folllowing
18,354
Folllowers
5.95
Avg. Likes
3.3
Avg. Retweets
49
SparkScore
7% of profiles matching the search query follow this account.
Ralph Mancini@ReverendRalph
New York City
News editor and devoted Packers fan. You can find my work at https://t.co/e3oEG6dVpN & https://t.co/qO34OTICZ7
2,289
Folllowing
2,343
Folllowers
0.81
Avg. Likes
0.23
Avg. Retweets
12
SparkScore
7% of profiles matching the search query follow this account.
Twitter Verified Account
Tiffany Blackmon@tiffblackmon
Former soccer player, collector of street dogs, NFL Network Reporter. https://t.co/EHwPy41vty
1,549
Folllowing
13,994
Folllowers
37.57
Avg. Likes
7.89
Avg. Retweets
46
SparkScore
7% of profiles matching the search query follow this account.
These Guys Podcast@TheseGuysSP
Seattle, WA
Talking sports on @applePodcasts, @stitcher & Podbean! Hide your tweets, we will find them. #TheseGuys : @therealTyRota, @raiderDebater & @Kashpy.
33,917
Folllowing
38,186
Folllowers
12
Avg. Likes
2.5
Avg. Retweets
44
SparkScore
7% of profiles matching the search query follow this account.

 

[statbid]

Thanks to everyone that viewed this thread. I know this was a tough request. I gave up on this approach and was able to get a Python script to do the parsing and transformation work.