0
votes

I would like to split up data from a spreadsheet column (Work ID#, last name, first name) into two adjacent columns.. so that I have 3 separate columns for this data.

Here is a link to my spreadsheet: https://docs.google.com/spreadsheets/d/1gsLYnrNHEMbZTML1YZG-NhtDYO_FAQaAPwGFvtqn2kg/edit?usp=sharing

2
can you show an example of what your intending the result to be? also there isn't a Work ID column on your spreadsheet. - Umar.H

2 Answers

2
votes

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"))&", "&regexreplace($B2:B,"^.*\d\ ",""))},",")),""))

However, employee_ID is then formatted as text.

1
votes

I believe the easiest for you is to use the following logic and single formula

=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(A2:A,"(\d+) (.+), (.+)","$1@$2@$3"),"@")))

enter image description here

Please write the headings manually. Including them in the formula un-necessarily complicates things. Just make sure that everything below B2, C2 and D2 is cleared.
Please let us know about info on how the formula works.

Functions used: