News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Tweaking spreadsheet data

    Viewing 6 reply threads
    • Author
      Posts
      • #2334382
        WSbenjones76
        AskWoody Lounger

        Hello all; Hope all is safe and well.

        I need some help with Excel tweaking!!

        Column A has phone numbers.
        I need to remove any number that isn’t a UK landline, i.e. it doesn’t start with 01, 02 or 03.

        So in this example; I would not copy over Rows 2, 3, 5, 7 and 8.

        I would copy over 1, 4, 6, 8 and 10 into Column B.

        Please help! TIA – BJ.

         
        MODERATOR EDIT: please refer to Lounge Rules about descriptive topic titles

        Attachments:
      • #2334391
        krweaver
        AskWoody Lounger

        Maybe

        =IF(AND(1*MID(TEXT(A1,”00000 000000″),2,1)>=1,1*MID(TEXT(A1,”00000 000000″),2,1)<=3),TEXT(A1,”00000 000000″),””)

        filled down

      • #2334420
        Paul T
        AskWoody MVP

        This works for me (in LibreOffice): =IF(LEFT(A1,1)<"4",A1,"")

        Is the second number formatted correctly as it has 2 leading zeros?

        cheers, Paul

      • #2334572
        WSbenjones76
        AskWoody Lounger

        Thank you all; @krweaver; I get the error message (screenshot attached).

        Perhaps I am making an error?

        Many thanks.

        Attachments:
        • #2334752
          Paul T
          AskWoody MVP

          The forum software mangles quotes. Try this version.

          =IF(AND(1*MID(TEXT(A1,"00000 000000"),2,1)>=1,1*MID(TEXT(A1,"00000 000000"),2,1)<=3),TEXT(A1,"00000 000000"),"")

          cheers, Paul

      • #2334753
        Paul T
        AskWoody MVP

        This one also works. =IF(1*LEFT(A1,1)<4,A1,"")

      • #2334988
        WSbenjones76
        AskWoody Lounger

        Dear Paul – that is actually amazing – thank you.

        I forgot one other tweak to this tweaking that I am trying to do.

        To exclude any code that isn’t 0203, 0207 or 0208.

        Meaning to exclude 028… Which I added to this attached XLSX.

        TIA. BJ.

        Attachments:
      • #2335025
        Paul T
        AskWoody MVP

        This works, but it’s highly format dependent – your spreadsheet mixes formats.

        =IF(OR(AND(LEN(A1)=10,1*LEFT(A1,1)=1),1*LEFT(A1,2)=20,1*LEFT(A1,1)=3),A1,"")

        This one is based on krweaver’s post and doesn’t care about the format in column A – it assumes everything is 10 digits long.

        =IF(OR(1*MID(TEXT(A1,"00000 000000"),2,1)=1,1*MID(TEXT(A1,"00000 000000"),2,1)=3,1*MID(TEXT(A1,"00000 000000"),2,2)=20),TEXT(A1,"00000 000000"),"")

        cheers, Paul

        • This reply was modified 1 month, 2 weeks ago by Paul T.
        1 user thanked author for this post.
    Viewing 6 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Tweaking spreadsheet data

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.