Formula to extract string values from concatenated column value

[Former Member]

Hello everyone,

I am trying to make formula where I have to break my data into parts on specific occurrence of characters. I worked on a formula and succeed in breaking the column in to new line. Now I want to extract these break lines of text to use it further.  I tried making the formula where my left first record is extracting before the character '-' for multiple row its not picking the second left first value.

For example :

my column: 1.00000-50000000.00000 : 50.00 |

on breaking :  1.00000-

                     50000000.00000:

                     50.00

but when my column value exceeds

my column : 1.00000-50000000.00000 : 50.00 | 50000001.00000-150000000.00000 : 70.00 |

on breaking : 1.00000-

                    50000000.00000 :

                    50.00

                    50000001.00000-

                    150000000.00000 :

                    70.00 

its not picking the next first left value:

Formula on left I am using :

whileprintingrecords;

stringvar MANG_FEE := cstr({ds_MngFeeComm.MANGE_FEE});

stringVar y := Replace(Replace(Replace(MANG_FEE,'-','-'+CHR(10)),'|',CHR(10)),':',CHR(13));

extractstring(y,'','-');

formula on right I am using :

whileprintingrecords;

stringvar MANG_FEE := cstr({ds_MngFeeComm.MANGE_FEE});

stringVar y := Replace(Replace(Replace(MANG_FEE,'-','-'+CHR(10)),'|',CHR(10)),':',':'+CHR(13));

Please help.

[Former Member · Accepted solution]

Hi,

Use this function ChrW() instead of CHR().

--DJ

[Former Member · Accepted solution]

Would you please explain it more?

[raghavendra_hullur · Accepted solution]

Hi, Could you explain what is your expectation on the replaced string?

Are you looking to extract the string with same delimiter into an array to use it for displaying with different records?

Thanks,

Raghavendra

[Former Member · Accepted solution]

I want to extract values from my string like this :

1.00000 -

50000000.00000

: 50.00 |

50000001.00000 -

150000000.00000 : 70.00 |

I dun' want values after '-'.

In case when there is a singular format :

1.00000 - 50000000.00000 : 50.00 |

my formula is working fine and picking the  value before '-' i.e  1.00000

but In case of this format :

1.00000 - 50000000.00000 : 50.00 | 50000001.00000 - 150000000.00000 : 70.00 |

My formula is not picking the second value before '-' i.e 50000001.00000

[abhilash_kumar · Accepted solution]

Hi Sana,

Try this please:

local stringvar s := {string_field}

local stringvar s1 := Replace(Replace(Replace(s,'-','-'&CHR(13)),'|',CHR(13)),':',':'&CHR(13));

local stringvar array s2 := split(s1, chr(13));

local numbervar i;

for i := 1 to ubound(s2) do

(

    if instr(s2[i],'-') > 0 then

        local stringvar fin := fin + s2[i] + chr(13);

);

Replace(fin,'-','');

-Abhilash

[Former Member · Accepted solution]

Thank you Abhilash

Your formula is working.

No another thing.

I want values after '-' in another formula

In case when there is a singular format :

1.00000 - 50000000.00000 : 50.00 |

50000000.00000

In case multiple format :

1.00000 - 50000000.00000 : 50.00 | 50000001.00000 - 150000000.00000 : 70.00 |

50000000.00000

150000000.00000

Sample data I am showing is not limited to two slabs. it could be three, four five.. so on slabs

[abhilash_kumar · Accepted solution]

Please post that as a separate 'Discussion' per SCN's rules.

-Abhilash