I am in search for the option which is opposite to concatenate. (that is if
we type in different columns it can be combined ) in one column. like that i
am looking for a command in which we type all the text strings will be broken
into different columns
pls help
thanks in advance

If you can use some character (e.g. space) as a separator, you can use Tools
- text to columns - Delimited... Select the column first.

Aa

"S.G.Pillai" wrote:

I am in search for the option which is opposite to concatenate. (that is if
we type in different columns it can be combined ) in one column. like that i
am looking for a command in which we type all the text strings will be broken
into different columns
pls help
thanks in advance

Hi

Take a look at DataText to Columns

Regards

Roger Govier


S.G.Pillai wrote:
I am in search for the option which is opposite to concatenate. (that is if
we type in different columns it can be combined ) in one column. like that i
am looking for a command in which we type all the text strings will be broken
into different columns
pls help
thanks in advance

go to

data

text to columns


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=480786

On Tue, 1 Nov 2005 00:58:03 -0800, "S.G.Pillai"
wrote:

I am in search for the option which is opposite to concatenate. (that is if
we type in different columns it can be combined ) in one column. like that i
am looking for a command in which we type all the text strings will be broken
into different columns
pls help
thanks in advance

You can use Data/Text to columns with <space as the delimiter. However, this
is not "dynamic". In other words, it won't update automatically when you
change the subject string.

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then you can use the following "regular expression" formulas:

With your subject phrase in A1, enter the following formula in B1 and copy/drag
across far enough to include enough cells for the longest sentence:

=REGEX.MID($A1,"\w+",COLUMN()-1)

This will also remove punctuation. If you wanted to include punctuation that
was adjacent to a word, such as the comma after word, then:

=REGEX.MID($A$1,"\w+\S?",COLUMN()-1)

If you also wanted to include punctuation that was off by itself, then:

=REGEX.MID($A$1,"\w*\S?",COLUMN()-1)

The formula will return empty strings once it gets past the end of the phrase.


--ron