Splitting column

Question

Hello I am working in this assignment and can't seem to figure out how to solve for problem 1. I have have been using the text to column and was hoping to get some help. This is the problem "

* Output a csv file that splits the Payroll_ID field into two fields, Payroll_IDNumber and Payroll_IDDay. These fields should show the numeric portion of Payroll_ID and the text portion of Payroll_ID, respectively. Only show each value of Payroll_ID once (i.e., do not repeat Payroll_ID). Sort your data in ascending order by Payroll_IDNumber. Your solution should have the following headers:"

I have attached the data set. thank you

Christina_H · Answer

It looks like there are always 8 digits followed by one or more letters?  If so, Left([Payroll_ID],8) will give you the number, and e.g. Substring([Payroll_ID],8) will give you the remaining text.

nkuipers · Answer

Hi @Jesstap,

I am not a wrangling expert and there is almost certainly more than one way to do it. I think you are on the right track with using the Text To Columns tool, but you may need to do a bit of pre-processing of the Payroll_ID field first, so as to introduce an appropriate delimiter to split on?