Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Parse Problem
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Parse Problem
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" wrote in message om... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Parse Problem
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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message om... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Parse Problem
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel invoke REST service function with Xpath fn parse result! | Excel Worksheet Functions | |||
How do I parse the cell reference function? | Excel Worksheet Functions | |||
Parse from the Right | Excel Worksheet Functions | |||
Parse formula | Excel Programming | |||
Parse | Excel Programming |