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 6 other Networking Specialists are ready to help you
Customer: replied 5 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 5 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 5 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 5 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 6 other Networking Specialists are ready to help you
Customer: replied 5 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 5 years ago.


Yes it works and work be okay

Customer: replied 5 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 5 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 5 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 5 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

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
< Previous | Next >
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY

Meet The Experts:

  • Claws224


    IEEE Network Engineer

    Satisfied Customers:

    IEEE, Microsoft
< Last | Next >
  • Claws224's Avatar


    IEEE Network Engineer

    Satisfied Customers:

    IEEE, Microsoft
  • Kevin's Avatar


    LAN/WAN Specialist

    Satisfied Customers:

    Certified MCSA and MCP Network Administrator with over 20 years of PC experience.
  • Eric K.'s Avatar

    Eric K.

    Network Administrator

    Satisfied Customers:

    13 years of desktop, server and network support experience
  • Mr. Clark's Avatar

    Mr. Clark

    Network Engineer

    Satisfied Customers:

    Computer Business Owner / Former DoD Network Specialist
  • Byron's Avatar


    PC Support Tech

    Satisfied Customers:

    CCNA, A+, MCSE 11 years at a large law firm.
  • Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    Certified Networking expert with over 10 years experience.
  • A. Stevens's Avatar

    A. Stevens

    Network Administrator

    Satisfied Customers:

    10+ years of Desktop, Laptop, and Server troubleshooting and support.