If char help

[#1]

hi

in excel sheet in a column [Ticked_Id] i have 5 different type od data

1)   0

2)   C /CNL FEE

3)   F16071

4)   F16071/0000000241

5)   FF16071 /0000000141 24A20

first three (1,2,3 ) wont change

but  at last two (4,5) i want the code after "/" as 10 character  (0000000241)

at (4)  "/" is 7th character; at (5) "/" is 9th character after space.

how i can write this in is statement.

thanks

[#2]

is this what you need?

if(index(value,'/'),right(value,len(value)-index(value,'/')), value) as field

[#3]

sorry you are right my mistake

1)   0

2)   C /CNL FEE

3)   F16071

4)   0000000241

5)   0000000141

output will be like above. 

[#4]

Alejandro  thanks but your expresion gives  only  3) and 4) as output

[Jason_Michaelides]

And how is QlikView supposed to know that row 2) needs a different rule applying?  Can you please state the rule you want to apply to every row - in plain English, not script code? My expression will left the text between a / and a space in any and every string.

[#6]

my input is

1)   0

2)   C /CNL FEE

3)   F16071

4)   F16071/0000000241

5)   FF16071 /0000000141 24A20

my output  must be

1) 0

2) C /CNL FEE

3) F16071

4)  0000000241

5)  0000000141

1) 2) 3) will stay same

for 4) output must be 10 character after '/'

for 5) output must be 10 character after '/'  but there ise a space before '/' in 5)

thnak you very much, i took so much of your time

note: Jason your last statement turns 5)   FF16071 /0000000141 24A20  as empty

[Jason_Michaelides]

merakist,

I cannot see a standard rule that can be applied here. You have a slash "/" and space " " in row 2) but want them ignored, however in rows 4) and 5) you want to pick the text between the "/" and " ". How am I or anyone else supposed to know which rule to apply when!?

Or is the difference becasue there is a letter after the / in row 2), not a number as in rows 4) and 5).

Do you see what I'm asking? In order to write you some code to extract what you need you must explain the Plain English rule e.g. "I want everything between the forward slash and the space. If there isn't a slash then start at the beginning and if there isn't a space end at the end."

As Tom Cruise said to Cuba Gooding Jr. "Help me to help you!"

[#8]

ok Jason, i thought that we could use your statement in an if statement.

i changed the data with REPLACE function  and my new fromat is like:

F16071/0000000241

FF16071/000000014124A20

now i need to take only 10 characters after '/'. can  you help me about this?

my inputs are above and finally i want 0000000241 as output for both of them

[Jason_Michaelides]

So to be clear - the rule you want to apply to all values loaded in this field is "Give me the first 10 characters after the first forward slash". Is that correct?

[#10]

yes, it  is now clear. i want exactely this

[Jason_Michaelides · Accepted solution]

If that is definitely the rule you want to apply then try:

LEFT(TEXT(SubField(YourField,'/',2)),10)

or

MID(YourField,Index(YourField,'/'),10)

Jason