How do I add dashes in apartment addresses?

[SuzieQ333]

I have a list in which some apartment addresses are formatted as B103 and some as B-103. How do I tell the list to add a dash every time there is a letter, directly followed by a number?

[Søren Larsen]

Assuming your list starts in A2, then you can put the following in, e.g., B2:

Please Login or Register  to view this content.

And copy down.

[K m]

Assuming Cell A1 is the raw data, try this:

=IF(ISERR(FIND("-",A1)),LEFT(A1,1)&"-"&RIGHT(A1,LEN(A1)-1),A1)

[SuzieQ333]

Hi Søren,
Thanks for your reply. Unfortunately I'm a newbie and don't totally understand your answer. This is a voter list with lots of fields and the addresses are all in one field, so it isn't "B" in A2 and "103" in B2. They are both in the same field. How do I handle this?
Thank you.

[Søren Larsen]

The A2 refers to the field where your address is placed. The B2 is just an example of where you can put the formula; you can put whereever you want, as long as it refers to your address field. Did that help or add to the confusion?

[SuzieQ333]

OK, I added a column after the address column. What do I do next? Remember, I'm a newbie and need step-by-step instructions.
Thank you.

[martindwilson]

is it always b?

[SuzieQ333]

No, these are real apartment buildings in my district. They are whatever number the apartment complex has: A-102; B-209 etc. The problem is that when some people register to vote, they write: "C201" and some people write "C-201." I want to standardize them to all "C-201."

[Leith Ross]

Hello SuzieQ333,

Welcome to the Forum!

To provide you with a working solution, it would be best if you can provide of samples of the data. Of course, uploading the workbook would be the best option. However you decide, please redact any sensitive information before you post it.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[martindwilson]

try this see attached

Please Login or Register  to view this content.

[SuzieQ333]

I have attached a snippet. As you can see, with these apartments, there is an added issue of "#". I am walking door-to-door, so I need all of the apartment numbers to be sequential, so I don't have to physically walk around the complex twice. Can you make a formula tfor this?

Thank you1

[martindwilson]

Please Login or Register  to view this content.

then sort by that column like this
mind you this is getting large it would probably be better to spilt the formula to two columns

[Leith Ross]

Hello SuzieQ333,

I have added a button to your workbook to run the "normalize" macro. This will format all the data in column "A" that is an apartment address. Once the addresses have been modified, they are sorted in ascending order. If you need help moving this over to your actual workbook, let me know.

Please Login or Register  to view this content.

EDIT: After noticing you are using a Mac, this code will probably not work with your OS. One of the big compatibility drawbacks is Mac can not call these Scripting object libraries like a PC. A workaround using Apple Script would be possible but difficult because UNIX Regular Expression syntax is very different and the remaining macro code would have to be translated as well.