Find string in random looking text field

[#1]

Hi,

I have a text field which varies in length and structure. However, in there somewhere it says TIME followed by an actual time.

Is there a way to fetch this information somehow in the script? To create a time field out of the below?

TEXT field examples:

1. blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639

2. blabla 8282828292909292892 jahjahjhjahja TIME 12:37

Kind rGDS,

oLLE

[swuehl]

Try something like

LOAD

     Textbetween(TextField, 'TIME ', ' ') as Time,

...

edit:

or

LOAD

     Mid(TextField, index(TextField, 'TIME ')+5,5)  as Time,

...

[ThornOfCrowns]

use the INDEX function to pick out the text, then use that as part of the MID function.

For example index(yourstringname, 'TIME ',1) shows you wher TIME is.

MID(yourstringname,index(yourstringname, 'TIME ',1),4) will cut out the next 4 chars after TIME

you can then format it as hh:mm if you require.

[alexandros17]

Try this

=If(Index('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', 'TIME')>0,
Mid('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', Index('blabla blabla 1234 blabla TIME 12:00 blabla blaba 439282639', 'TIME')+5, 5)
)

[sasiparupudi1 · Accepted solution]

Trim(SubField(SubField(Text,'TIME',2),' ',2)) as Text1