Extract data fields from wrapped text cell

    Hi,


    I'm trying to extract several bits of data from one cell, and paste it into other individual cells.


    The data is currently a long string, that contains 8 fields. Each field has a title and then a colon, and then the relevant data.


    It looks like this with 8 fields and 8 bits of data.


    Field 1:Data1Field 2:Data2


    I've tried using convert to columns which didn't seem to recognise the colon.


    Anyone advise the best way to separate so that the data from Field 1 goes in one cell, then data from Field 2 goes in another cell and so on.


    Thanks in advance ?

    What is between Data1 and Field2? Is there anything of consistency that can be used to separate the fields?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    No, nothing between those.


    The fields will always be the same, just the data will change.


    I have been trying to work out if I could do something with length of the gaps between the 2 fields but Ive not worked anything out yet.

    Can you give real examples to see if there is some one of distinguishing the field header from the data?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    I have been made aware that you have also cross posted this question on another forum. Please indicate by adding the link here. We can't help you otherwise as it is against the rules.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Sorry for the cross post, this is also here https://www.mrexcel.com/board/…o-multiple-cells.1132905/

    Heres some dummy data:


    Name:Stuart WildsAge:40account:23000000sortcode:221133Address:1 This roadThisTownThisCityA118BB
    Name:Paul FarquarAge:20account:23006625sortcode:111133Address:21 This roadThisTownThisCityA118CC
    Name:Clare McBulgeAge:42account:66272800sortcode:772873Address:1231A This DriveThisTownThisCityBC118BB
    Name:Chris FairhurstAge:33account:88277365sortcode:2337633Address:The GroveThisTownThisCityA118BB
    Name:Todd WindmillAge:50account:99982770sortcode:993833Address:12 That roadThatTownThatCityGR458BB

    Each of these is in one individual cell, so they would be in A1 to A5.

    As you can see, they always have the same headings before the colon, although the data after that could be any length string of data.

    I had been trying to get something working using length of particular strings but I've not managed to get it working yet.

    Assume criteria housed in B1:F1 as per below table


    In B2, formula copied across to F2 and all copied down ;


    =MID(LEFT($A2,IF(C$1="",250,FIND(C$1,$A2)-1)),FIND(B$1,$A2)+LEN(B$1)+1,250)


Participate now!

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

Register Yourself Login