How do I pull just the numeric characters in a cell?

[punter]

Hi,

I have some cells that I want to pull just the numeric value so I can do a Vlookup off the number. Example: 12345678DM. In the cell next to it I would like to pull just the number while leaving the DM off. I can't always promise that the amount of numbers will be 8 so I can't just do a =left(A1,8) or something. Any ideas?

Thanks as always,

Eddie

[Ragdyer]

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"punter" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have some cells that I want to pull just the numeric value so I can
> do a Vlookup off the number. Example: 12345678DM. In the cell next
> to it I would like to pull just the number while leaving the DM off. I
> can't always promise that the amount of numbers will be 8 so I can't
> just do a =left(A1,8) or something. Any ideas?
>
> Thanks as always,
>
> Eddie
>
>
> --
> punter
>
>
> ------------------------------------------------------------------------
> punter's Profile:
> http://www.excelforum.com/member.php...fo&userid=2044
> View this thread: http://www.excelforum.com/showthread...hreadid=567213
>

[ImpulseBlue]

you can still do the left if you know that there will always be 2
letters at the end:
=LEFT(F5,LEN(F5)-2)

Hope that helps

punter wrote:
> Hi,
>
> I have some cells that I want to pull just the numeric value so I can
> do a Vlookup off the number. Example: 12345678DM. In the cell next
> to it I would like to pull just the number while leaving the DM off. I
> can't always promise that the amount of numbers will be 8 so I can't
> just do a =left(A1,8) or something. Any ideas?
>
> Thanks as always,
>
> Eddie
>
>
> --
> punter
>
>
> ------------------------------------------------------------------------
> punter's Profile: http://www.excelforum.com/member.php...fo&userid=2044
> View this thread: http://www.excelforum.com/showthread...hreadid=567213

[oldchippy]

Hi,

I have some cells that I want to pull just the numeric value so I can do a Vlookup off the number. Example: 12345678DM. In the cell next to it I would like to pull just the number while leaving the DM off. I can't always promise that the amount of numbers will be 8 so I can't just do a =left(A1,8) or something. Any ideas?

Thanks as always,

Eddie

Hi Eddie,

Try something like this,

=LEFT(A1,FIND("DM",A1)-1)*1

oldchippy

[punter]

Thanks everyone. They all work. You are the greatest.

Eddie.