How to trim prefix in goggle sheets using various conditions-CodePudding

I have data as follows in excel/google sheets. Numbers that have a length of 19 characters need to be manipulated in this way For all strings with a length of 19 last 6 digits need to be trimmed, ( i can easily do it ) and remove the leading prefix which is either 200 or 20000

for example 2005507187528000001 to 5507187528 | 2000017303364000001 to 17303364

Have no idea what to do to remove the prefix, I tried trimming the last 14 digits to get 20000 or 20055 and using this to determine if I need to take out the first 3 or first 6, but no success.

Please help !!!

thanks

CodePudding user response：

try:

=INDEX(IFERROR(REGEXEXTRACT(A2:A&""; ".{6}$")))

update:

=REGEXEXTRACT(F905&""; "^20 (\d.*)\d{6}")

CodePudding user response：

If I understood your question correctly you want to remove the first N characters whether it is 200 or 20000.

Try:

=IF(LEFT(A2,5)="20000",RIGHT(A2,LEN(A2)-5),RIGHT(A2,LEN(A2)-3))

Drag down to column.

Result:

Explanation:

Using the LEFT() function you can extract the first 5 characters. You can then use an IF() to check if it is equal to 20000. Then using the Combination of RIGHT() and LEN() to remove the first N characters. If it is equal to 20000 remove the first 5 characters, if not then remove the first 3 characters.

Using an ArrayFormula:

=ARRAYFORMULA(IF(A2:A="","",IF(LEFT(A2:A,5)="20000",RIGHT(A2:A,LEN(A2:A)-5),RIGHT(A2:A,LEN(A2:A)-3))))

Here's a way using arrayformula so you don't have to drag down/copy to cells below. This of course still needs to be adjusted to your range.

Note: I have not included the formula to remove the last 6 characters since according to you you already have this, so you can just add this formula to yours.

For all strings with a length of 19 last 6 digits need to be trimmed, ( i can easily do it )

References:

Remove the First N Characters in a Cell in Google Sheets - Multiple ways to remove the first N characters, refer to this link.
LEFT()
IF()