instr

[Former Member]

i used this to retrieve values form a string field where the string has multiple words

1234568 john smith

it retreives the numbers prior to the space

LEFT ({TableName.Field}, InStr({@field}, " "))

if there is only SNAP it returns a blank value

how do i get it to return the vale if there are multiple words or just one?

[Former Member · Accepted solution]

if instr(x,' ') = 1 then
split(x)[2]
else if instr(x,' ') > 0 then
split(x)[1]
else
x;
// where X is the string field

[Former Member · Accepted solution]

its a beautiful thing, thank you

[Former Member]

Try this...

IF IsNumeric(LEFT ({TableName.Field}, InStr({TableName.Field}, " "))) 
THEN LEFT ({TableName.Field}, InStr({TableName.Field}, " "))
ELSE ""

This will return an empty string if the results of the original formula aren't numeric.

HTH,

Jason

[Former Member]

i dont think i explained it properly- i am trying to create an id field from a string field that isnt always consitant.

my data looks like this

name field

123456 john smith

jack snap

joe

i need to take the first part of the string if there are spaces between the words or there is only one word.

my field should end up looking like

123456

jack

joe

if my field looks like this i dont get the first value it is blank, i need to retrieve the first word here.

/AKERS NATIONAL ROLL COMPANY

if my field is just one value 7041001

i get nothing.