Loop all cells to remove bracketed text

[gcoug]

Morning All

I am shredding a large excel file (extracted from an xml file). A large number of the cells contain data with large bracketed text strings and superfluous information. The information desired is the information to the left of the first bracket. It will be used to populate lookup tables in Excel or import to Access (too much info for Access to import currently).

Example Cell Value:
125.34 g [Environment; Tech Info for Problem Spills: Ethylbenzene p.83 (1982)]**PEER REVIEWED**

Desired:
125.34 g

I did a search and replace to get rid of the **PEER REVIEWED**.

I added columns to used a following formula to get rid of the bracketed text:

=IF(ISERROR(FIND("[",A1),A1,LEFT(A1,LEN(A1)-(LEN(A1)-FIND("[",A1)+1))))

The search and replace and adding columns etc. allows me to look at the data to ensure minimal errors but is really, really time consuming and Excel gives calculation time warnings. I would like to do it all in code and do it for each cell. Frought with peril but better than hand editing.

Open for suggestions on the best approach. Would like to leave the null strings for now just show it was evaluated.

[tigeravatar]

gcoug,

Something like this?

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[snb]

Please Login or Register  to view this content.

[gcoug]

Tiger

I tried the code and the result was interesting.

It gave a run-time error '1004' and exited. Looking at the data showed, the bracketed data was removed for rows 1 to 6 and somewhere near row 6 column AB it stopped. A debug.print to track r, c on the inner loop has it going all the way.

I don't understand the

Please Login or Register  to view this content.

The delimiter is "[" but I'm not familiar with the ")(0)" part. Is the "0" the binary compare operator?

Bob

[tigeravatar]

Bob,

I'm not sure why you got the error, it ran fine for me
As for your question, Split splits a string into an array of elements delimited by the specified delimiter (which was "["). Element 0 is everything found before the "[" so putting the (0) at the end returns element 0 of the array.

For example:
Split("This is a test"," ")(2) = "a"

That would return element 2. Split is splitting the string "This is a test" into an array delimited by spaces, so 0 is "This", 1 is "is", 2 is "a" and 3 is "test".

I would also recommend trying snb's code which should perform a mass find/replace and seeing if that works for you.

~tigeravatar

[gcoug]

snb

Not overlooked. Just old linear thinker and better code reader than coder.

I was looking at your code and referencing the help menus in Excel before I asked another dumb question. I'm not familiar with the structure of your arguments (call it inexperience). I try to run in both the 2003 and 2007 versions. The 2003 machine is connected to the net and 2007 is a stand alone (personal laptop).

[gcoug]

Thank you Tiger and snb!

I got both methods to work fine and I learned quite a bit today. It would help if could type better, faster.

On to the next challenge!