I'm working on the basis that column B in your sheet (last_name) contains your merged data, (employee_ID last_name, first_name).
You can do this with three formula, using regexreplace
and regexextract
.
employee_ID
Put this in cell A1:
=arrayformula({"employee_ID";iferror(if($B2:$B="","",value(regexextract($B2:$B,"^.*\d"))),"")})
first_name
Put this in cell C1:
=arrayformula({"first_name";if($B2:$B="","",regexreplace($B2:$B,"^.*\,\ ",""))})
last_name
I created a new column D in front of department. This goes in cell D1:
=arrayformula({"last_name";if($B2:$B="","",regexreplace(regexreplace($B2:$B,".*\d\ ",""),"\,.*",""))})
Summary
Each one has an array using {}
. The first part of the array is the column heading in ""
, like "employee_ID"
. This is so you can keep your formula in row 1 incase you want to add a filter view on the dataset. Then a ;
is a return, then the rest of the cells below: iferror(if($B2:$B="","",value(regexextract($B2:$B,"^.*\d"))),"")
.
regexextract($B2:$B,"^.*\d")
looks for anything at the start ^.*
, then a number \d
.
value()
converts the result to a number. iferror
handles where a number can't be found.
On first_name, anything at the beginning ^.*
followed by a comma \,
then a space \
is replaced with ""
(ie. removed).
On last_name, .*\d\
replaces anything .*
followed by a number \d
followed by a space \
with nothing ""
(ie. removed), then from that result another replace removes a comma \,
followed by anything .*
.
Formula in one cell
If you want to do the split in one go, in say new columns F,G and H, put this in cell F1:
=arrayformula(iferror(trim(split({"employee_ID,last_name,first_name";if($B2:$B="","",value(regexextract($B2:$B,"^.*\d"))&", "®exreplace($B2:B,"^.*\d\ ",""))},",")),""))
However, employee_ID is then formatted as text.