Splitting a string into a vertical list based on delimiter?

**Flabbergaster** · 11-16-2012, 04:45 AM

Is it possible to alternate the text to column function in VBA to match the following needs:

Say I have a list of text strings. I would like to go through them one at a time, from top to bottom, and copy the content vertically to one column based on a delimiter "@". That is, the macro should take cell1 in column A (text string one consisting of, say, 7 paragraphs delimited by "@") and copy the parts down in column B and so on with the other strings. In this way creating a list of paragraphs in column B.

sdfas@xgdg@asdgwrt@fasg@	sdfas
jwelrjb@dfwg@sdfe@asfweg@	xgdg
dasf@asf@asfgttyh@sdgshery@fsdh@	asdgwrt
casdgery@sdhgrh@	fasg
	jwelrjb
	dfwg
	sdfe
	asfweg
	dasf
	asf
	asfgttyh
	sdgshery
	fsdh
	casdgery
	sdhgrh

**Kyle123** · 11-16-2012, 04:59 AM

If you don't mind a macro, select the data in the column and run:

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

**patel45** · 11-16-2012, 05:02 AM

Please Login or Register  to view this content.

**Flabbergaster** · 11-16-2012, 05:03 AM

It has a problem with this line

Please Login or Register  to view this content.

**Flabbergaster** · 11-16-2012, 05:07 AM

Worked like a charm....

**Trebor76** · 11-16-2012, 05:08 AM

Hi there,

Try this - just change the 3 commented lines 'Change to suit' if necessary:

Please Login or Register  to view this content.

Regards,

Robert

**Flabbergaster** · 11-19-2012, 05:46 AM

Originally Posted by patel45

Please Login or Register  to view this content.

Is it possible to alter this code in such a way, that it sorts column B and remove doubles on each step ?? If there are more words than the max nb of row in Excel, but of course unique ones...

**patel45** · 11-19-2012, 06:40 AM

Please Login or Register  to view this content.

**jindon** · 11-19-2012, 06:56 AM

Different method

Please Login or Register  to view this content.

**Flabbergaster** · 11-19-2012, 07:49 AM

Originally Posted by patel45

Please Login or Register  to view this content.

This one breaks at "stop"??

**Flabbergaster** · 11-19-2012, 07:50 AM

Originally Posted by jindon

Different method

Please Login or Register  to view this content.

This one breaks at txt = Join$(Evaluate("transpose(" & .Address & ")"))

**Flabbergaster** · 11-19-2012, 07:50 AM

There are 14000 rows in my database

**jindon** · 11-19-2012, 08:22 AM

Try this one then

Please Login or Register  to view this content.

**Flabbergaster** · 11-19-2012, 08:47 AM

Perfect!!!!!, thanx

Splitting a string into a vertical list based on delimiter?

LinkBack

Thread Tools

Rate This Thread

Display

Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Re: Splitting a string into a vertical list based on delimiter?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions