Right function starting at specific character

Hi,

pls try

MID({myField}, INSTR({myField }, " ") + 1, LEN({myField})-( INSTR({myField }, " ") + 1))

Select allOpen in new window

Regards

here is what i entered as "myfiedld" is  {@EquipmentID}
MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}-(INSTR({@EquipmentID}, " ") + 1)))

Get error " A number, currency amount, date, time or date time is required

You have the ( ) paired in the wrong places.  Try that this way

MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, " ") + 1)

mlmcc

Tried the first fuction SPLIT and got the following error "A subscript must be between 1 and the size of the array"
Split({@EquipmentID},'-')[1]

Tried the other MID function and returned the full text.
MID({@EquipmentID}, INSTR({@EquipmentID}, " ") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, " ") + 1)

THe INSTR needs to search for  -

MID({@EquipmentID}, INSTR({@EquipmentID}, "-") + 1, LEN({@EquipmentID}) - INSTR({@EquipmentID}, "-") + 1)

the subscript error indicates some don't have a - or are NULL

If InStr({@EquipmentID}, "-")  > 0 then
     Split({@EquipmentID},'-')[2]
Else
     {@EquipmentID}

Select allOpen in new window

What is the {@EquipmentID} formula?

mlmcc

if {RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}="-" then "" else {RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}

Used the equipmentid field in the table and the split function worked.  Thank you

found one more issue.

there are sometimes two "-" in the text field how do I make sure i always get the text after the last dash?

example:

487-156-knoxville
12345-knoxville

desired output:
knoxville
knoxville

Try this with the split

Split({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID},'-')[UBound(Split({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID},'-')]

mlmcc

FWIW, another way to get everything after the last "-" would be:

Mid ({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}, InStrRev ({RS_ROUTE_EQUIPMENT.EQUIPMENT_ID}, "-") + 1)

 If the field could be null, you could add a check for that.

 James