Function Parse Problem

  • Thread starter Thread starter Shatin
  • Start date Start date
S

Shatin

There is a column in my spreadsheet whose value is usually like this:

36.5,24.0,24.2

I use VBA's parse function to break the number into three columns:

mColumn.Parse parseline:="[xxxx] [xxxx] [xxxx]",
Destination:=nColumn.Cells(1)

For the most part, this single line of code works well. Then there's
the odd cell which is like this:

103.3,24.8,25.2

The first number has an extra digit and the results are wrong. I tried
to solve the problem by examing which cells have the extra digit and
use the left, mid and right functions to deal with those special
cells. None of my formulas worked. The cells would always have the
same wrong values. Out of frustration, I tried to clear the contents
of the cells created by the parse function (with VBA, not at the Excel
page itself). To my amazement, I couldn't do that. It seems that the
values placed in the three new columns by the parse function cannot be
changed. I am now scratching my head as to what I can do. Perhaps
someone can tell me a better way to parse those numbers?
 
Shatin,

Take a look at VBA's Split function, which split a string based on a
delimiter and returns an array: below is an example that works on the
activecell.

HTH,
Bernie
MS Excel MVP

Dim myArr As Variant
myArr = Split(ActiveCell.Value, ",")
ActiveCell(1, 2).Resize(1, UBound(myArr) _
- LBound(myArr) + 1).Value = myArr
 
Of course, if you know that you will always have three items:

ActiveCell(1, 2).Resize(1, 3) = Split(ActiveCell.Value, ",")

will work just fine. You can change the range for the .Resize to move
the results anywhere you want, of course.

HTH,
Bernie
MS Excel MVP

Bernie Deitrick said:
Shatin,

Take a look at VBA's Split function, which split a string based on a
delimiter and returns an array: below is an example that works on the
activecell.

HTH,
Bernie
MS Excel MVP

Dim myArr As Variant
myArr = Split(ActiveCell.Value, ",")
ActiveCell(1, 2).Resize(1, UBound(myArr) _
- LBound(myArr) + 1).Value = myArr


Shatin said:
There is a column in my spreadsheet whose value is usually like this:

36.5,24.0,24.2

I use VBA's parse function to break the number into three columns:

mColumn.Parse parseline:="[xxxx] [xxxx] [xxxx]",
Destination:=nColumn.Cells(1)

For the most part, this single line of code works well. Then there's
the odd cell which is like this:

103.3,24.8,25.2

The first number has an extra digit and the results are wrong. I tried
to solve the problem by examing which cells have the extra digit and
use the left, mid and right functions to deal with those special
cells. None of my formulas worked. The cells would always have the
same wrong values. Out of frustration, I tried to clear the contents
of the cells created by the parse function (with VBA, not at the Excel
page itself). To my amazement, I couldn't do that. It seems that the
values placed in the three new columns by the parse function
Click to expand...
cannot
be
changed. I am now scratching my head as to what I can do. Perhaps
someone can tell me a better way to parse those numbers?
Click to expand...
Click to expand...
 
Why don't you use Data/Text to Columns ? You have a comma delimiter.
VB TextToColumns.

Regards
BrianB
 
The reason I didn't use TextToColumns is that I didn't know about it.
Now that I know about it, it works perfectly for my macro! Now I am
going to experiment with the Split function as well...Thanks to you
guys for your help!
 
Back
Top