I am looking for a formula to find the wildcard character position in a string

[Naga]

Hello,

Happy New year.

Need help. I am looking for a formula to find the wildcard character position in a string like below. I tried substring, but it was not helping me as it gives me -1 as output.

Abcd*1234 - I would need 5 as the * position

Thanks

Naga

[jrgo]

@Naga 

Try

REGEX_REPLACE([FIELD], 'abcd(.)1234', '$1')

Edit:

REGEX_REPLACE([FIELD], '^.{4}(.).*', '$1')

[wdavis · Accepted solution]

Hi @Naga 

The formula - STRCSPN([FIELD],'*')+1 should work if you are just looking to return the position of the wildcard character.

Thanks

Will

[jrgo · Accepted solution]

@Naga 

Sorry, I misunderstood your request. Assuming I understand correctly, you want to find the position of the *. You can use the FindString function. By default it's a 0 based function meaning that the first position is 0 so in your example, the 0 based position of the * is 4. The added +1 would convert to 5.

FindString([Field1], '*')+1