I need to get large numbers within a string, no leading zeros

[pencil pusher]

Hi,

So I have a list of data that looks somewhat like this

ABC00004
ABC01085
ABC00334
ABC00100

I want it to look like this

4
1085
334
100

The ongoing theme is that there will always be 3 letters and then numbers after it.

I want to capture the number only, and with no leading zeros, but I can't figure out how.

As a starting point, I tried using this formula below, but it cuts data like ABC01085 down to 85 instead of 1085 and ABC00100 comes up blank. Any ideas on how to fix this to meet my needs is appreciated!

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

 

[mikerickson]

Try
=MID(A1, 4, 255)+0

 

[JoeMo]

Copy B1 down.

Excel Workbook
	A	B
1	ABC00004	4
2	ABC01085	1085
3	ABC00334	334
4	ABC00100	100
Sheet3

 

[pencil pusher]

I forgot to show the version of this formula that made for me...

it looks like this

=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"0","@",LEN(A1)-LEN(SUBSTITUTE(A1,"0","")))))

 

[pencil pusher]

Wow you guys are fast!!! both formulas worked! thanks for the help!

 

[Rick Rothstein]

...both formulas worked! thanks for the help!

You could also use this formula...

=0+REPLACE(A1,1,3,"")