Removing leading zeros

[Jake_Naude]

I'm using substitute to strip enumlists that have this format...

AAA-BBB-CCC-020A

...down to the remaining number + letter like this...

020A

Is it possible to remove the leading zero, and not affect the zero on the number 20...?

20A

 Thanks in advance... 😉

[AleksiAlkio]

So you would like to have AAA-BBB-CCC-2OA as a result?

Is the format always like that? Is the leading zero always present? Can it be something else than a zero? Should itbe removed as well? To have the correct formula, you need to elaborate a little.

[Jake_Naude]

Hi @AleksiAlkio... Apologies...

The string has this format:

AAA-BBB-CCC-02OA, AAA-BBB-CCC-02OB, AAA-BBB-CCC-02OC

AAA-BBB-CCC-021A, AAA-BBB-CCC-021B, AAA-BBB-CCC-021C

The A's, B's, & C's are there for demo purposes...

I manage to substitute the AAA-BBB-CCC- with "" and end up with:

020A, 020B, 020C, 21A, 021B, 021C

I'd like to drop the leading zero and end up with:

20A, 20B, 20C, 21A, 21B, 21C

Is this possible...? 🤔

[AleksiAlkio]

Try with MID(INDEX(SPLIT([Code],"-"),4),2,4)

[Jake_Naude]

Thanks @AleksiAlkio

MID(INDEX(SPLIT([Code],"-"),4),2,4) works perfectly on two digit numbers.

AAA-BBB-CCC-020A results in 20A

AAA-BBB-CCC-120A also results in 20A

If it adjust it to MID(INDEX(SPLIT([Code],"-"),4),1,4)

AAA-BBB-CCC-120A also results in 120A but...

AAA-BBB-CCC-20A then results in 020A 

Please advise if it is possible to account for 3 digit numbers and still not have the leading zero...? The range I work with is  001A to 999A... Cheers... 🤔

[romulo_torres]

Hello!

how about:

IF(
LEFT(INDEX(SPLIT([Code], "-"), 4), 1) = "0",
MID(INDEX(SPLIT([Code], "-"), 4), 2),
INDEX(SPLIT([Code], "-"), 4)
)

[Jake_Naude]

Many thanks @romulo_torres ... This looks very promising... I've tried running it and I'm getting an error which I don't understand...

MID function is used incorrectly

Please could you advise...?

[AleksiAlkio]

With the 001A, you want to remove only the 1st zero?

[Jake_Naude]

I'd like to remove both zeros please

[AleksiAlkio]

LEFT([Code],12)&NUMBER(LEFT(INDEX(SPLIT([Code],"-"),4),3))&A should do the job.

[Jake_Naude]

Thanks @AleksiAlkio ... I'm sorry, I may have confused the issue.

My goal is to keep only the 4 right-most characters from the long string, and  then remove the leading zeros... 

The left part of the string is not important.

Here are three sample strings:

AAA-BBB-CCC-100A = 100A

AAA-BBB-CCC-010B = 10B

AAA-BBB-CCC-001C = 1C

I'm trying to take a number with a zero in front of it, and remove the zero.

Sorry if I over complicated the question?

[AleksiAlkio · Accepted solution]

Then use just a part from it like NUMBER(LEFT(INDEX(SPLIT([Code],"-"),4),3))&A

[Jake_Naude]

Many thanks @AleksiAlkio ... 😉

[AleksiAlkio]

You're welcome!

[Jake_Naude]

So... I'm still testing...

I have an enumlist (text) column called [lrn] which gives a result that looks like this:

I have another enumlist (text) column called [short-lrn] where I'm using this expression:

IFS(
STARTSWITH(TEXT([lrn]),"00"),RIGHT(TEXT([lrn]),2),
STARTSWITH(TEXT([lrn]),"0"),RIGHT(TEXT([lrn]),3),
TRUE,[lrn]
)

Instead of yielding a list result, it yields just the very last string in the list.

It results in 46C.

It has stripped away the leading zero, so I'm half way there... Just need the rest of the list now...

Any thoughts please... 🤔