Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: LEFT() for each rows in a single cell?

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    274
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question LEFT() for each rows in a single cell?

    The following data is in a SINGLE cell. I'm trying to get the phone number from each line and output it as just a list of phone numbers in a single cell.

    FROM THIS:
    Code:
    (734) 435-8519 < Sent TXT 14/25/09 @ 08:54 (734) 917-1416 < Sent TXT 14/25/09 @ 08:54 (734) 742-6437 < Sent TXT 14/25/09 @ 08:54 (734) 435-8511 < Sent TXT 14/25/09 @ 08:54-Bob's number (734) 360-5106 < Sent TXT 14/25/09 @ 08:54 (734) 880-3306 < Sent TXT 14/25/09 @ 08:54 (734) 384-0869 < Sent TXT 14/25/09 @ 08:54-Not working (734) 494-5151 < Sent TXT 14/25/09 @ 08:54 (435) 851-8519 < Sent TXT 14/25/09 @ 08:54-Yelled at me (734) 408-2349 < Sent TXT 14/25/09 @ 08:54 (734) 224-7269 < Sent TXT 14/25/09 @ 08:54 (734) 299-4730 < Sent TXT 14/25/09 @ 09:02 (734) 299-4703 < Sent TXT 14/25/09 @ 09:02-Not working# (734) 291-9930 < Sent TXT 14/25/09 @ 09:02 (734) 345-8002 < Sent TXT 14/25/09 @ 09:02
    TO THIS:
    Code:
    (734) 435-8519 (734) 917-1416 (734) 742-6437 (734) 435-8511 (734) 360-5106 (734) 880-3306 (734) 384-0869 (734) 494-5151 (435) 851-8519 (734) 408-2349 (734) 224-7269 (734) 299-4730 (734) 299-4703 (734) 291-9930 (734) 345-8002
    I'm not sure how to use LEFT() for this type of situation. The text located to the right of the numbers could be ANYTHING. But the phone number format is always the same.

    Any ideas?
    Last edited by jeffcoleky; Oct 22nd, 2019 at 02:47 PM.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,938
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    15 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    Try whit this UDF

     AB
    2(734) 435-8519 < Sent TXT 14/25/09 @ 08:54
    (734) 917-1416 < Sent TXT 14/25/09 @ 08:54
    (734) 742-6437 < Sent TXT 14/25/09 @ 08:54
    (734) 435-8511 < Sent TXT 14/25/09 @ 08:54-Bob's number
    (734) 360-5106 < Sent TXT 14/25/09 @ 08:54
    (734) 435-8519
    (734) 917-1416
    (734) 742-6437
    (734) 435-8511
    (734) 360-5106

    CellUDF Formula
    B2=getphonenumber(A2)


    Code:
    Function getphonenumber(cad As String)
      Dim c As Variant, newcad As String
      For Each c In Split(cad, Chr(10))
        newcad = newcad & Left(c, 14) & Chr(10)
      Next
      getphonenumber = Left(newcad, Len(newcad) - 1)
    End Function
    -------------------
    HOW TO INSTALL UDFs

    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TextOnly just like it was a built-in Excel function.
    Regards Dante Amor

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    Maybe
    Code:
    Function jeffcoleky(Cl As Range) As String
        Dim Sp As Variant
        Dim i As Long
        
        Sp = Split(Cl, Chr(10))
        For i = 0 To UBound(Sp) - 1
            jeffcoleky = jeffcoleky & Split(Sp(i), "<")(0) & vbLf
        Next i
        jeffcoleky = jeffcoleky & Split(Sp(i), "<")(0)
    End Function
    Used in the worksheet like
    =jeffcoleky(a2)

    EDIT:
    I'm obviously way too slow today
    Last edited by Fluff; Oct 22nd, 2019 at 03:16 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    May 2011
    Posts
    274
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    OK those all do work in excel so thank you! However, just like Fluff, I'm a little slow.

    Is there a way to use a FORMULA for this? Or could these be adapted to work in google sheets? If not, I'll make what you've posted work since this IS an Excel forum I posted in! lol.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    I certainly don't know of any simple formula that would do it, and I've never used GoggleSheets so can't convert the VBA to the version of Java that it uses.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,938
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    15 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    I have not used google sheet either.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    May 2011
    Posts
    274
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    So no easy formula method in excel either? If not, thanks everyone for making it work this way! it saves me hours.

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    Hi
    What about
    Code:
    B1=LEFT(A1,LEN(A1)-LEN(RIGHT(A1,LEN(MID(A1,FIND("<",A1),255))))-1)
    Last edited by mohadin; Oct 22nd, 2019 at 05:04 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    @mohadin
    All those values are in one cell, not multiple rows.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    328
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LEFT() for each rows in a single cell?

    Quote Originally Posted by Fluff View Post
    @mohadin
    All those values are in one cell, not multiple rows.
    OOps
    Missunderstanding
    Sorry

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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