discard

[smilingjohn]

Hi all,

I have a field by name Description within whic the data is like

nitiative I12878: Business Planet MVP 1 Get a Plan

nitiative I12849: Business Planet MVP 2 Get a Plan

nitiative I12868: Business Planet MVP 1 Get a Plan

...

I am using keepchar function to take only "I12868"

Keepchar(Description,0123456789') as desc

But the thng is i am getting the extra number also which is present in this line lik "1"

but i want only the number followed by I that is "I12849" id's ,

how do we eliminate the other numbers ?

regards

[Anil_Babu_Samineni]

In you data String also there

Keepchar(Description,'I0123456789') as desc

Here, I is caps lock

[Anonymous]

Hi

try this

if you would like to have only 'I12849'  ,'I2878'........etc.,

SubField(Description, ' : ', 1)

[smilingjohn]

Thanks Anil,

This i have tried , but the thing is that along with the ID its taking all the numbers present in that line

for example if the data is like

Descriptio:

"Initiative I12878: Business Planet MVP 1 Get a Plan"

Keepchar(Description,'I0123456789') as desc

then the expected output should be like this : I12878

Actaul : I128781  look it is taking the 1 also

I hope i am clear in explaining

[Colin-Albert]

Try using TextBetween()

     TextBetween(Description, '|', ':') as desc

[Anil_Babu_Samineni]

My Mistake, Check This

[Colin-Albert · Accepted solution]

If you wish to retain the pipe character the use

     TextBetween(Description, ' ', ':') as desc

or

     '|' & TextBetween(Description, '|', ':') as desc

[smilingjohn]

Hi Alluraiah,

I tried using your your script

SubField(Description, ' : ', 1) as id

But it did not work the out put is same as the data , it did not make any change

[Anonymous]

Hi

You can try this as well

SUBFIELD(SubField(Description, ':', 1),' ',2) as ID

anyway you got the correct answer from Colin ...