How to get only the number?

Help
1

Hi,
It might be an easy question but I could’nt find the solution.
I have data in a column of an excel which is like; “120 USD”. How could I get the number without the currency? I want to subtract two numbers by that.

Thanks,
Aysegul

1 Like
2

@aysyavuz
Get the data in a string variable by reading the column or cell and then you can split the string by the space and take the first item: numberOnly = "120 USD".Split(" "c).First will assign "120" to numberOnly.

3

Fine
–use excel application scope and pass the file path as input
–use read range activity and get the output with a variable of type datatable named outdt
–use a for each row loop and pass the above variable asinput
–inside this use a assign activity like this
out_value = Split(row(“yourcolumnname”).ToString," ")(0).ToString
where out_value is a variable of type string

simple isn’t it
Cheers @aysyavuz

4

@aysyavuz

If data is in same format split it based on the space like below
Input = “120 USD”
Array1[. ] = input. Split(cchar(" "))
Intvalue = Array1[0]

Regards
Ajay

5

Hi,

Be aware that with the proposed solution, you might face an issue depending on your number format.
For example, if your cell is 1 200 USD, by splitting on " " and taking the first element, it would give you “1” instead of “1200”.

If your data is always having the 3 letter currency at the end, you can also consider removing the last 4 character of the cell (being " XXX")

Regards.

1 Like
6

I would say it would be easy if you make macro on Excel to remove USD from Excel .

Just a different approach.

Regards,
Ahtesham

7

  1. Use matches activity the input to it will be row.item(“column_Name”).ToString
    The pattern will be “(\d)+”
    Set the out put variable say out_Match

  2. the use assign activity
    req_Num = String.Join(“,”, out_Match)

8

Hi all,

Thank you for your answers. I tried to do that with Assign activity - replacing USD with “”. It works!

Thanks,
Aysegul

2 Likes
9

Great
Cheers @aysyavuz

10

Hey @ashley11 I read your comments on this query and found helpful.

can you help me on below very similar query on my Project?