How can split number

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

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

try this

Tested Result
https://regex101.com/r/NnYJLY/1

Cheers
@coder

1 Like

thanks for your reply

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

1 Like

so this part "$55.99$55.99 " from Excel

yes it is from excel or queue it is same

1 Like

only for test i put that in excel

1 Like
  • 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

Thanks i will try now

1 Like

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

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

This one

Main.xaml (7.7 KB)

or can you please check in my workflow

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

Hi @coder,

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

Untitled.xls (13.9 KB)

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

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

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

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

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

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

Thank you for you i will try that