Text Formatting

[AntonioPerera]

Hi ,
I have a field which contains data in the following ad hoc manner, here are some examples :

Type # 1
John Smith<br>9 days at $1,800/day

Type # 2
System Implementation - Peter Jones & Paul Simpson<br>Period: 15/11/08 to 28/11/08

Type # 3
Market Operations Management Improvement - David Cross<br>Period: 24/11/08 to 28/11/08


My idea is to pick the names appearing and transform them into codes.
It should pick the first person's name in the case of type 2 where there is more than one person.

I have a formula which pick the names and transform them into codes as follows :

whileprintingrecords;
if {table.field} [1 to 11]= "John Smith" then "JS01" else
if {table.field} [1 to 12]= "Peter Jones" then "PJ01" else
if {table.field} [1 to 11]= "David Cross" then "DC01" else
" "

For some reason the codes for type 1 examples are picked correctly but type 2 & 3 ones don't get picked.

What I am doing wrong ?
Any suggestions ?

 

[lbass]

The names are not necessarily in the first position. I would do something like this:

if instr({table.field},"-") > 0 then
(
if instr({table.field},"&") > 0 then
extractstring({table.field},"- "," &") else
extractstring({table.field},"- ","<br>")
) else
(
if instr({table.field},"&") > 0 then
left({table.field}," &") else
left({table.field},"<br>")
)

This would have to be modified if there could be more than two names in the field though.

-LB

 

[AntonioPerera]

Hi Lbass,

When I try to save the formula,it gives me a message stating "A number is required here" on this line :

left({table.field}," &") else

 

[lbass]

Sorry, try:

if instr({table.field},"-") > 0 then
(
if instr({table.field},"&") > 0 then
extractstring({table.field},"- "," &") else
extractstring({table.field},"- ","<br>")
) else
(
if instr({table.field},"&") > 0 then
left({table.field},instr({table.field}," &")-1) else
left({table.field},instr({table.field},"<br>")-1)
)

-LB