Excel 2003 - Parsing

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

I have figured out several ways of doing this, but would like to know if
anyone knows of a way to do it with a single formula. The reason for this is
the large amount of data does not make breaking it down with macros very
practrical from a time-cost viewpoint.

I have the result of a query in a cell. The result contains seven variable
length varibles, separated by commas. I would like to get the last (seventh)
varible.

2008-12-17,17.42,18.11,17.42,18.02,113000,18.02
should result in the cell equaling 18.02

2008-12-16,110.93,112.48,110.50,112.35,388000,112.35
Should result in the cell equaling 112.35



Any ideas?

Craig
 
=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,",","^",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))
 
Niek,


It took me awhile to figure what that code was doing, but I did figure it
out and it was brilliant. Since I know that there will always be 7
variables, I replaced the code that counted the commas with a six.

Thanks, it is exactly what I needed and is working like a charm.



Craig
 
I have figured out several ways of doing this, but would like to know if
anyone knows of a way to do it with a single formula. The reason for this is
the large amount of data does not make breaking it down with macros very
practrical from a time-cost viewpoint.

I have the result of a query in a cell. The result contains seven variable
length varibles, separated by commas. I would like to get the last (seventh)
varible.

2008-12-17,17.42,18.11,17.42,18.02,113000,18.02
should result in the cell equaling 18.02

2008-12-16,110.93,112.48,110.50,112.35,388000,112.35
Should result in the cell equaling 112.35



Any ideas?

Craig
Click to expand...


=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99))

--ron
 
Great, Ron! It took me quite a few times "Evaluate formula" to understand
how it works, real nice!

Would a somewhat similar formula be possible for, for example, the last but
one string, where the number of strings is unknown?
If so, I could greatly simplify my standard approach; count the number of
separators, find the position of the n-th one, etc.
 
Great, Ron! It took me quite a few times "Evaluate formula" to understand
how it works, real nice!

Would a somewhat similar formula be possible for, for example, the last but
one string, where the number of strings is unknown?
If so, I could greatly simplify my standard approach; count the number of
separators, find the position of the n-th one, etc.
Click to expand...

Well, the approach becomes less robust as the segments become longer, and the
distance from the last segment becomes greater. But for second from the last,
using that approach, I suppose one could use:

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),198)),
FIND(" ",TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),198)))-1)

But I prefer to use a regular expression approach.

For example, if you have Longre's morefunc.xll installed (or something
equivalent as a UDF), you could use:

=REGEX.MID(A1,"[^,]+",-2)

Where the regex: "[^,]+" means to group all the characters up to any
character that is not a comma; and the -2 indicates to take the second group
from the end.
--ron
 
Great, Ron! It took me quite a few times "Evaluate formula" to understand
how it works, real nice!
Click to expand...

And by the way, the approach did not originate with me. It's been kicking
around on the NG for quite a while -- and I don't know who posted it first.
--ron
 
But I prefer to use a regular expression approach.
For example, if you have Longre's morefunc.xll installed (or something
equivalent as a UDF), you could use:

=REGEX.MID(A1,"[^,]+",-2)

Where the regex: "[^,]+" means to group all the characters up to any
character that is not a comma; and the -2 indicates to take the second
group
from the end.
Click to expand...

A simple UDF to do this would be...

Function Field(R As Range, Num As Long) As Variant
Field = Split(R.Value, ",")(Num - 1)
End Function

where R is a reference to the cell with the list and Num is the field number
desired (6 in this case), so after putting the above function in a Module,
the worksheet function call would be...

=Field(A1,6)
 
But I prefer to use a regular expression approach.

For example, if you have Longre's morefunc.xll installed (or something
equivalent as a UDF), you could use:

=REGEX.MID(A1,"[^,]+",-2)

Where the regex: "[^,]+" means to group all the characters up to any
character that is not a comma; and the -2 indicates to take the second
group
from the end.
Click to expand...

A simple UDF to do this would be...

Function Field(R As Range, Num As Long) As Variant
Field = Split(R.Value, ",")(Num - 1)
End Function

where R is a reference to the cell with the list and Num is the field number
desired (6 in this case), so after putting the above function in a Module,
the worksheet function call would be...

=Field(A1,6)
Click to expand...

Of course, one can always write a UDF that does not use Regular Expressions
that will do exactly the same thing as can be done using Regular Expressions.
Sometimes the resulting UDF is simple, as in this case; sometimes it is more
complex.
--ron
 
Back
Top