Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - Question about a text field and formula.
I have a table with a field that contains text (field name : productinfo). The field contains a lot of text. I would like to obtain the text at a starting point (say ABC) and all the way to the end of the field. The text in the field varies.
Example:
productinfo =
This is miscellaneous text provided with a variety of information. ABC this is information I would like to obtain.
I would like a new field on the data load (say ABCINFO). Therefore, in the example above :
ABCINFO = ABC this is information I would like to obtain.
I am not sure if and how TEXTBETWEEN would work in this situation.
Any thoughts ? Jerry
i suppose you could use textbetween like
textbetween(productinfo, 'abc', right(productinfo,1) & right(productinfo,1)
i would use subfield. this also assumes you don't have 'abc' > 1 time in your string
subfield(productinfo, 'abc', 2)
if you want to include the text abc, then do...
'abc' & subfield(productinfo, 'abc', 2)
i suppose you could use textbetween like
textbetween(productinfo, 'abc', right(productinfo,1) & right(productinfo,1)
i would use subfield. this also assumes you don't have 'abc' > 1 time in your string
subfield(productinfo, 'abc', 2)
if you want to include the text abc, then do...
'abc' & subfield(productinfo, 'abc', 2)
thinking about this a bit more, maybe better off using right(), with index() and len()
=right('productinfo', productinfo - (index(productinfo, 'abc') - 3))
//taking right N number of characters. where N = total length of string - index/position of 'abc' + 3 (length of 'abc') if you want to include abc in your result.
Mid(productinfo,Index(productinfo,'ABC')) as ABCINFO
hope this helps
Marco