+ Reply to Thread
Results 1 to 9 of 9

Separating Name and Address Phone Number with Odd Delimiter

Hybrid View

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2012
    Posts
    5

    Separating Name and Address Phone Number with Odd Delimiter

    I have the following text, in which I need separated into three columns.

    Column A) Company Name
    Column B) Address
    Column C) Phone Number

    I was thinking perhaps I could replace "-------" to just one "-" and use that delimiter to separate the phone number.
    Then Use *** Company Name Here *** --> *Number Variable* and filter the rest..

    But I don't see it being able to work.
    Here is the text I'm looking to format (Mind you there is about 8,000 Records.

    And, it's all formatted like below. (Company Name, phone number being on different lines and phone number being separated by dash's

    Looking for a all-in-one formula if at all possible.

    American Communications Services Of Louisville
    462 S 4th Av ----------------------------------------------------- 589-0893
    American Communications Services Of Louisville
    462 S 4th Av ----------------------------------------------------- 589-1044
    Arrow Electric 317 Wabasso Av Louisville ----------------------- 367-0141
    D & J Cable Contractors Inc 3099 Element Ln Lsvl ------------ 614-4137
    Ecomtek Telecom 4169 Westport Rd Lsvl ------------------------326-5739
    Fishel Technologies 4508 Bishop Ln ---------------------------- 456-9444
    Granite telecommunications 4675 Outer Loop Louisville ------- 969-3523
    Impact Office Products Inc 1305 Durrett Ln Lsvl --------------- 366-6988
    Independent Telephone 4525 Poplar Level Rd Lsvl -------------409-7191
    Nationwide Telecom 2400 Ash Ave Lsvl -------------------------554-9085
    OR Communications Louisville ---------------------------------- 966-9292
    Telecompute Louisville -------------------------------------------540-5577

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Hi SCalkin and welcome to the forum,

    See if the Left(), Right and formulas in the attached help.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2012
    Posts
    5

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Thank you! Well that formula defiantly helps. I'm working on using that formula, so that I would be able to copy down the formulas for all 5,000 records. As it stands, because Company Name is sometimes on a different line, it's proving to be more difficult.

    Edit:

    This looks much better but still very sloppy...

    any help idea's clean this up?
    Attached Files Attached Files
    Last edited by SCalkins; 03-18-2013 at 06:42 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Hi Scalkins,

    See another formula for all the stuff on the same row on the attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2012
    Posts
    5

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Screen Shot 2013-03-18 at 7.10.22 PM.png

    Everything works great except for this.

    Is there a way to remove all "-" Characters in that formula?

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Separating Name and Address Phone Number with Odd Delimiter

    What should the answer be for your picture? Should Column B be blank?

    You have 3 different "standards" in your data.
    1. Two rows for a Company, Address, Phone.
    2. One row with all on it
    3. Missing Address on one row.

    Do you have an answer yet or should I keep working on it?

  7. #7
    Registered User
    Join Date
    03-18-2013
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2012
    Posts
    5

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Sorry about that, sometimes I just assume everyone knows what I'm talking about without explaining. Haha..

    Using this formula in column B) =LEFT(VARIABLE,MIN(FIND({0,1,2,3,4,5,6,7,8,9},VARIABLE&"0123456789"))-1)

    It's producing the correct results. (Company Name)
    However, there is a trail of "-----" at the end of the company name (see new photo)

    \1

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,169

    Re: Separating Name and Address Phone Number with Odd Delimiter

    Ok - I have an answer for 2 and 3 above combined. See the attached...
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-18-2013
    Location
    Illinois, USA
    MS-Off Ver
    Excel 2012
    Posts
    5

    Thumbs up Re: Separating Name and Address Phone Number with Odd Delimiter

    Simply amazing, wonderful job buddy!! I Appreciate every bit of time and effort you put into helping me!!

    I can't thank you enough!

    Works awesome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1