How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask John D Your Own Question
John D
John D, Engineering Consultant
Category: Networking
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
Type Your Networking Question Here...
John D is online now
A new question is answered every 9 seconds

EXCEL 2003 How do I remove unwanted information in a cell

This answer was rated:

EXCEL 2003: How do I remove unwanted information in a cell?

Example: APN: 124-29-111-019 OR 124-29-111-019


I think it is a function of =SUBSTITUTE but I can't get it to work





Hope this is ok Please feel free to reply if you need further clarification or click Accept if this answers your question fully.


John D, Engineering Consultant
Category: Networking
Satisfied Customers: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Networking Specialists are ready to help you
Customer: replied 6 years ago.

After I enter the string this pops up in the cell, not the number I desire.


which cell has the number that you want to remove the dashes from?


Customer: replied 6 years ago.
APN: 124-21-314-113



I need all the cells to look like this: XXXXXXXXXXX /p>


No spaces or other text in the box

No, I want the cell reference , e,g cell A2 or B5 etc



Customer: replied 6 years ago.

John, these are Parcel numbers for Houses.


I receive Foreclosure reports, Slow Pay reports, Tax reports in various ways as example:


APN: 124-21-411-072 and 124-23-566-908. I would like them to all appear like this:




With this format, I can sort and delete duplicates easier.... I do(NNN) NNN-NNNNrecords at a time so I need a way that I can make one pass at all the incorrect format at once

Customer: replied 6 years ago.

John, all the records I receive have the Parcel number first in the A box.



124-29-512-0812304 DIXON SPRINGS AV89031
124-29-514-0216204 WICHITA FALLS ST89031
124-30-210-1296040 SEA CLIFF COVE ST89031
124-30-611-0544006 RAIN FLOWER LN89031
APN: 124-21-310-0206464 Chebec St
APN: 124-21-310-0442818 Disk Ave
APN: 124-21-313-0112116 Mistle Thrush Dr

I understand that, but you are not telling me in which cell these parcel numbers are (e.g. cell A2, cell C3, etc) so that I can adapt the formula for these cells


Ok, here is a sample file with the formulas in the yellow cell



John D, Engineering Consultant
Category: Networking
Satisfied Customers: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Networking Specialists are ready to help you
Customer: replied 6 years ago.

They start at A1 and continure A500 etc.


A1 B1 C1 D1

124-29-214-0163604 MASTERCRAFT AV89031ALLEN MANOR 224C3
124-29-512-0812304 DIXON SPRINGS AV89031GRANADA RIDGE 224D3
124-30-611-0544006 RAIN FLOWER LN89031HIGHLAND HILLS UNIT 124B3
APN: 124-21-310-0206464 Chebec StSharron Bolin9Y
APN: 124-21-310-0442818 Disk AveChing-Shau Liu AND XXXXX XXXXXu8
APN: 124-21-313-0112116 Mistle Thrush DrJared Jacoby AND Jared Jacoby8Y

Ok, I will set up again the formulas for these cells. But I need to know whether the solution that I gave you in my last file worked or not?



Customer: replied 6 years ago.


Yes it works and work be okay

Customer: replied 6 years ago.
How do I set up for making those changes as you did?

One moment I will apply it to your data, but in the meantime if the solution in the sample file worked please remove the neutral feedback that you left me. To do that send an email to [email protected] (with reference to this question) asking them to delete the feedback. Let me know


Or would you like to send me your file so I can set it up on or directly?






Edited by John D on 12/15/2010 at 4:58 PM EST
Customer: replied 6 years ago.
<p>Relist: Incomplete answer. Lets try again...with the same problem</p><p>I have another question?</p><p>Why did you bill me twice?</p><p>I don't mind paying but I ask one question and have not received a satisfactory answer!! Please credit my account for one over charge immediately.</p><p>Now, I can send a file if that will help and you can check it out. </p><p> </p>

Here is how to send the file:


Go to and upload the file there (no need to sign up). You will then get a page that has the File ID. Copy the File ID and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it, or just try replacing the sensitive data with dummy values.


If there is a problem with the wikisend site you can try uploading the file here instead:


Customer: replied 6 years ago.
Relist: Incomplete answer.

Hi. John D. provided the answer to you. You can paste the following link to Cell B1. It will convert the Data in Cell A1 to the format you want. Once you paste the formula to

Cell B1, Copy Cell B1 (Right Clich Cell B1 and select "Copy") and paste to CELLS B2 through B500. This will convert all the numbers in CELLS A1 through A500 to the format you are looking for.


Copy and Paste this formula to CELL B1 (Or any other Cell you want to see A! Converted to);




I attached a sample spreadsheet;

Sample Spreadsheet

Edited by J. Mar on 12/17/2010 at 7:41 PM EST
Customer: replied 6 years ago.

I finally figured it out myself.... Since I was double billed, would somebody issue me credit immediately. I will use you again but you need some organizational help.


Call me at: XXX-XXX-XXXX if you have any questions with the overcharge.

I put in a request for a refund. No need to reply back to this post