Hi, my user add a string of 47 numbers reading from codebar. I have to follow a layout and take parts of this string to get the type of information, for instance, the first 3 numbers.
From the 4th to 10th get the amount, from the Xth to Xth character take a date.
What type of column can I use to get these three information and write each one in a column?
Tkx
I think you need to bring that back to the sheet and use Left, Mid & Right functions to separate the string to what you want. It would be the easiest way.
Tkx so much for your prompt reply. I´ve tried it using arrayformula, but it didnt work.
It means that we someone add a new row I have to drag down the formula.
Tkx again!
Can you share a sheet with sample data and desired outcome? I will try to have a go for you later when I have time.
Thx for your kindness https://bit.ly/2WOLCz4
Can you specify which part of the string corresponds to which information, I will try to extract them one by one.
the two importants are the first digit should be 8 and the middle when I use this formula value(MID(A3:A;5;7)&mid(A3:A;13;2)&","&mid(A3:A;15;2)))
So let’s say from a string like this.
846800000014329502962020006100380002002643171602
You want to get 3 fields:
Is it correct?
The other digits tell me other info. But yes, the 2 main information are amount and type of record.
In this case, I need to check if this sequence starts with 8 and another check from 5th take seven digits, after from 13th take 4 digits.
in your example 846800000014329502962020006100380002002643171602
8 as first digit OK
the amount will be 132,95
Man, with your valuable tip about trigger, I’m gonna try to write a script to review this string after user save. right?
I’m gonna write a formula for you tomorrow when I got home. Away from the computer today 
Tkx again! Of course we have many ways to solve and my easier way I copy the formula of the cell above. So I set the Google Sheet formula using MID, Left and now when user add another row I just copy the formula above.
Feel free for sharing another better way (of course).
But your help was amazing!
Oh My! Yesterday everything run fine! Today I just add to “onchange function” the script to copy the formula and nothing happen. I guess my trigger stopped. any tip?
I enabled notification I’m analyzing what´s wrong.
Now I’m receving…
|7/26/20 10:54 PM|onChange|Exception: Cell reference out of range|change|7/26/20 10:54 PM|
YES!! After hours studying, reading StackOverFlow, watching YT videos I got it!
Let´s go to another challenges.
Just for records
var lastRow = ss.getLastRow();
var lastColumn = ss.getLastColumn();
Logger.log(lastRow);
Logger.log(lastColumn);
//var lastCell = e.source.getActiveSheet().getRange(lastRow, lastColumn);
var LinhaDigitavel = ss.getRange(lastRow,1).getValue();
Logger.log(LinhaDigitavel);
// valida primeiro caracter da LinhaDigitavel
ss.getRange(lastRow,5).setValue(LinhaDigitavel[0]);
// copia a fórmula do valor da célula anterior
//UltimaFormula = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(lastRow-1,4).getFormula();
CelulaDestino = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(lastRow,4);
Logger.log(CelulaDestino);
ss.getRange(lastRow-1,4).copyTo(CelulaDestino);
//ss.getRange(lastRow,4).setValue(UltimaFormula).setNumberFormat(“Currency”);
}
Hi Alex,
I have made a sample sheet for you: https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1520352505
Can you check if it’s what you want? Thank you.
You can use the methods I listed here ot copy down the formulas, but only use it when arrayformula can’t work for your case.