Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to convert a list of property id numbers that has a standard format of 0000-A-00000-00-00, where my worksheet has the various combinations of

  • 1-A-123
  • 10-B-1234

Ideally they would read as follows

  • 0001-A-00123-0000-00
  • 0010-B-01234-0000-00

I've tried using the custom number formatting but it doesn't like the letter in the middle of the number. I didn't know if my only option was to break them apart and then put them back together again.

I would accept a solution that merely put the leading zeros at the front of the number, (max is 4 characters) so the result could look like 0001-A-123

share|improve this question

migrated from stackoverflow.com Aug 28 '12 at 12:29

This question came from our site for professional and enthusiast programmers.

3 Answers 3

You could use a mega-formula or VBA for this

To recut data in A1 to your desired outpu in B1

=IFERROR(REPT("0",4-FIND("-",A1)+1)&LEFT(A1,FIND("-",A1)-1)&"-"&MID(A1,FIND("-",A1,FIND("-",A1)+1)-1,1)&"-"&REPT(0,5-LEN(RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1))))&RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1))&"-0000-00","missing -")

enter image description here

share|improve this answer

You'll have to split them apart (into 3 pieces) then combine them back again using string functions.

Like this: https://docs.google.com/spreadsheet/ccc?key=0AqyHjJ0rl5d1dHgxNFlNVWd2Njd2cDVBaEtwWGxfU1E

share|improve this answer

Save the spreadsheet as a CSV, edit with a smart text editor (e.g., vi), and read back into Excel.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.