String Manipulation Problem - Remove data after the second character occurrence

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!

1 Like

@MF.RPA

Try below expression.

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

Hi,

Another solution:

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

Regards,

1 Like

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

Hi Yoichi,

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

image

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

@MF.RPA

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

1 Like

Hi @MF.RPA

Can’t you just split by blank space?

image

image

1 Like

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

@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.

1 Like

Hi Mitchell,

Hope you are doing well my friend :slight_smile:

What about this?

image

1 Like

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.

1 Like

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?

Hi,

Can you try the following?

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

Regards,

2 Likes

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,

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

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,

2 Likes

@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()

Hi,

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

img20220222-1

Regards,

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.