How can split number

Learn
1

Hi all ,

Conversion from string "$55.99$55.99 " to type 'Integer' is not valid.

i have scripting this price from website to excel but only i need the first number how i can get that ?

thank you

1 Like
2

(?<=\p{Sc}).*(?=\p{Sc})

try this

Tested Result

Cheers
@coder

1 Like
3

thanks for your reply

sorry i do not understand i want do that in if condition to check that for all row

1 Like
4

so this part "$55.99$55.99 " from Excel

5

yes it is from excel or queue it is same

1 Like
6

only for test i put that in excel

1 Like
7

  • you can use Matches Activity and pass Input as row(“yourColumnName”).ToString and then use this Pattern (?<=\p{Sc}).*(?=\p{Sc}) and get a output as outRegex

  • now use assign activity like this intVar =Cint(outRegex(0).ToString)

2 Likes
8

Thanks i will try now

1 Like
9

Sir if it is possible can do that in workflow because i get error

10

Matches: parsing “?<=\p{Sc}).*(?=\p{Sc}” - Quantifier {x,y} following nothing.

This one

11

Main.xaml (7.7 KB)

or can you please check in my workflow

13

Main.xaml (7.7 KB) Untitled.xls (13.9 KB)

14

Hi @coder,

Are the two numbers always the same? $55.99$55.99 = 2x $55.99

15

Untitled.xls (13.9 KB)

Can you please see the excel file , only i need this number 55.99 one

16

Okay, I took a look at your file and saw that all numbers always have two decimals.

input = "$55.99$55.99 "
output = CDec(Replace(Left(input, InStr(input, ".") + 2).Trim, "$", ""))

This will look for the first “.” to find the position, then plus two decimals would be the end of the first number. Then I removed the “$” using the replace method and then the whole thing is converted to a Decimal Type. If you were to convert it to an Integer, you would lose the Decimal data.

I hope this helps

17

Thank you , but we’re I can write this? In the Matches activitu?

18

Hi, The matches activity, which @Pradeep_Shiv is talking about, is regular expressions.

I am not using regular expressions or the Match activity.

How you could use this is by getting the value from your workbook cell. Then you have the input value as string.

Then you you use an assing activity to extract the value from the string and assing it to an decimal variable.

This value can be written back into your file if you want to. Build in a loop that does this for every row in your file.

1 Like
19

I will try to that thank you ; if it possible attach me an example I would understand well

20

Right now I dont have that much time, if you only wish to clean up your file you could try using VBA. His is a quick VBA script that would clean you file:

Option Explicit
Sub GetFirstNumber()

'declare variables
Dim lastRow As Integer
Dim counter As Integer
Dim outputValue As Double
Dim inputValue As String

'get last row
lastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row

'set first row
counter = 2

'loop through list and clean up
Do Until counter = lastRow + 1
    'ignore if cell item is empty
    If ThisWorkbook.Sheets(1).Cells(counter, "B").Value <> "" Then
        'get value
        inputValue = ThisWorkbook.Sheets(1).Cells(counter, "B").Value
        'get cleaned output
        outputValue = CDbl(Trim(Replace(Left(inputValue, InStr(inputValue, ".") + 2), "$", "")))
        'write value back to cell
        ThisWorkbook.Sheets(1).Cells(counter, "B").Value = outputValue
        'set dollar currency format
        ThisWorkbook.Sheets(1).Cells(counter, "B").NumberFormat = "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
    End If
'increment counter
counter = counter + 1
Loop

End Sub
1 Like
21

Thank you for you i will try that