Substring and Instring function 1

[Hm786]

Hi,

I need a formula to extract only "DIG REG" from
DIG REG-DIGITAL REGULAR field value. I am using Left and instr function to get the result but not able to get rid of the -.


Thanks,
Hamida

 

[lbass]

If it is always on the left, then you just need:

left({table.field,7)

If it could be in another position in the string, use:

mid({table.string},instr({table.string},"DIG REG"),7)

-LB

 

[DataDog]

What's wrong with left(your.field,7)? The "7" is for your 6 characters + the space.

DataDog
'Failure Is Not An Option'

 

[Hm786]

Ok, I have these field values ANLG REG-ANALOG REGULAR,
DIG RBLD-DIGITAL REBUILD,DBS NATIONAL, DIG REG CMTY-DIGITAL REGULAR COMMUNITY SPECIFIC.But I need to show only the value before the - and if the - is not there then only the regular value. My formula

if (instr({Command.t_field_value},'-'))> 0 then

TrimRight (left({Command.t_field_value},(instr({Command.t_field_value},'-'))))
else
{Command.t_field_value}
gives me what I want except with a dash. If I user


Thanks,
Hamida

 

[Turkbear]

Hi,
Basically you want all the chars up to the last char to the left of the '-' but not the '-' itself, so the formula would be:

if (instr({Command.t_field_value},'-'))> 0 then

TrimRight (left({Command.t_field_value},(instr({Command.t_field_value},'-')-1 )))
else
{Command.t_field_value}

Not sure I got the placement correct in the multiple parens, but you will be able to figure it out..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Hm786]

Thank you Turkbear, your formula worked like a charm and mine also.
My formula goes like this

if (instr({Command.t_field_value},'-'))> 0 then
mid({Command.t_field_value},1,INSTR({Command.t_field_value},'-') - 1)
else
{Command.t_field_value}


Thank you for your quick response.

Hamida

 

[Turkbear]

Hi,
No Problem - I use that type of formula a lot to parse names into 'normal' format when they are stored in the form:
LastName,FirstName MI





To Paraphrase:"The Help you get is proportional to the Help you give.."