| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need help setting a simple format to keep rent. | New Users to Excel | |||
| macro setting? | Excel Programming | |||
| Very simple ? on setting up tab order | Excel Programming | |||
| Sending email via macro- setting the importance setting. | Excel Programming | |||
| setting row in macro | Excel Programming | |||
Linear Mode
