Formatting S/NO String in excel

Hello everyone,

image
I am trying to correctly build a S/NO counter in an excel table. However, the issue i am facing is regarding how the value jumps from 2 to 4 as shown in the screenshot. This is due to the way it is currently coded by retrieving the number of rows in the excel and ( row.count ) and using that as my starting variable.

Would like to know if there is a simple and quick way to store the last cell in column A and trim the variable to only the numbers before the decimal?

E.g. 2.2 = 2 , 8.2 = 8 , 16.1 = 16.

Many thanks!

There isn’t only one way to do this.

One option could be:

  1. Use read range to get the whole table
  2. Use an assign to retrieve the value of the last row. It would look something like this: dtVariable(dtVariable.Rows.Count-1)("S/N").ToString

The first part indicates to use the last row, the β€œ-1” is because rows count starts at 1 but rows index start at 0. Then indicate the column name and cast it as a String (if you need to)

Hi @nerlichman,

Thanks for the prompt reply. I roughly understand what u mean. However, the screenshot provided is not all. The .1 value will continue meaning it can also reach 4.9 or 4.10 .

Sorry, I forgot the last part of the assign to extract the number.

For readability do it in two assigns, the one from the previous point suppose stored in a variable stringVar. Then do another assign to the same variable like this: stringVar.Split(".")(0)

It will create an array with two entries the part before the decimal (.) and the part after, then the (0) is where you define to get only the part before the decimal separator

Hi @nerlichman,

Can u elaborate more on how the (0) is supposed to be defined to only retrieve parts before the decimal?
E.g. What does the (0) really mean?

Also, how will it be affected if the value before the decimal is > 1 digit? E.g. 100

When you do a Split what it means is that it will β€œchop” the string by the part you define (in this case is β€œ.”) and it will store each part (ordered) in an array.
For your examples:

  • For 2.2: it will return this array {β€œ2”, β€œ2”}.
  • For 8.2: {β€œ8”, β€œ2”}
  • For 16.1: {β€œ16”, β€œ1”}

Then you must get the position you want, in this case since you want everything that is before the β€œ.” you need to get the first position in the array. To access the value of a particular position of an array variable you need to indicate the position between parenthesis, and since arrays (like rows) have their index starting at 0, to get the first position you need to indicate β€œarrayVariable(0)”.

I hope this helps you understand.
Cheers!

1 Like

Hi @nerlichman,

Thank you so much! This is exactly the explanation I was looking for. Will test it and see how it works. Really appreciate it.

Cheers!

1 Like

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