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
