Excel needs to pull substring values to variables

Question

I'm an Oracle programmer with no VBA knowledge or experience. I've been asked to modify some Excel macros that were written for a report we get from our payroll provider to make it easier to view and then search for some different scenarios.

The payroll provider changed the layout of the report so I need to alter the macro accordingly.

The old report had accounting codes on each row for the employee. The new report groups the employees by the accounting codes with the codes listed on three rows with a varying number of rows below with the employees assigned to that accounting code.

The accounting codes are separated by a slash character so it looks like this:

code1/code2/code3/code4/code5
code6/code7/code8/code9/
code10/code11/code12

EmployeeID Name (Last Name, First Name) Pos#   EarnCode Earnings
N1234567   Messi, Leonardo              09876  LT1      $ 3,185.55
N2345678   Iniesta, Andreas             08765  REG      $ 1,980.10
N3456789   Hernandez, Xavier            07654  REG      $ 2,027.80

code1/code2/code3/code4/code5
code6/code7/code8/code9/
code10/code11/code12

EmployeeID Name (Last Name, First Name) Pos#   EarnCode Earnings
N4567890   Hazard, Eden                 06543  REG      $ 2,187.15
N5678901   Fabregas, Cesc               05432  LT1      $ 1,875.60
N6789012   Terry, John,                 04321  NBO      $ 1,290.50
N7890123   Thibaut, Courtois            03219  REG      $ 1,365.25

I need to move code4 and code7 to separate columns for every employee row below them, and then obviously changing the variable values when the accounting codes change for their appropriate employees. If it were SQL, I would use instr to find the position of the third slash and then take the next five characters like this:

substr(instr(ACCT_CODES, '/', 3), 5)

SQL's instr function can count the number of occurences of the item in question but in VBA it can only find the first location (from what I can find.) I've tried the VBA function Split but haven't made it work yet.

How can I pull out what I need and assign it to a variable to be used on the rows below?

Answer 1

You can use split to split a string to an array, Split(ACCT_CODES,"/")(6) will give you the 7th and (3) will give you the 4th