How do you remove numbers and letters from a large number of cells in one column?

Hey everyone,

I was wondering if anyone knows how to remove numbers and letters from a column of cells.

I got about 4000 cells that have a prefix of a seven digit number followed by the letters “LPH” then a space followed by an eight digit number after that.

Example:
1000100LPH 00005555
1000101LPH 00005556
1000103LPH 00005557
1000104LPH 00005558

All I need to stay in the cells are the eight digit number after the “LPH.” The seven digits number and the letters are of no use and I need to delete them all. Is there a way to delete all those unwanted digits and letters without having to do it manually, one by one?
I’m currently working on Excel2007 and I’m definitely a noob to this so I won’t be offended if you explain it to me as if I were a third grader.

Any help would be greatly appreciated.

Thank you.

Assume that your data is in A1. In B1, type this formula and copy it down. =Right(A1,8)

Alan

Edit: Very fast Moo!

Originally Posted by Moo the Dog

If your examples start in cell A1, you could add a formula in B1 such as:

Formula:

Please Login or Register  to view this content.

then fill that formula down to the end of your list.

- Moo

Originally Posted by alansidman

Assume that your data is in A1. In B1, type this formula and copy it down. =Right(A1,8)

Alan

Edit: Very fast Moo!

Oh wow, that was fast. It worked perfectly.

Thank you guys for the information. Much appreciated!

One more noob question. On the same example I posted earlier how do you separate the two numbers into separate cells? I need to be able to sort via the last 8 digits but having both in separate columns would help me find duplicates and inconsistencies versus a client provided spread sheet.

Thanks in advance! :-)

Originally Posted by Moo the Dog

Well, if you wanted the left half of the example to be in one cell, you could use: =LEFT(A1,10) in cell B1, then use =RIGHT(A1,8) in C1

That would give you
A1: 1000100LPH 00005555
B1: 1000100LPH
C1: 00005555

If you only want the first 7 numbers from the left, leaving off the LPH, then use =LEFT(A1,7)

- Moo

Ahhh I see. Thanks again Moo, It worked perfectly.

Cheers!

Originally Posted by Moo the Dog

You're welcome, Dark. Be sure to mark the thread as [SOLVED] if you feel the solutions given are what you needed. You can do so by clicking on 'Thread Tools' just above your first post in this thread, and selecting 'Mark Thread as Solved'.

Thanks,
Moo

Will do, Moo. Thanks again, much appreciated.

Dark