Use If/Then to perform text to columns

Question

I am trying to figure out how perform a text to columns function in excel from data that is downloaded from a reporting software. The attached chart includes an individual security with a rolled-up market value and cost basis, with the lot-level cost basis comprising the rolled-up data. If there is lot level data, I need that data to be moved to an adjacent column, then I need to fill the name of the security that corresponds to the lot in the column it vacated. However, the name is only in the rollup row. Additionally, I need the roll-up row deleted if there is lot level data, but remain if there is not. The text to column wizard is insufficient because I can only choose one character (i.e., "L") to separate, and even then it fills the new column with just the "ot opened", removing the L. I was looking into the LEFT function, but I can't figure out the logic. For example, this is how the data looks when exported:

This is how the data needs to look (highlights only for reference). The rollup highlighted in yellow needs to be deleted if there is lot-level data. However, the name of the security in the rollup needs to be carried down the column once the "Lot opened on:" data is moved to a new column, allowing identification of the security by its lot instead of just the ticker.

Any assistance would be appreciated!

Thanks,

Answer 1

1. Identify lot rows - I'm assuming your data starts in A2 with the “Security Name” column. Insert a helper column (say Z) called Type with this formula:

=IF(LEFT(A2,12)="Lot opened o","Lot","Rollup")

This marks each row as either "Lot" or "Rollup".

2. Create a column for the Security Name (carried down)

In a new column (say AA, called SecurityNameNormalized), enter:

=IF(Z2="Rollup",A2,IF(Z2="Lot",LOOKUP(2,1/(Z$1:Z2="Rollup"),A$1:A2),""))

This looks up the most recent “Rollup” name above each lot, effectively carrying it down.

3. Move the “Lot opened on…” text to a new column

If you want to separate the “Lot opened on:” part into its own column (say AB, LotDetail):

=IF(Z2="Lot",A2,"")

You can refine it to strip the “Lot opened on:” prefix:

=IF(Z2="Lot",TRIM(MID(A2,FIND(":",A2)+1,LEN(A2))),"")

4. Delete roll-up rows that have associated lot rows:

• Filter the Type column for "Rollup".
• Keep only those Rollup rows where no “Lot” follows before the next Rollup.

You can flag such rows automatically in another helper (say AC, KeepRollup):

=IF(Z2="Rollup",IF(COUNTIFS(Z3:INDEX(Z$2:Z$1000,MATCH(TRUE,INDEX((Z$3:Z$1000="Rollup"),0),0)+ROW(Z3)-ROW(Z$3)+1),"Lot")>0,"Delete","Keep"),"")

That formula looks ahead to see if a “Lot” exists before the next “Rollup” — if so, mark it for delete. Then just filter by “Keep” and delete the others.

5. Final cleanup

Now you should have:

• SecurityNameNormalized showing the correct security name for each lot
• LotDetail showing the lot's open date
• No unnecessary roll-up rows

You can delete the helper columns after confirming results.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin