Extracting Data from formula field 1

[idehen]

CR version: 10

I have a report that displays as:

Name Currency Amount
John USD EUR GBP 12345.00 5647.00 891.00
Mike USD GBP 1258.21 56.0

Notice that Currency and Amount are showing multiple data on each line. This is because i used a formula to pick up the data from showing horizontal to single lines seperated by spaces.

Question:
Given that the Currency and Amount are both formula, how can i create another formula to pick up single data out into columns of its own.

when i use formula, Split ({Curency}," ") [1] and then [2] respectively, they seem to work picking the first and second data of the currency but when i use [3], i get an error message

*A subscript must be between 1 and the size of the array*

How can i get around this as sometimes currency can be 3 currencies in the case of John or 2 currencies in the case of Mike. I want each currency in seperate column.

This also applies to the amount field. Am sure one solution will sort the other out.

Thanks guys.

Sol

 

[lbass]

Add an introductory clause to each formula, e.g.,

if ubound(split({@Currency}," ")) >= 3 then
split({@Currency}," ")[3]

-LB

 

[idehen]

Thanks very much Ibass. Fantastic. worked perfect!