Take Certain Text In Cell

Hi all,

Hope you are doing good.
Here I am again :slight_smile:

My attention is to create running number.
Eg : GE-2103-1000-Rev.0

User will enter the date and other details and running number will create by his own.
From the date, 21 in the beginning refer to the year.
03 after refer to the month from the date entered.
1000 will add 1 in the next after. (1001,1002,1003 etc)
0 at the last will change/update to 1 or 2 or 3 when they edit(make some revise).

My question is there any ways to take certain text from one cell?

1 Like

Hi Biha, As I understand, you would like the running number to be generated from the details entered by the user, you can use the template column to create a new value from values in other cells.

But how to take for example the number 21 only for the year 2021 and 03 for the month of march?

  1. To get 21 out of 2021, one way is to use math:
    Create another column "Split Year"which subtracts 2000 from year column (2021) to get 21,
  2. To get 03, you may create another column “Month Number” and use if-then-else to create number out of Month name “March”. For example: If Month = March, then value of Month Number is equal to 3 and so on.

Good idea!
Do you have any idea on how to do running on 1000 .
It will be cumulative column :

thanks for replying. It same idea with @vijay .
Do you know how to make the number cumulative as go down?

Please refer to the solution in the link that I gave you, as that’s exactly what it does.

1 Like

For the date in yymm format

(YEAR (D) -2000) * 100 + MONTH (D)

where D is the date or Now

1 Like

And for 1000 … 1001

Math column

1000 + X

where X is your base number

In this case, are you trying to generate the next row value in a column from the previous row value. Meaning, if ID is 1000 in the ID column in row 20, then in row 21, the ID column should have value 1001, right? Darren’s and Manu’s solution will generate in the same row, but to generate in the next row will require a different solution.

ah for an ID + 1

Add a Rollup / Max column with your ID as reference, then use the math formula 1000 + X to format your number

My solution provides an auto-incrementing column.
So every time a new row is added, the number in that column will auto-increment by 1.
Is that not what he is looking for?

It has to percolate to the next row. Can the next row have the auto incremented value in the same column? I guess that is what he is looking for. For example, the user need not enter the ID again for the next row. It will be autogenerated. In your solution, it is happening in the same row. How would that be used for the next row entry?

there is already the solution in the subject of @Darren_Murphy
If you read the whole thread, you have the solution to increment an ID then format it

It is looks like this.
but where is the ID? hmm sorry dont get it.

For those who are reading this topic, here is the solutions.
Applying both.