TEXTBETWEEN Question

[jerryr125]

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

[stevejoyce · Accepted solution]

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)

[stevejoyce]

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.

[MarcoWedel]

Mid(productinfo,Index(productinfo,'ABC')) as ABCINFO 

hope this helps

Marco