setting up a hopefiully simple macro

[ekafrawy]

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

 

[tkstock]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Jef Gorbach]

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

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.

 

[JE McGimpsey]

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.

 

[Dave Peterson]

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]

thanks for the amplification, Dave!

mumblemumblemumble...<g>

 

[ekafrawy]

ok great thank for the help

 

[ekafrawy]

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

 

[tkstock]

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