Needing help with formula to auto move text from one cell to another

    Hi,


    I have a large spreadsheet which has street addresses, then a few cells beneath has the city. I am trying to move the city into the cell directly next to the street address. (column C has street address, i would like to move all cities to column D). I have tried a few different methods, none of which seem to be helping. I would think there is a formula or function i could use.... but am not having luck on the google trying to figure out how to craft a formula for this. My thinking is i should be able to drag a formula all the way to the bottom, then copy and paste the values entirely to a new sheet.


    Could someone help me please? Sample File attached

    Hi,


    If you're using a version of Excel with dynamic arrays, you could try something like this

    Code
    =INDEX(FILTER(C1:C1000,C1:C1000<>""),SEQUENCE(COUNTA(C1:C1000)/2,2))
Or,
=LET(_rng,C1:C1000, INDEX(FILTER(_rng,_rng<>""),SEQUENCE(COUNTA(_rng)/2,2)))


    Edited 2 times, last by mfroelaz ().

    Do you still need a space between City and next Street?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login