tags:

views:

810

answers:

1

I would like to parse a cell column of strings in Excel VBA, each of the cell contains the following:

2 '␂'

I would like to change the contents of this cell to contain only 2 (or any numeric characters just before the 'string'. What would be the easiest method of parsing such info? Thanks.

+2  A: 

If you are sure about the format, find the string before the ' (apostrophe) & change the cell value. Assuming you are on the cell that has this value, use the following VBA code.

activecell.text = mid(activecell.text, 1, instr(1, activecell.Text, "'")-1)

It looks for an apostrophe and extracts characters before it & puts it into current cell.

shahkalpesh
What if the number before the apostrophe is 2000 or 200 instead of 2? How can I make the 2nd parameter of mid function more flexible in terms of how many characters to return?
stanigator
It shouldn't be a problem in that case. The code looks for apostrophe and returns all characters before it starting from 1st character.
shahkalpesh
Thanks. I found out by using it anyway.
stanigator