String Manipulation Problem - Remove data after the second character occurrence

[MF.RPA (M.Farrell)]

Hello everyone,

I am having trouble with a complicated string manipulation problem.

It seems like my activity should work but it does not

Background information:

1. I have a file called results, which has a column called total price
2. The total price I am trying to correct is $155.3377 $1553377

Objective

1. Return only $155.3377 deleting everything after the second $

Process

1. I declare a price variable “Price”
2. If Price.ToString.Contains(“$”)
   If True
3. Assign TotalPrice = Row(“TotalPrice”).ToString
4. Assign TotalPrice = TotalPrice.remove(TotalPrice.indexOf((“$”), TotalPrice.indexOf(“$”) + 1)).ToString

Issue
My result for the price $155.3377 $1553377 returns the same value $155.3377 $1553377

Can someone help me return just $155.3377

Thank you!

[lakshman (Ganta lakshman)]

@MF.RPA

Try below expression.

           InputString = "$155.3377 $1553377"
           OutputString ="$"+InputString.Split("$"c)(1).Trim

[Yoichi]

Hi,

Another solution:

System.Text.RegularExpressions.Regex.Match(yourString,"\$[.\d]+").Value

Regards,

[MF.RPA (M.Farrell)]

@lakshman

"$"+InputString.Split("$"c)(1).Trim

Thank you for the idea but I am still getting the same result.

[MF.RPA (M.Farrell)]

@Yoichi

System.Text.RegularExpressions.Regex.Match(yourString,"\$[.\d]+").Value

Hi Yoichi,

Thank you for your idea but unfortunately, I am still getting the same result. I tried several examples.

I am now wondering if there is something wrong with my assign activity and IF activity.

[lakshman (Ganta lakshman)]

@MF.RPA

Can you please print the your input string before applying above string manipulation on it and check it once.

[gustavo.cervelin (Gustavo Cervelin Custódio)]

Hi @MF.RPA

Can’t you just split by blank space?

[MF.RPA (M.Farrell)]

It looks like the solution is working but after $ is removed, at the end I am printing the orginal value.

[MF.RPA (M.Farrell)]

@gustavo.cervelin

Hi gustavo,

Nice to hear from you!

Actually I cannot use the space as a delimiter since in some cases I could have spaces in between the numbers.

[gustavo.cervelin (Gustavo Cervelin Custódio)]

Hi Mitchell,

Hope you are doing well my friend

What about this?

[MF.RPA (M.Farrell)]

I assigned a new variable after the the REGEX method and I appear to be getting the appropriate result.

Now I need to figure out how to manipulate my new variable to clean up the rest of the formatting.

I will share my results as my investigation continues.

[MF.RPA (M.Farrell)]

@MF.RPA

the the REGEX method and I appear to be getting the appropriate result.

Now I need to figure out how to manipulate my new variable to clean up the

The regex activity does not work when I have a price such as this: $ 1 9 . 0170 $ 1 9 0170

Do you know how I could make regex work in this case?

[Yoichi]

Hi,

Can you try the following?

System.Text.RegularExpressions.Regex.Match(yourString,"\$(\s?[.\d])+").Value

Regards,

[MF.RPA (M.Farrell)]

@Yoichi

System.Text.RegularExpressions.Regex.Match(yourString,"\$(\s?[.\d])+").Value

Hi Yoshi,

Thank you for the quick reply.

This new method appears to work where the output is $ 1 9 . 0170

I have two additional questions

1. Could you recommend a way to remove the spaces in-between the numbers? I was not sure if I could use .replace(" “,”") with regex or not

2. Could you recommend a good resource to learn regex? It seems simple but I never really grasped it.

Thank you,

[MF.RPA (M.Farrell)]

I also noticed that I have another strange price format if you have any ideas

This price is coming across is a few different formats which makes it very tricky

$ 0 0000 . $ 0 . 0000
Or
$ 0 . 0000
or
$ 0 0000 .

The desired output would be $0.0000

[Yoichi]

Hi,

Do you need to get the data as it appears? If you can accept $0.0000 as $0 ,the following will work.

"$"+Double.Parse(System.Text.RegularExpressions.Regex.Match(yourString,"(?<=\$)[^$\r\n]+").Value.Replace(" ","")).ToString()

OR
If the number of decimal places is always four, the following might be better.

"$"+Double.Parse(System.Text.RegularExpressions.Regex.Match(yourString,"(?<=\$)[^$\r\n]+").Value.Replace(" ","")).ToString("0.0000")

Regards,

[MF.RPA (M.Farrell)]

@Yoichi

Does your first solution apply to a dynamic number of decimals?

My results can sometimes be .0 or .00 or .000 or .0000

First solution proposed:

"$"+Double.Parse(System.Text.RegularExpressions.Regex.Match(yourString,"(?<=\$)[^$\r\n]+").Value.Replace(" ","")).ToString()

[Yoichi]

Hi,

If there is $ sign before the above string, it will work as the following.

Regards,