I am trying to upload a great deal of vendors into a new accounting package,
but I cannot use the old vendor number format in the new software. I would
like Excel to automatically assign vendor numbers based on the first letter
of the vendor name. Taking that letter and simply add one number to the
previous vendor. Such as A000001, then A000002, etc.

Not sure exactly how you have your spreadsheet setup, but this will
take the first letter of the name in column B and then increment the
number above it by one. This is assuming that you're vendor numbers
are in column A and your vendow names in column B.
=LEFT(B11,1) & TEXT(RIGHT(A10,6)+1,"000000")

Now, that will work, but not the best way, IMO. I would use something
like this so that you don't have to tweak the formula any when the
first letter of the company name changes.
=IF(LEFT(B12,1)=LEFT(A11,1),LEFT(B12,1)&TEXT(RIGHT(A11,6)+1,"000000"),LEFT(B12,1)
& TEXT(1,"000000"))


Monique wrote:
> I am trying to upload a great deal of vendors into a new accounting package,
> but I cannot use the old vendor number format in the new software. I would
> like Excel to automatically assign vendor numbers based on the first letter
> of the vendor name. Taking that letter and simply add one number to the
> previous vendor. Such as A000001, then A000002, etc.

Forgot to mention, the formulas would be entered into column A. For
instance, the formula below would be entered into A12.
=IF(LEFT(B12,1)=LEFT(A11,1),LEFT(B12,1)&TEXT(RIGHT(A11,6)+1,"000000"),LEFT(B12,1)
& TEXT(1,"000000"))

JW wrote:
> Not sure exactly how you have your spreadsheet setup, but this will
> take the first letter of the name in column B and then increment the
> number above it by one. This is assuming that you're vendor numbers
> are in column A and your vendow names in column B.
> =LEFT(B11,1) & TEXT(RIGHT(A10,6)+1,"000000")
>
> Now, that will work, but not the best way, IMO. I would use something
> like this so that you don't have to tweak the formula any when the
> first letter of the company name changes.
> =IF(LEFT(B12,1)=LEFT(A11,1),LEFT(B12,1)&TEXT(RIGHT(A11,6)+1,"000000"),LEFT(B12,1)
> & TEXT(1,"000000"))
>
>
> Monique wrote:
> > I am trying to upload a great deal of vendors into a new accounting package,
> > but I cannot use the old vendor number format in the new software. I would
> > like Excel to automatically assign vendor numbers based on the first letter
> > of the vendor name. Taking that letter and simply add one number to the
> > previous vendor. Such as A000001, then A000002, etc.

Assume Col A is the column to be numbered. Type 1 in the cell you want to
begin the numbering sequence in. Then, say your first cell is A3 put this in
the next cell and copy down the column:

=If(B3>0, A3 + 1, "")

The cells in column A will remain blank until you enter something in column
B of the previous row.

"Monique" wrote:

> I am trying to upload a great deal of vendors into a new accounting package,
> but I cannot use the old vendor number format in the new software. I would
> like Excel to automatically assign vendor numbers based on the first letter
> of the vendor name. Taking that letter and simply add one number to the
> previous vendor. Such as A000001, then A000002, etc.

disregard that, I misread your post.

"Monique" wrote:

> I am trying to upload a great deal of vendors into a new accounting package,
> but I cannot use the old vendor number format in the new software. I would
> like Excel to automatically assign vendor numbers based on the first letter
> of the vendor name. Taking that letter and simply add one number to the
> previous vendor. Such as A000001, then A000002, etc.