I'm fairly rusty with my excel. I have a list of part numbers, see below:

00011001
00011002
00011003
00011004
00011005
00011006
00011010
00011011
00966963001
01023506180
01023506200
01023506220
01023506280
01023506500
01123508140
01123508160
01123508180
0112350822
01123508220
01123508250
01123508280
01123508320
01123508350
01123508600
01123508700
01123508750
01123508800
01123508850
01123518160
01125508160
01133510250
01133510300
0113351235
01153508320
01173508010
0151350616
0151350618
0151350822
0175450801
0337854
0401150180
0472400100
0481050550
0481100150
0481100160

While those samples have all numbers, some part numbers do have letters in them. I need to format all my numbers/text, like this:

####-###-###-###

Right now, i can split the data up and use the CONCATENATE function to bring it back together but my shorter part numbers end up like this:

1100-1--
1100-2--

but the longer ones come out right. Any suggestions on a format that can adjust to shorter part numbers?

1/13/2009 3:07:21 PM

What do you mean adjust to shorter part numbers?

1/13/2009 3:28:45 PM

you could write an IF function to add the Zeros to the end of each part number if it needs it (Assuming thats what youre trying to do) them chop and concat. although if that's your complete list i'd just add the zeros manually

[Edited on January 13, 2009 at 3:46 PM. Reason : ]

1/13/2009 3:45:25 PM

I need to put a "-" into the raw numbers. The numbers should be in this format after the inserted the "-": 1111-111-111-111

but some of the shorter part numbers should also fit the criteria for example:

1111-11
1111-111
1111-111-1

edit: the complete list is over 30,000+ numbers.

[Edited on January 13, 2009 at 3:48 PM. Reason : s]

1/13/2009 3:46:17 PM

your actual list is longer than this right?

assuming you aren't going to add zeros, you could do a control+f (replace) after your're done to remove the -- and --- etc that will be on the right of the smaller numbers.

then you'll just have to deal with the ones with a single - on the end. and i gotta run so i can't figure out that part. i mean VBscript is an obvious answer, but Im assuming you would have already turned there if you could (as would i) gl

1/13/2009 3:49:47 PM

=LEFT(A1,4)&IF(LEN(A1)>4,"-"&MID(A1,5,3),"")&IF(LEN(A1)>8,"-"&MID(A1,9,3),"")&IF(LEN(A1)>11,"-"&MID(A1,12,3),"")

1/13/2009 4:40:26 PM

pro

1/13/2009 4:56:24 PM

well played

1/13/2009 6:12:51 PM

VBA is an overused crutch

1/13/2009 6:19:47 PM

In Excel 2003 (I think 2007 is the same, just don't have it on this computer) go to "Format Cells" and pick "Custom" underneath the number tab.

Underneath "Type" put in the number of 0's you want to pad your numbers to (in your case, 10).

This will turn everything into a 10 character number, which should allow your normal processing to work on it.

[Edited on January 13, 2009 at 6:51 PM. Reason : .]

1/13/2009 6:51:30 PM

doesnt sound like he wants to pad 0s

i'm assuming that formula agentlion will work for his purposes

1/13/2009 7:14:38 PM

had a couple off-by-1 errors causing it to sometimes ignore the final character. be sure to double check all the boundry conditions here. i.e. strings with 4, 5, 7, 8, 10, 11 characters (just go ahead and check all lengths. I have not)

=LEFT(A1,4)&IF(LEN(A1)>4,"-"&MID(A1,5,3),"")&IF(LEN(A1)>7,"-"&MID(A1,8,3),"")&IF(LEN(A1)>10,"-"&MID(A1,11,3),"")

[Edited on January 13, 2009 at 10:00 PM. Reason : .]

ok, actually here are all cases for strings up to 13 characters long (the longest you indicated in your example) and they all work. Anything longer than 13 characters will be truncated


[Edited on January 13, 2009 at 10:02 PM. Reason : .]

1/13/2009 9:58:56 PM

can I hijack for a minute? thanks.

Is there anyway to edit the ribbon in Excel 2007. Help documents say no, which has got to be bullshit. I have two computers running Excel 2007 (from the same disc, set-up,etc), but the ribbon is different on each computer...

1/15/2009 12:00:58 PM

the ribbon automatically reorganizes itself based on the screen resolution and window width.
Try making the windows on both computers the exact same size, and I bet it will look the same

1/15/2009 12:20:13 PM

well sonofabitch. one monitor is smaller than the other and I want the ribbon to look like it does on the larger monitor

1/15/2009 12:27:37 PM