Help to replace text withing string field - CR version 7

[jannoth]

Hi all.

A text field contains the following string:
A=£64.00; B=£49.00; C=£10.00

I need to replace the A, B and C so that the text reads as follows:

Full=£64.00; Partial = £49.00; Remitted = £30.00

Can anyone please suggest a formula that would work in version 7?

Thanks

J

 

[lbass]

The problem is in knowing what functions are available in 7.0. Can you use the split() function?

-LB

 

[PatriciaObreja]

Use the Replace() function if it exists in version 7.

-----------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[jannoth]

Hi. Thanks for your replies and please remember that I'm not technical!

1. there is no "Split()" function - the only reference to splitting relates to splitting sections.
2. there is no "Replace()" function - which I know about from version 11 - but it isn't in version 7.

Any other ideas??

Ta

J

 

[CR85user]

Is extractstring() available in v7?

Is the layout always the same in terms of the position of characters?

If so then worst case scenario use a formula such as:

'Full = ' + mid({table.field},3,8) + 'Partial = ' + mid({table.field},13,8) + 'Remitted = ' + right({table.field},8)

Sadly though this would only work if the value is always £NN.NN - Replace / Extractstring / Split would all be better options.

'J

 

[CR85user]

Maybe you couls use instr() to better locate the positioning for £ and . symbols to do the above sting manipulation more accurately.

Also is picture() available in v7?

'J

 

[jannoth]

Sadly, Exactstring() isn't available in v7 but your suggested formula about using substrings looks great - just the sort of thing I've been looking for. The pain is that amounts vary so sometimes a fee might be £10.00 whereas it might drop to £9.00 so it's likely to break (

I know of the existence of Instr() so using the alpha letters as reference points would work (there would always be three letters: A, B and C) but I wouldn't have a clue how to assemble a short formula to do it (

Finally, Picture() does exist in the form
Picture(string,picture) - but how would that help, hehe? The Instr() sounds very possible )

Thanks again to everyone!!

J

 

[jannoth]

Sorry - I've just reread my last post and it didn't make my point well (

The Instr() solution looks very promising, perhaps the only way, but could anyone please suggest a suitable formula using it? To recap, what I need is to examine a string such as this:

A=£64.00; B=£49.00; C=£10.00

Then replace the "A" with "Full", "B" with "Partial" and "C" with Remitted - where the amounts are not guaranteed to be fixed widths - so "C=£10.00" might sometimes be "C=£9.00".

Thanks - sorry about my poor communication |

J

 

[CR85user]

Double check if the function is available first: it is extractstring() not exactstring().

Picture again has the same flaw as the positional mid() as it is great if always the same format - but if not then it won't work.

I will do a quick test string using instr and post back a quick formula to suit.

'J

 

[jannoth]

Nope, extractstring doesn't exist either - just checked the user manual too |

 

[CR85user]

Just did a quick n dirty try of the following:

//{@Convertstring}
'Full: ' + MID({table.string},INSTR({table.string},'A=')+2,6) + ' Partial: ' + MID({table.string},INSTR({table.string},'B=')+2,6) + ' Remitted: ' + MID({table.string},INSTR({table.string},'C=')+2,6)

It still isn't how I would liek to see it though.

What kind of figures would you expect to see in your sample data?

For example:

£9.00
£45.00

Would they go into the hundreds etc?

'J

 

[jannoth]

These relate to short training courses so it's possible that A could reach hundreds (but never thousands) but B and C would never even reach the hundreds. So:

A could be £9.00 or £45.00 or maybe £120.00
B & C should only ever be £9.00 or £45.00 - never more.

J )

 

[CR85user]

Ok, Let me add in a quick isnumeric() to check lengths. (Can you double check that the function is avail?)

Ta


'J

 

[jannoth]

V7 really is basic, eh? No isnumeric function either (

Not making your life any easier, am I?

Sorry!

 

[CR85user]

Try this:

(
'Full: ' +
If isnumeric(Mid({@table.string},Instr({@table.string},'A=£')+3,3)) then
mid({@table.string},instr({@table.string},'A=')+2,7) else
If isnumeric(Mid({@table.string},Instr({@table.string},'A=£')+3,2)) then
mid({@table.string},instr({@table.string},'A=')+2,6) else
If isnumeric(Mid({@table.string},Instr({@table.string},'A=£')+3,1)) then
mid({@table.string},instr({@table.string},'A=')+2,5)
)
+
(
' Partial: ' +
If isnumeric(Mid({@table.string},Instr({@table.string},'B=£')+3,3)) then
mid({@table.string},instr({@table.string},'B=')+2,7) else
If isnumeric(Mid({@table.string},Instr({@table.string},'B=£')+3,2)) then
mid({@table.string},instr({@table.string},'B=')+2,6) else
If isnumeric(Mid({@table.string},Instr({@table.string},'B=£')+3,1)) then
mid({@table.string},instr({@table.string},'B=')+2,5)
)
+
(
' Remitted: ' +
If isnumeric(Mid({@table.string},Instr({@table.string},'C=£')+3,3)) then
mid({@table.string},instr({@table.string},'C=')+2,7) else
If isnumeric(Mid({@table.string},Instr({@table.string},'C=£')+3,2)) then
mid({@table.string},instr({@table.string},'C=')+2,6) else
If isnumeric(Mid({@table.string},Instr({@table.string},'C=£')+3,1)) then
mid({@table.string},instr({@table.string},'C=')+2,5)
)

'J

 

[CR85user]

lol! - You are right it is basic....

I didn't realise v7 was without the function, I will go lookup what is available that we can work with.

Ever thought about upgrading?

'J

 

[jannoth]

Hehe - I have v11 on my PC BUT we need to stick with v7 for one or two reports cos the report distribution is very handy and missing from later versions (maybe TOO good).

I have located a promising Viewer for v11 which refreshes data too - but it's Beta, not through Business Objects and totally unsupported, so needs lots of testing first.

Until then, it's back to the grind )

J

 

[CR85user]

I beleive you can use instr() and ubound() is that correct?

'J

 

[jannoth]

Instr() = YES
Ubound() = NO

Source = CR7 F1 Help (User Guide = useless!)

 

[lbass]

Try this:

stringvar x := {table.string};
"Full=" + mid(x,instr(x,"A")+2, instr(x,"B")-3-instr(x,"A")+1)+
"Partial=" + mid(x,instr(x,"B")+2, instr(x,"C")-3-instr(x,"B")+1)+
"Remitted=" + mid(x,instr(x,"C")+2)

-LB