Results 1 to 2 of 2

Thread: How to split one row with different seperators?

  1. #1
    Neophyte Axel's Avatar
    Join Date
    Nov 2020
    Location
    Germany
    Posts
    1
    Articles
    0
    Excel Version
    365

    Question How to split one row with different seperators?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I've a monitoring-table from some testclients and one row is filled with internetdomains. I need for a reporting only a part of the domain.

    In the field domain you can find typical internet domains, I would like to evaluate them further according to the domains.

    My problem is that there are several entries in the form "..... 0.a.0.2.0.8.3.2.0.1.0.a.2.ip6.arpa" or "..... 73a15bb4c8.fp.measure.office.com".

    For me it would be relevant to have the last three domains separated by a period in an additional column. I.e. with the two "2.ip6.arpa" and "measure.office.com".

    Additionally, some domain values contain only two dots, e.g. "0x0d.de".

    For this I have too little experience with the PowerQueryEditor how to create a new one with the reduced domain info from the one column.

    Currently there are about 140.000 entries in the table, new ones are added constantly.

    In the attached file in Table1 is an example of my monitoring-table. In Table new the new form.

    I'm using Excel 365.

    Demo.xlsx

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    See the one step solution in appended file.

    Since you are a fellow countryman, I can also recommend the German site ms-office-forum.net for PQ questions.
    Last edited by pinarello; 2020-11-22 at 02:25 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •