Is there a Google Sheet function that will parse out Country Name and Number into two different columns?

Question

In cell A1, I have data that looks like this:

	A
1	Finland 100 France 2000 Germany 1840 Great Britain 1333 Greece 64

I want the output to look like this:

	B	C
1	Finland	100
2	France	2000
3	Germany	1840
4	Great Britain	1333
5	Greece	64

There are other countries + values, but I wanted to illustrate that sometimes there are countries with multiple words, which is causing me to struggle with the problem.

=TRANSPOSE(SPLIT(JOIN(" ", FILTER(SPLIT(A1, " "), ISNUMBER(SPLIT(A1, " ")))), " "))

gets all of the numbers, but fails to get the country names on a single row.

What am I missing?

Answer 1

You may try:

=index(split(regexreplace(tocol(split(regexreplace(A1,"(\d)( )(\D)","$1|$3"),"|")),"( )(\d)","|$2"),"|"))