Extract all the words from a string for processing, while leaving string intact.

**staggers47** · 10-10-2014, 08:10 AM

Hi,

Take a string like the following. Notice it has all sorts of punctuation.

'This is a test-string', he said; the answer was going to be somewhere between 'hard' and 'very hard'.

I need to be able to go through the string and take each word in turn to do some processing to it (replacing the word with another), but leave the punctuation, etc. as it is.

In other words I need to end up with 2 versions of it:

1. the original string
2. another 'output' string, which has all the punctuation in the same places, but with 'processed' words.

Everything I've tried has been hard to do, which either means I'm doing it wrong or it really is hard!

I've been doing things like going along the line, looking for non-alpha characters add them to a variable until I get to an alpha char, process the word, then add the non-alpha chars back to wherever they came from. This has been not very successful.

Is there a better way?

Help appreciated.

Staggers

**romperstomper** · 10-10-2014, 08:46 AM

You can do this with regular expressions. Can you give an example of the sort of processing you want to do?

**LJMetzger** · 10-10-2014, 08:55 AM

Hi Staggers,

You may want to try using VBA function split() to start your parsing. I don't know what else to suggest, since you haven't described what your output string is supposed to look like.

Sample code follows (tested and working in Excel 2003 - Assumes Option Base 0 for array indices):

Please Login or Register  to view this content.

Lewis

**staggers47** · 10-10-2014, 09:00 AM

Not really. The processing is 'simply!' to get the words out from all the other stuff. It will simply be a case of replacing one word with the (maybe) the same word spelled differently (think US v. UK spelling, for example).

So really I need to make a copy of the whole thing (which might be several sentences), leave the original as it is and then on the copy 'output' version do the word replacements.

My problem is that I haven't been able reliably to extract just the words from all the punctuation, process them, and put them back.

**romperstomper** · 10-10-2014, 09:04 AM

Here's an example. It will process all the selected cells and replace the words with "foo" in the cell to the right:

Please Login or Register  to view this content.

**staggers47** · 10-10-2014, 09:18 AM

@Romperstomper.

Well, this is obviously the way to go. However, I should explain that I don't need you code to do the actual replacing of words.

What I need is for it to find each word in turn - which yours obviously does very well, so I can then replace it (or not) and move on to the next word until the sentence(s) are complete.

**romperstomper** · 10-10-2014, 09:58 AM

How do you plan to replace it? If manually, you could just alter the code to pop up an inputbox.

**staggers47** · 10-10-2014, 10:08 AM

No, its done in code.

It will be looking up in what is currently a 40k word table.

So a user pastes in a paragraph or single sentence and hits a button.

The only way I've been able to make it work so far is to strip almost all punctuation except the periods at the end of sentences and then look up each word. So the result is that you have a cell with the original sentence, and another cell with the new version.

But that obviously isn't ideal, because I want to leave the original punctuation there in both cases.

So that's the thing. I need the code to find each word in turn, and then the replace with bit would be the result of the table look-up my code does. I don't know if this can be done with regex?

Edit: Will probably be away from keyboard for a while now, but will reply to any response asap.

**romperstomper** · 10-10-2014, 10:49 AM

In my code you'd want to look up match.submatches(2) and then put the return value in place of "foo" in the concatenated string. So something like:

Please Login or Register  to view this content.

**staggers47** · 10-10-2014, 12:08 PM

@ romperstomper

Well I'll be damned.

I really don't know my way around regex, so I modified your code a bit for testing purposes. I added a counter Cnt to simulate each lookup result and increased it for each match so I'd know I had the loop right, which it seems I do. Also, it isn't actually a vlookup, so I've used 'replace' instead.

Please Login or Register  to view this content.

Now here's what I get.

Input: 'This is a test-string', he said; the answer was going to be somewhere between 'hard' and 'very hard'.
Output: TEST0 TEST1 TEST2 TEST3-TEST4', TEST5 TEST6; TEST7 TEST8 TEST9 TEST10 TEST11 TEST12 TEST13 TEST14 'TEST15' TEST16 'TEST17 TEST18'.

Now I've done that there's a tiny problem that I didn't spot at first. The original single quote is missing. Have you any idea what I've done to cause that?

Regards

Staggers

**romperstomper** · 10-10-2014, 12:52 PM

Unfortunately a leading apostrophe in Excel is treated as an indicator of text formatting, not part of the cell value.

**staggers47** · 10-10-2014, 01:16 PM

Oh well, you've still made it vastly easier for me.

Just one final thing: you have rCell in a loop, but in my application it will only ever be a single named cell. How do I change the code accordingly?

Regards

Staggers

**staggers47** · 10-10-2014, 01:17 PM

Oh well, you've still made it vastly easier for me.

Just one final thing: you have rCell in a loop, but in my application it will only ever be a single named cell. How do I change the code accordingly?

Regards

Staggers

**romperstomper** · 10-10-2014, 02:02 PM

Something like this:

Please Login or Register  to view this content.

Extract all the words from a string for processing, while leaving string intact.

LinkBack

Thread Tools

Rate This Thread

Display

Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Re: Extract all the words from a string for processing, while leaving string intact.

Thread Information

Users Browsing this Thread

Similar Threads

Extract Last Two Words of a string

[SOLVED] Routine to remove brackets around words, leaving the format intact. Doesn't always work.

Extract n words from string

UDF to extract first three words from string

Extract last two words from string

Bookmarks

Bookmarks

Posting Permissions