Copy occurrences from one cell in a column to another

    I have a sheet with about 1500 rows and in each row there will be two or three occurences of text. This text will always start the same but with four numbers at the end which will be different. Example: SSS-DEWBU-1234 and SSS-DEWBU-3456. There will be a lot of other text in each cell, and a typical single cell example is:


    SSS-DEWBU-1234
    This is an example of the text in
    each cell. Where the text varies.


    SSS-DEWBU-3456
    Further text to display the this
    example of cell contents


    I need to lookup each cell in the column and extract the SSS-DEWBU-1234 and the SSS-DEWBU-3456 from each cell in the column and copy it to the adjacent cell. So where there are two of these in one cell, the first will go to column B and the second to column C. I've attached a workbook with three in one cell to see what it should finish up with.
    Many thanks
    ArtySin

    Re: Copy occurrences from one cell in a column to another


    ug. For some reason in your example, the text in column A has SSS-DEWBU but the "result" in columns B-D has SSS-T5WBU. I'm not sure if this is a typo or has some significance I'm not understanding.


    The way I have read your question, the following formula in B1 can be copied across and down to generate the results I THINK you are looking for:
    =MID($A1,SEARCH("!",SUBSTITUTE($A1,"SSS-DEWBU","!",COLUMN()-1)),14)


    Some caveats:
    This assumes that the start of the text will always be SSS-DEWBU
    This assumes the desired result will always be 14 characters
    This assumes the character "!" will never legitimately be in the source text anywhere ("!" can be replaced by any character, or indeed string of characters that you can guarantee will never be present)
    If the formula is copied right into a fourth column it will generate an error (as there are only 3 instances), this can easily be solved by wrapping the formula in an iferror formula if you have an unknown number of instances.

Participate now!

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

Register Yourself Login