Break-up varying data strings

stankonia · #1 (**permalink**) 05-07-04, 17:23

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!!!!

Cosmos75 · #2 (**permalink**) 06-03-04, 16:08

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!

DavidCoutts · #3 (**permalink**) 06-07-04, 10:45

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

Cosmos75 · #4 (**permalink**) 06-08-04, 11:06

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!

DavidCoutts · #5 (**permalink**) 06-08-04, 11:39

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

Cosmos75 · #6 (**permalink**) 06-08-04, 13:53

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!