I got a problem that is driving me nuts

[punter]

Hi,

This is my first time posting and I hope someone can help me.

Every week I get about 20,000 lines of data that come to me in an excel
sheet from a vendor that we do a lot of business with. The problem
that I have is with their invoice numbers. Their invoice numbers are 9
digits long and should look like this:

1-234-56789

My issue is that they come to me on the sheet like this:

123456789

I need to have the dashes in the so I can enter them properly into our
computer system. I have no problem concatenating them but I do have an
issue either the second or fifth number is a zero. Example:
123406789.

What I would do is text to columns and then concatenate to place the
dashes where they belong. For some reason the zeros are getting
dropped if they are in the second or fifth spot. Example:

123406789 is how I receive the data
1-234-6789 is how the data ends up after I text to column and
concatenate.

Any help that anyone could provide would be awesome. Even after
sorting by the ones that are missing the zeros I am still losing about
an hour a week putting them back in.

Thanks

 

[Andy B]

Hi

Try using this formula:

=LEFT(A2,1)&"-"&MID(A2,2,3)&"-"&RIGHT(A2,5)

Andy.

 

[Myrna Larson]

You can leave the numbers alone and apply this custom number format to the column:

0-000-00000

which will put the dashs in the right places.

Otherwise, use these custom number formats: 2nd column 000, 3rd 00000. This will "put back" the
zeroes.

 

[punter]

Thanks a lot Andy. I tried it and it works like a charm. You saved me
an hour a week that I can now put to constructive use.

Myrna I also thank you but the custom format didn't work for me. The
zeros wouldn't end up coming through in the final column.

 

[Myrna Larson]

The custom formats will add the dashes or the leading zeroes. What do you have/want in the
"final column"?