setting up a hopefiully simple macro
 

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

Thanks


--
ekafrawy
------------------------------------------------------------------------
ekafrawy's Profile: http://www.excelforum.com/member.php...o&userid=24387
View this thread: http://www.excelforum.com/showthread...hreadid=379764

setting up a hopefiully simple macro
 

Use the -Data Text to Columns- functionality. Specify "delimited
and "other" as the delimiter. Put an "*" in the box, then check th
"Treat consecutive delimiters as one"

HT

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37976

setting up a hopefiully simple macro
 

You don't need VBA, use DataText To Columns with a separator of *.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ekafrawy" wrote in
message ...

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

Thanks


--
ekafrawy
------------------------------------------------------------------------
ekafrawy's Profile:
http://www.excelforum.com/member.php...o&userid=24387
View this thread: http://www.excelforum.com/showthread...hreadid=379764

setting up a hopefiully simple macro
 

"ekafrawy" wrote in
message ...

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

Thanks


--
ekafrawy
------------------------------------------------------------------------
ekafrawy's Profile:
http://www.excelforum.com/member.php...o&userid=24387
View this thread: http://www.excelforum.com/showthread...hreadid=379764


If the only column is these cells, then it might be easier to copy/paste
into Word, do a Find-and-Replace(* for ^p then ^p^p for ^p to remove extra
blank lines from ** unless wanted) then copy/paste results back to Excel,
especially if this is a one-time use.

setting up a hopefiully simple macro
 

If this is a one-off, a way to do this manually would be to do a
Find/Replace to replace a double asterisk with a single asterisk. Then
choose Data/Text to Columns, select Delimited, and enter the asterisk in
the Other box on the second pane.

If you need to do this repeatedly, you could record a macro of the above.




In article ,
ekafrawy
wrote:

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

setting up a hopefiully simple macro
 

I think I like tkstock's response better <vbg, but if the OP is gonna do the
edit|Replace, then it's best to change:

~*~*
to
*

The asterisk is a wild card and xl needs to be told to really look for the
asterisk--not any set of characters.



JE McGimpsey wrote:

If this is a one-off, a way to do this manually would be to do a
Find/Replace to replace a double asterisk with a single asterisk. Then
choose Data/Text to Columns, select Delimited, and enter the asterisk in
the Other box on the second pane.

If you need to do this repeatedly, you could record a macro of the above.

In article ,
ekafrawy
wrote:

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

--

Dave Peterson

setting up a hopefiully simple macro
 

thanks for the amplification, Dave!

mumblemumblemumble...<g

In article ,
Dave Peterson wrote:

I think I like tkstock's response better <vbg, but if the OP is gonna do the
edit|Replace, then it's best to change:

~*~*
to
*

The asterisk is a wild card and xl needs to be told to really look for the
asterisk--not any set of characters.



JE McGimpsey wrote:

If this is a one-off, a way to do this manually would be to do a
Find/Replace to replace a double asterisk with a single asterisk. Then
choose Data/Text to Columns, select Delimited, and enter the asterisk in
the Other box on the second pane.

If you need to do this repeatedly, you could record a macro of the above.

In article ,
ekafrawy
wrote:

Hi I was wondering if using a macro would be a good way to do this.
I have a bunch of cells that have 5 or 6 sentences in each cell. Each
sentence is separated by an Astrisk(*) or double Astrisk(**). Is there a
way to have excel take each sentence and put it into its own cell
automatically? instead of copying each sentence and pasting it into its
own cell one by one?

setting up a hopefiully simple macro
 

ok great thank for the help


--
ekafrawy
------------------------------------------------------------------------
ekafrawy's Profile: http://www.excelforum.com/member.php...o&userid=24387
View this thread: http://www.excelforum.com/showthread...hreadid=379764

setting up a hopefiully simple macro
 

it work great except i need each separeted sentence into a new row not
new column. I don't see an option to do thi

--
ekafraw
-----------------------------------------------------------------------
ekafrawy's Profile: http://www.excelforum.com/member.php...fo&userid=2438
View this thread: http://www.excelforum.com/showthread.php?threadid=37976

setting up a hopefiully simple macro
 

You will need a macro for that.

You could have some code load the strings into a text array:

Code:
--------------------

Dim myArray() as String
Dim ind as Integer
ind = 1
While Activecell < ""
Redim Preserve myArray(ind)
t = Activecell.Value
for x = 1 to Len(t)
if Mid$(t,x,1) = "*" And myArray(ind) < "" Then
ind = ind + 1 ' only increments on first asterisk
elseif Mid$(t,x,1) < "*" Then
myArray(ind) = myArray(ind) & Mid$(t,x,1)
end if
next x
Selection.Offset(1,0).Select
Wend
--------------------

Then dump those array values where you want them...

HTH


--
tkstock


------------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...o&userid=14443
View this thread: http://www.excelforum.com/showthread...hreadid=379764