Removing two letters from a string of letters and numbers

i have a list of 2000 fields which have the same format IE "AB10014"

I need to remove the "AB" from every field and leave the #.

Besides putting a space and running text to columns I'm not sure how. Thanks for your help.

Or even:

=MID(A1,3,255)

Note that the string formulae in posts 3 to 6 (and this one) will return a text value that looks like a number. If you want the answer as a proper number then you can add zero to the formula (+0) or multiply by 1 (*1) or use a double minus in front of the formula (--). Leading zeros will be removed, as Paul pointed out.

Hope this helps.

Pete

If you want the output to be a number:

=1*RIGHT(A1,LEN(A1)-2)

Remove characters from text
Let me clear it with an example
Let's say you want to split a text column of phone numbers formatted as 999-999-9999 into three columns of numbers: area code, prefix, and number. There are several ways to remove characters from text.

What do you want to do?
Remove a specified number of characters from the right or left side of text
Remove a text string from a worksheet by using the Replace command
Remove a specified number of characters from the right or left side of text
To do this task, use the LEN, LEFT, and RIGHT functions.

Example
The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example


1
2
3
A
Data
Vitamin A
Vitamin B1
Formula Description (Result)
=LEFT(A2, LEN(A2)-2) Removes last two characters from contents of A2 (Vitamin)
=RIGHT(A3, LEN(A3)-8) Removes first 8 characters from A3 (B1)
Function details
LEN, LENB

LEFT, LEFTB

RIGHT, RIGHTB

Top of Page TOP OF PAGE

Remove a text string from a worksheet by using the Replace command
Select the range of cells that you want to search.
If you want to search the entire worksheet, click any cell in the worksheet.

On the Home tab, in the Editing group, click Find & Replace, and then click Replace.
In the Find what box, enter the text or numbers that you want to search for or choose a recent search from the Find what drop-down box.
NOTE You can use wildcard characters in your search criteria.

If you want to specify a format for your search, click Format and make your selections in the Find Format dialog box.
Click Options to further define your search. For example, you can search for all of the cells that contain the same kind of data, such as formulas.
In the Within box, you can select Home or Workbook to search a worksheet or an entire workbook.

In the Replace with box, enter the replacement characters and specific formats if necessary.
If you want to delete the characters in the Find what box, leave the Replace with box blank.

Click Find Next.
To replace the highlighted occurrence or all occurrences of the found characters, click Replace or Replace All.
NOTE To cancel a search in progress, press ESC.

you all are the best! Thanks you saved me a big headache here...just starting back up using excel more commonly. I tried several to check em out. Appreciate it.

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete