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 👍

  • hugletank

    Changed the title of the thread from “Extra data fields from wrapped text cell” to “Extract data fields from wrapped text cell”.
  • 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.

  • 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!


    MS Excel MVP 2010-2016

  • 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.