+ Reply to Thread
Results 1 to 2 of 2

Break the data into two columns

  1. #1
    Registered User
    Join Date
    11-10-2009
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Break the data into two columns

    Bayview Boulevard Sydney 90 William St Sydney
    Y Hotel Hyde Park 5-11 Wentworth Ave Sydney
    Wyndham Vacation Resorts Asia Pacific Sydney 66 Wentworth Avenue Sydney
    Y Hotel City South 179 Cleveland St Chippendale
    Hyde Park Inn 271 Elizabeth St Sydney

    I have this set of data with 8k records in which I need to separate out the address from the hotel name. The only help in this data is the address starts with a number. I can't use Text-to-Column coz neither it has any common delimiter nor it is of a fixed width. Can someone tell me how can I write a function to separate this out? Thanks

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Break the data into two columns

    Realistically, you can't as there is unlikely to be any bullet proof logic which can be applied to all records simultaneously to determine the split point.

    You *may* be able to capture *most* if you assume that the first digit in the string denotes commencement of address... eg if list is in A1 onwards then

    Please Login or Register  to view this content.
    You will still need to review all of the outputs as I'm sure a multitude of address will fail to conform to the above logic.

+ 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