Is it possible to Use Subfield for more than one criteria

[#1]

Hi

I have a text table that is in one Cell on a spreadsheet

Example below

In our database we have a table that I want to split this data out into new fields.

I have used the following expression in the load script    Trim(SubField(mnoteshistory, '...'))  as Last_Contact

The problem is that not all entries in the data is separated by a '...' how would I separate by '...' and 'none'

Thanks for any assistance

30/03/2015 10:35:28 - Bob Jones
...

27/03/2015 16:52:34 - credit control
...

27/03/2015 16:07:30 - credit control
...

12/03/2015 15:43:47 - credit control
...

12/03/2015 15:42:15 - credit control
...

12/03/2015 15:35:25 - credit control
...

12/03/2015 14:23:40 - credit control
...

27/01/2015 15:48:14 - Sue Smith
none

12/01/2015 14:03:15 - Jill Dixon
none

Regards

Dave

[#2]

Hi Dave,

You can maybe do a replace on the none text and just use the single subfield then

Trim(SubField(Replace(mnoteshistory,'none','...'), '...'))

hope that helps

Joe

[#3]

another option that you have is to use a substring map, which might be better if you have more than just those two potential values for you subfield.

Map:

Mapping Load * Inline [

old,new

none,<Sub>

...,<Sub>

aaa,<Sub>

bbb,<Sub>

];

Trim(SubField(MapSubString('Map',mnoteshistory), '<Sub>'))

[sasiparupudi1]

Use index function

if (index (field,'...')>0 ,subfield,field)

Hth

sasi

[#5 · Accepted solution]

Thanks Joe for your help.

Both work well, I think I will use the substring map as not fully explored the data yet and will give me the flexibility I need.

CHEERS

[#6]

no worries glad to help