need help changing format of every entry in a column

**BBLANCOBRNX** · 01-13-2012, 01:24 PM

Hello. I have a workbook with about 20,000 lines in it. In column I have unique 13 digit codes in this format: 1234567890123. I want the codes to be in this format: 1234-56-789-0123. I know I can make them LOOK like that by using custom format of ####-##-###-#### for column A, but that does not make them come up in a search when I use the dashes in the search. I want to be able to search for the number using the dashes SO I want a way to automatically make the number in the formula bar and the cell appear with the dashes. Can anyone help me with this? Thank you in advance!

BBB

**arlu1201** · 01-13-2012, 01:31 PM

Put this formula in column B -

Please Login or Register  to view this content.

Drag down & Paste Special.

**BBLANCOBRNX** · 01-13-2012, 01:57 PM

Hey, that's neat! Only problem is I have other data in column b, c, d, and e...the code you gave me gives me new data with the dashes in column b...is there a way to get the data in column a to just be changed? Thank you for the response.

BBB

Edit: I could make space above the first entry if that would somehow help?

**arlu1201** · 01-13-2012, 02:16 PM

You can use this formula in any column you want. Once you are done with dragging it down to the whole column, just copy and paste special values in column A.

**BBLANCOBRNX** · 01-13-2012, 02:44 PM

Originally Posted by arlu1201

You can use this formula in any column you want. Once you are done with dragging it down to the whole column, just copy and paste special values in column A.

OH! I misunderstood. IT WORKED PERFECTLY! Now there is one other teeny little problem maybe you can help me with

I have some duplicate values, like 3 or 4 of the same number in a row in column A, with different "quantities" in column B. Is there a way to add together like rows (while only adding up the "quantities" from column B) thus eliminating the 3 or 4 duplicate entries and consolidating them to one row? Thank you so much for the help. I am learning so much thanks to this forum!!!

BBB

**arlu1201** · 01-13-2012, 02:53 PM

Glad your 1st issue worked. Regarding your 2nd issue, i will happy to help you with it. But you will need to raise a separate thread for that. You can mark this solved and then open a new thread with your issue.