Auto increment a cell values upon specific character

Question

Looking for a formula to auto increment the first and/or last value (s) iterations into another column cell.&nbsp;For instance, as the text string has more dates then display the value before the first comma and then after each comma. Emphasis on "5/24/2022, 5/25/2022, 5/26/2022" see attachment.&nbsp;Date column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B column5/24/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/24/20225/24/2022, 5/25/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/25/20225/24/2022, 5/25/2022, 5/26/2022&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/26/2022&nbsp;&nbsp;

subodh_tiwari_sktneer · Answer

Scott1417&nbsp;&nbsp;Please try this...&nbsp;Assuming your date strings start from A2 then...In B2=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),1+(ROWS(A$2:A2)-1)*LEN(A2),LEN(A2)))and then copy it down.

scott1417 · Answer

Hi Subodh,&nbsp;Thank you for the response. The description and excel file I originally provided "missed the mark". I did try your formula. I bet you can get it to work after you review the formula below and see the "new" excel file attached. Some how the formula below achieves what I was attempting to describe. TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),100))&nbsp;Can you review your formula and compare it to the formula, which one is the better of the two?Scott

hansvogelaar · Answer

Scott1417&nbsp;
If you always want to return the (trimmed) text after the last comma, your new formula is the one to use.
If you want something else, please explain in more detail.

scott1417 · Answer

HansVogelaar&nbsp;&nbsp;Thank you Hans. I appreciate your knowledge on the formula.&nbsp;Scott

Forum Discussion

Auto increment a cell values upon specific character

4 Replies

Resources