tags:

views:

810

answers:

1
Q: 

Excel: string parsing VBA

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  2009-05-29 21:43:20
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  2009-05-29 21:47:25
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  2009-05-29 23:00:51
Thanks. I found out by using it anyway.
stanigator  2009-05-30 04:29:44

related questions

How can I find user's time zone offset in excel
One line macro
How to Reverse the Order of a Selected Range of Excel Columns using VBA
Return background color of selected cell
EXCEL VBA CSV Date format problem
VBA - "Can't find Project or Library" for standard VBA functions
Highlight and unhighlight specific ranges when a box is clicked
Why can't I set Application.ReplaceFormat.NumberFormat to some valid formats?
How do I avoid run-time error when a worksheet is protected in MS-Excel?
Can a vba function in excel return a range?
Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
Permission Denied opening an Excel File using Excel 12.0 Library & VB6
Find the directory part (minus the filename) of a full path in access 97
automatically execute an Excel macro on a cell change
What is a good up-to-date book for an experienced developer to learn Excel VBA?
Quitting Excel using VBA causes run-time error 424
Create an Excel macro which searches a heading and copy-paste the column
McAfee deletes code from VBA module
copying website data to XL 2003
How to adjust macro?
Manipulate a file in code (VB.NET) without executing the file's macros
How do I set the background color of Excel cells using VBA?
VBA-Sorting the data in a listbox, sort works but data in listbox not changed
Editing Embedded PowerPoint from Excel VBA
Top ten ordering in Excel based on complex team rules