Spliting this string: "2 hours, 15 minutes" into 2 parts 3

[Halfcan]

Hi All,

I have a string: "2 hours, 15 minutes" that I need to eventually convert to numbers.

The hard part is that the 2 and the 15 portions can vary in length
ex: "100 hours, 15 minutes" and Im not sure what to do.

My first thought was to split the hours value and minutes values apart into separate values:
ex: result of formula 1: would = 2 hours,
and result of formula 2: would = 15 minutes.

Then since the "hours," portion would always be the same number of characters, I would use another formula to chop off the " hours," portion leaving the 2, then convert the 2 from a string to a number.

Same for 15 minutes.

Any ideas?

Using CR 8.5

HC

 

[vidru]

If the comma is always present, you could use the Split function:

// Split the string at the comma
StringVar strHours := Split({Table.Field},",")[1];
StringVar strMins := LTrim(Split({Table.Field}, ",")[2]);

// Get the Hours value
NumberVar Hours := Val(Replace(strHours, "hours", ""));

// Get the Minutes value
NumberVar Minutes := Val(Replace(strMins, "minutes" ,""));

-dave

 

[lbass]

Or, to simplify a little, I think you could just use:

//{@hours}:
val(split({table.string},",")[1])

//{@minutes}:
val(split({table.string},",")[2])

-LB

 

[bdreed35]

LB(*) - I didn't know you could use the val function to drop the hours or minutes string.

BTW, congrats on being Tek-Tips Tipmaster of the Week. It is always good to see one of our own honored.

~Brian

 

[lbass]

Thanks, Brian!

-LB

 

[Halfcan]

Thanks Guys, these formulas are exactly what I was trying to do.
HC

 

[Halfcan]

Hi again,

something that I didn't forsee came up.

The data string isn't always in the format that I thought.

example: most common: 2 hours, 10 minutes.
however, if no hours are entered, the string is: 10 minutes
or the opposite, if no minutes entered: 2 hours.

The problem is that the delimiting character is a comma, and the second 2 examples don't have comma's.

Using the formulas:
//{@hours}:
val(split({table.string},",")[1])

//{@minutes}:
val(split({table.string},",")[2])

crystal crashes when the half of the string is null or doesn't have a comma.

String possiblities:
2 hours, 10 minutes = ok
10 minutes = crash
2 hours = crash

One person recommended that I try to deal with this using
regular expressions, but I'm kind of lost as I'm not a programmer...any ideas? sorry....

HC

 

[vidru]

You can modify LB's formulas a bit to test the format of the string:

//{@hours}:
if instr({table.string},',') <> 0 then
val(split({table.string},",")[1])
else if instr({table.string},'hours') <> 0 then
val({table.string})
else
0;

//{@minutes}:
if instr({table.string},',') <> 0 then
val(split({table.string},",")[2])
else if instr({table.string},'minutes') <> 0 then
val({table.string})
else
0;

-dave

 

[Halfcan]

Vidru gets a shiny star.

Thanks, it works great.

HC