Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    1

    Unanswered: Break-up varying data strings

    Hello! Thank you in advance if you read this, or know how to do this...

    I have a table that I have imported to Excel from a web database, but I need to break up a string of data, that could have zero to ten first and last names I want to get out of that cell... the only consistent way to know the breaks between names is a single "|" character spaced between names... for example:

    (no data)
    John Smith | Jane Smith (2 names)
    John Smith | Jane Smith | Joe Smith (3 names.... ad inifinitum)

    Is there an easy way to program that break? There are also corresponding Business titles with that... but once I get set, I could figure that out...

    I could do it manually, but I would like to see if there is a way to get it out first... THANK YOU!!!!

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Try this. I've attached a spreadsheet that shows two methods (Formulas in cells and VBA Code). Let me know if it doesn't work right and I'll try again.

    Neither of them are as elegant as I'd like them to be but it's what I could come up with quickly!

    Hope it helps!
    Attached Files Attached Files
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Another Way to do this would be to use the Text to Columns Tool

    Highlight the data you want to split
    Goto Data->text to columns

    on the wizard that opens up choose delimited
    click Next
    in the next page click the other checkbox and in the small textbox type |
    then click finish

    this will split your data into columns where the Columns are split by the | symbol.

    HTH

    David

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by DavidCoutts
    Another Way to do this would be to use the Text to Columns Tool

    Highlight the data you want to split
    Goto Data->text to columns
    WONDERFUL! You learn something new everyday.


    And here I am trying to do this the hard way!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I do this kind of thing everyday as the database we use churns out comma delimited files(which excel doesn't recognise as csv's). This wizard also comes up when you try to open a text file which excel doesn't recognise.

    You can do this by VBA as well,

    such as

    Workbooks.OpenText MyFile, DataType:=xlDelimited, Other:="|"

    'or

    Range("A1:A10").TextToColumns DataType:=xlDelimited, Other:="|"

    I looked at your code and thought Ouch! So i thought i give the poor chap a chance at understanding it.

    All the Best

    David

  6. #6
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by DavidCoutts
    You can do this by VBA as well,

    such as

    Workbooks.OpenText MyFile, DataType:=xlDelimited, Other:="|"

    'or

    Range("A1:A10").TextToColumns DataType:=xlDelimited, Other:="|"
    Nice! Thanks for the heads up!
    Quote Originally Posted by DavidCoutts
    I looked at your code and thought Ouch! So i thought i give the poor chap a chance at understanding it.
    Hehe... been awhile since I've done much in Excel.


    stankonia, sorry if what I posted was inconprehensible!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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