Is there a way to transform string of names and surnames to seperate rows?

Question

I have a string of names and surnames:

Name1 Surname1 Name2 Surname2 Name3 Surname3

and I want to transform it into separate columns like this:

Name and surname
Name1 Surname1
Name2 Surname2
Name3 Surname3
Name4 Surname4

I have tried Paste formatting -> Split text with a space separator and then Paste special -> Transposed, but it separates the first and last name into different columns (columns 6-11):

Name and surname
Name1
Surname1
Name2
Surname2
Name3
Surname3

I have also found this post which splits by a task number, but I can't figure out the regex ((\d+\.) pattern does not apply to my question. My actual names and surnames don't have any index)

=TRANSPOSE(SPLIT(REGEXREPLACE(A1, "(\d+\.)", "♥$1"), "♥"))

score 2 · Accepted Answer · answered Dec 06 '22 at 15:25

2

try this:

=TRANSPOSE(SPLIT(REGEXREPLACE(A2,"(\S+) (\S+) ","$1 $2"),""))

answered Dec 06 '22 at 15:25

rockinfreakshow

15,077
3
12
19

Is there a way to transform string of names and surnames to seperate rows?

1 Answers1