Parsing when deliminator is a string
 

I have a column of data that needs to be parsed into 2 columns, but the place
where the parse should begin is a word, in this case "EXCLUDES". How do I
word a parse command in this case?
BTW, if it's also possible, it'd be great to not only parse the 2nd part
out, but also to delete the actual word "EXCLUDES." If this makes it too
complicated, I can always do a mass find and replace. :)

Why not do a mass find and replace FIRST (with, say, a space, comma,
etc), then do a Data/Text to Columns, splitting on the new delimiter.

In article ,
"Rose" wrote:

I have a column of data that needs to be parsed into 2 columns, but the place
where the parse should begin is a word, in this case "EXCLUDES". How do I
word a parse command in this case?
BTW, if it's also possible, it'd be great to not only parse the 2nd part
out, but also to delete the actual word "EXCLUDES." If this makes it too
complicated, I can always do a mass find and replace. :)

Because my text can and does include spaces, commas, semicolons, tabs, and
every character on my keyboard - hence a mass Find and Replace will not work.
I need to find a string of text within my cell and parse it there.

"JE McGimpsey" wrote:

Why not do a mass find and replace FIRST (with, say, a space, comma,
etc), then do a Data/Text to Columns, splitting on the new delimiter.

In article ,
"Rose" wrote:

I have a column of data that needs to be parsed into 2 columns, but the place
where the parse should begin is a word, in this case "EXCLUDES". How do I
word a parse command in this case?
BTW, if it's also possible, it'd be great to not only parse the 2nd part
out, but also to delete the actual word "EXCLUDES." If this makes it too
complicated, I can always do a mass find and replace. :)

OK. You could do two Replaces followed by a Text to Columns. For
instance,

1) replace all "%" with "^$^REPLACE_ME^$^" (or some other unique phrase)
2) replace all "EXCLUDES" with "%"
3) Data/Text to Columns, splitting at "%"
4) replace "^$^REPLACE_ME^$^" with "%"





In article ,
"Rose" wrote:

Because my text can and does include spaces, commas, semicolons, tabs, and
every character on my keyboard - hence a mass Find and Replace will not work.
I need to find a string of text within my cell and parse it there.

Yes, that would work. However, I don't wish to do this very manual operation
335 times as I receive data, and time is of the essence (making waiting for
all 335 to come in and *then* go through the find and replace dance
unfeasible). I also have 2 columns I need to do this to per item.

This can be automated with a bit of code in a macro. I just need to know how
to word that code. It's been a while since I've needed it, so I've forgotten
how to put it. I can *make* Text to Columns do what I need it to. I can
*make* Excel do what I'm doing in Access, too, but it involves a lot more
manual, unnecessary work.

"JE McGimpsey" wrote:

OK. You could do two Replaces followed by a Text to Columns. For
instance,

1) replace all "%" with "^$^REPLACE_ME^$^" (or some other unique phrase)
2) replace all "EXCLUDES" with "%"
3) Data/Text to Columns, splitting at "%"
4) replace "^$^REPLACE_ME^$^" with "%"





In article ,
"Rose" wrote:

Because my text can and does include spaces, commas, semicolons, tabs, and
every character on my keyboard - hence a mass Find and Replace will not work.
I need to find a string of text within my cell and parse it there.

Try recording it.

In article ,
"Rose" wrote:

Yes, that would work. However, I don't wish to do this very manual operation
335 times as I receive data, and time is of the essence (making waiting for
all 335 to come in and *then* go through the find and replace dance
unfeasible). I also have 2 columns I need to do this to per item.

This can be automated with a bit of code in a macro. I just need to know how
to word that code. It's been a while since I've needed it, so I've forgotten
how to put it. I can *make* Text to Columns do what I need it to. I can
*make* Excel do what I'm doing in Access, too, but it involves a lot more
manual, unnecessary work.