Split text into array using multiple delimiters

**TKFRMjarvis** · 02-25-2014, 03:18 PM

I'm writing a macro to spell check each word in a cell and simply change the color of misspelled words. The defualt delimiter is Chr(32), which is a space, but I'd like to also use Chr(10), line break, as our people often use soft breaks. Without the Chr(10) incorporated, if a misspelled word is at the beginning or end of a line, the correctly spelled word adjacent to it, if only separated by a line break, will also be highlighted. Here is my code:

Please Login or Register  to view this content.

I highlighted the split command in red, which is where I need to incorporate the space and line breaks as delimiters. Any help would be great!

**MarvinP** · 02-25-2014, 03:40 PM

Hey Jarvis,

Why reinvent spellcheck?
http://www.addictivetips.com/microso...in-excel-2010/

**TKFRMjarvis** · 02-25-2014, 03:56 PM

Good question MarvinP. I should have clarified. My company uses excel to layout large amounts of information related to signage manufacturing. Room numbers, Department names, Personnel names, paragraphs of text, lists, product codes, and the text varies from Upper-lower to all Caps. The normal spell check function is often impractical as it wants to check every name or product code, and these can number in the hundreds, even thousands in extreme cases. And it's apparent that many people will opt to skip spell check altogether, rather than click ignore...ignore...ignore. I currently have a different macro that highlights any cell with a misspelling and it works great, but I'm working on this one for cells with long paragraphs of text or lists, where it may be difficult to pick out the word. And the macro works great so far. I just need to figure out how to add another delimiter, or find an alternative if possible. It's an odd problem.

**Jakobshavn** · 02-25-2014, 04:00 PM

Why not just replace the Chr(10)'s with Chr(32)

Please Login or Register  to view this content.

**TKFRMjarvis** · 02-25-2014, 04:04 PM

That may work. Would that alter the text in the cell? Because I have to maintain the line breaks that were entered.

**Jakobshavn** · 02-25-2014, 04:54 PM

It should not alter the text in the cells with respect to the word separators because you are not re-joining vWords()

Your code does not change the characters in oCell, only their formatting.

**TKFRMjarvis** · 02-25-2014, 05:20 PM

Hmm. I'm running it and getting the same results. The misspelled word is below 2 other words, separated by line breaks (alt+enter). All 3 words are being highlighted. I can't see why it wouldn't work, or why the chr(10)'s aren't getting replaced by 32.

**MarvinP** · 02-25-2014, 05:24 PM

Perhaps you have some char(160) mixed in with the space characters. I see this problem frequently.

**TKFRMjarvis** · 02-26-2014, 03:01 PM

I've been fooling around with it and my problem is somewhat enigmatic. If I find & replace the Chr(10)'s by typing alt+0010, they all are found and replaced, so the Chr(10)'s are definitely there. But when I use vWords = Replace and tell it to find them, it doesn't seem to be working, because it highlights every everything that is separated by a Chr(10) and only stops when it hits a Chr(32). One thing I'm unsure about is if VB is searching for a 10 or a 0010? Not sure if this would be a factor, but I know Find/Replace only finds them in the spreadsheet if I type 0010.