vbTextCompare not performing as expected in vba

**johnmerlino** · 07-09-2011, 06:05 PM

Hey all, the cell can contain a number of values (e.g. &W, &H, & , etc). If the test passes, then that means the current string in the cell contains one of the above delimiters. The problem is when I grab the secondary name (the name to the right of the delimiter), part of the delimiter remains because the delimiters contain different character lengths:

cell c output:
W abc c def
H abc c def
abc c def
abc c def

original data in cell A:
abc a def &W abc c
abc a def &H abc c
abc a def & abc c
abc a def &abc c

what I want in cell C:
abc c def
abc c def
abc c def
abc c def

The code:

Please Login or Register  to view this content.

I was trying to use vbTextCompare to check if the cell contains " &", and if so subtract 1 from the value of pos, but otherwise subtract 2, but it doesn't appear to be doing that.

Thanks for response.

**stanleydgromjr** · 07-09-2011, 07:26 PM

johnmerlino,

Can we see your workbook and macro code?

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

**protonLeah** · 07-09-2011, 07:33 PM

Please Login or Register  to view this content.

**johnmerlino** · 07-10-2011, 01:05 PM

After taking a break I realized why the vbTextCompare wasn't working. It was comparing for " &" which all the cells contained, so I had to limit it by the pattern i wanted to targed:

Please Login or Register  to view this content.

Also I think the answer above misunderstands what I am trying to do. This:

Please Login or Register  to view this content.

split will return a new array with the extracted contents and then it returns index position 0, which would be the main name. But The issue I was having was the secondary name where part of that name is extracted from main name.

**protonLeah** · 07-10-2011, 02:49 PM

Does that mean that you solved the problem? If not then:

Please Login or Register  to view this content.

Notes:
1. Your original post gave a sample of the original data in column A and a desired output for column C that looked like the LEFT side of the original data.
2. The Split function, in your case, gives just a two element array with index zero holding the left side of the original data and index one holding the right side.
3. Once the original data is broken at the ampersand, the test for the remaining delimiters is accomplished with a small test loop.