Hope this is ok Please feel free to reply if you need further clarification or click Accept if this answers your question fully.
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?
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
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
John, all the records I receive have the Parcel number first in the A box.
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
They start at A1 and continure A500 etc.
A1 B1 C1 D1
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?
Yes it works and work be okay
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 firstname.lastname@example.org (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?
Here is how to send the file:
Go to www.wikisend.com 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:http://www.sendspace.com/
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;
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.