i need to remove the "zero"s from cell

[bkcffee]

like these cells
00012345-A
0012345-A
00000012345-A

i think left/right function with len mybe can sovle it?
but idk how to do it :D

anyhelp ?

thanks in advance

[cytop]

Substitute function, maybe

=SUBSTITUTE(D11, "0", "")

[sandy666]

I assume your data start from A1, so try

Please Login or Register  to view this content.

with Control+Shift+Enter

cytop's solution is shorter, normal (non-array) so use it

[bkcffee]

thank you
it worked perfectly !!

[bkcffee]

I assume your data start from A1, so try

Please Login or Register  to view this content.

with Control+Shift+Enter

cytop's solution is shorter, normal (non-array) so use it

O.o
wow... keep it simple man :P

no my data start with zeros and end with -letter :x

[bkcffee]

oh no
i had another preblem
Substitute function removed all the zeros in the cell
example
000000590-A end up being like 59-A and it should be 590-A
004104599-A 414599-A shold be 4104599-A

i only need to remove the zeros in the begining !!

[cytop]

Slightly more complicated...

=SUBSTITUTE(TRIM(SUBSTITUTE(D11,"0"," "))," ","0")

[sandy666]

Use what you want
with my formula result is:

Please Login or Register  to view this content.

and this is simple smile3_e0.gif

try with 000123000005670000-C

[bkcffee]

im sorry
i tried urs too
but it gave me #value error :/

i copy and past

i'll try to put it in attachment

[bkcffee]

Substitute function, maybe

=SUBSTITUTE(D11, "0", "")

it gave me blank cells :x

[sandy666]

im sorry
i tried urs too
but it gave me #value error :/

i copy and past

i'll try to put it in attachment

I said like below

Please Login or Register  to view this content.

with Control+Shift+Enter

[bkcffee]

WTF !!!
nvr used ctlr shift enter with formulas before

and it worked
it added {formula} :D

thank you

[sandy666]

If the basic problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks

[Manikandan Arumugam]

bkcfee,

=MID(A2,FIND(LEFT(SUBSTITUTE(A2&" ","0",""),1),A2),LEN(A2))

[Phuocam]

One way ...

=--LEFT(A1,LEN(A1)-2)&RIGHT(A1,2)

[AlKey]

and another

Please Login or Register  to view this content.

v	A	B
1	00012345-A	12345-A
2	0012345-A	12345-A
3	00000012345-A	12345-A
4	000000590-A	590-A
5	004104599-A	4104599-A

[bkcffee]

and another

Please Login or Register  to view this content.

v	A	B
1	00012345-A	12345-A
2	0012345-A	12345-A
3	00000012345-A	12345-A
4	000000590-A	590-A
5	004104599-A	4104599-A

care to explain plz?
keep in mind that my cells end up with -A -B etc....

[bkcffee]

bkcfee,

=MID(A2,FIND(LEFT(SUBSTITUTE(A2&" ","0",""),1),A2),LEN(A2))

One way ...

=--LEFT(A1,LEN(A1)-2)&RIGHT(A1,2)

ty guys <3