Mid Function (Please Advise)

As expected I need a little assistance. I'm new to excel. I've read the properties and capabilities of len, left, right, and mid. I understand most of it but not all of it.

I have 2 sheets, the first sheet contains data dumped by a processor.

On the first sheet, I need to break out the resource codes separated by commas in column D (note the inconsistency in length), into the Resource Code column on sheet 2.

On the first sheet, I need to break out the resource qtys in column D (tied to the resource code), which are represented by an integer with left right brackets, [], into the Resource Qty column on sheet 2.

On sheet 2 I've demonstrated how the data SHOULD be displayed, but there are very minimal formulas attached. I was able to get the first resource code and qty to display, but cannot extend my formula to evaluate the rest of the field. (small edit to clarify what formulas I've already created)

Please find my attached document.

Thanks in advance and I look forward to assisting others and learning from this community.

Arba,

Welcome to the forum!
Attached is a modified version of your posted workbook. In the 'HD Target Format' sheet, cell D3 and copied right is this formula:

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE('BP ETM Dump'!$D2,"[",REPT(" ",99)),",",REPT(" ",99)),(COLUMN()-4)*99+1,99)),"]","")

Hi Arba,

Assuming you just have the sheet1 data and a sheet2 with headers in row 1 (named as in your example workbook), the following macro should reorganize your data as requested:

Sub reorg()
Dim arr As Variant, i As Long, lastrow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("BP ETM Dump")
Set ws2 = Sheets("HD Target Format")
lastrow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For i = 1 To lastrow
    ws2.Cells(i + 1, 1).Resize(1, 3).Value = ws1.Cells(i, 1).Resize(1, 3).Value
    If InStr(1, ws1.Cells(i, 4).Value, "[") > 0 Then
        arr = Split(Replace(Replace(ws1.Cells(i, 4).Value, "]", ""), ",", "["), "[")
        ws2.Cells(i + 1, 4).Resize(1, UBound(arr) + 1).Value = arr
    End If
Next i
Application.ScreenUpdating = True
End Sub

The formula does, but not for human eyes. That part is right here:
COLUMN()-4

Column() will return the number of the column the formula is in. So in column E, column() returns the number 5. In column F, it returns 6, and so on.
The formula uses a mid function and the function starts at (column()-4)*99+1
So:
Column D starts at (4-4)*99+1 = 1
Column E starts at (5-4)*99+1 = 100
Column F starts at (6-4)*99+1 = 199

etc...

The reason the start number is going up by 99 is because the Substitute() functions are replacing the open brackets "[" and commas "," with 99 spaces. The formula starts at the next 99 up and grabs the character(s) to return for the appropriate column.

Hopefully you'll come back to this thread tiger, but one last question. I understand absolutely everything except for this, why is the Column() function's starting number at -4?

EDIT: and how does Trim come into play? I understand that it removes any leading or trailing spaces (so I assume it works with the rept function), but am not sure what exactly it's doing in this case.

The column number isn't starting at -4, the function is subtracting 4 from the column number returned by the function.

If you enter =COLUMN() into any cell in column B, the result will be 2. B is the second column in the spreadsheet. For any cell in column H it would return 8.

Since the formula is starting in column D (4), by subtracting 4 you end up with 0 for that part of the formula. 0*99+1 = 1. When dragged to the right one column, that part of the formula becomes 1*99+1 = 100. COLUMN()-4 in column E is equivalent to 5-4.

It could probably have also been written as

(COLUMN(A1)-1)*99+1

The A1 would update as you dragged it to the right, to B1, then C1, D1, etc. COLUMN(A1) = 1. COLUMN(B1) = 2, etc.

Hopefully that helps explain it.