Excel check first 4 numbers

Build Activities
#1

I have a excel sheet, I need to use a if statement and write range activity.
If the first 4 numbers in the Number column are greater then 999 and less then 2000 then write general in the fund column. I have attached a excel sheet as a example. The numbers are formatted like this. 1234.654.564.381011.000.000. I only have to check the first 4 numbers so in the example just check to see if 1234 is great then 999 or less then 2000 if that is true then write general in the fund column. Please see attached sheet.Book1.xlsx (9.9 KB) Thanks for the help

#2

Hi @NATHAN_MORA

What u mean by general here

Can u elaborate it ?

#3

If the first 4 numbers in the number column are greater then 999 and less then 200 then write general in the fund column

#4

In the excel sample the first number is 4004.000.000.381011.000.00000. 4004 is greater then 2000 so nothing would happen. the second number in the number column of the excel sheet is 1000.000.000.311010.000.00000, 1000 is greater then 999 and less then 2000 so General should be written in the fund column.

#5

Any Ideas?

#6

User Read Range to read the Excel file into a datatable. Use For Each Row to loop through all the rows. In the loop you can read and convert the first four digits like this:

FourDigits = CInt(row("Number").ToString.Split("."c)(0))

Use If to compare it and then you can write to the “Fund” column like this:

row("Fund") = "General"

Use a Write Range after the For loop to save the changes back to your Excel file. Make that you have checked the AddHeaders property.

image

#7

Where do i write this code “CInt(row(“Number”).ToString.Split(”.“c)(0))”

#8

In the For Each Row loop:
image

#9

flow
flow711×614 33.3 KB
this is where im at, dt1 is the datatable from the read range

#10

You need to create a variable of type Int32 and then use Assign with the CInt code to convert it to a number. Then you can compare it in the If activity like this:

image

#11

Thanks Brotha Nice work

#12

You are welcome!

#13

What if i wanted to check the next column of number? For example 2274.000.000.343130.000.50000. what if i wanted to check the number 343130 in the number instead of the first 4 we did earlier?

#14

What if i wanted to check the next column of number? For example 2274.000.000.343130.000.50000. what if i wanted to check the number 343130 in the number instead of the first 4 we did earlier?

#15

Just use CInt(row("Number").ToString.Split("."c)(3)) instead. The code split at the periods, so it looks like this after the split:

Index: 0, Value: 2274
Index: 1, Value: 000
Index: 2, Value: 000
Index: 3, Value: 343130
Index: 4, Value: 000
Index: 5, Value: 50000

CInt(row("Number").ToString.Split("."c)(Index))
1 Like
#16

You can use simply a method split like this say str1 is a variable contains value:
2234.4345.3232.345345

str1.split(".“C)(0) - This will give 2234
str1.split(”.“C)(1) - This will give 4345
str1.split(”.“C)(2) - This will give 3232
str1.split(”."C)(3) - This will give 345345

Once value is extracted from string convert to integer to check whether value lies in range or not.

1 Like
#17

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