I have a column which contains Vendor names in a spreadsheet with Vendor
data. I created a formula that takes the first 3 char. of the Vendor Name,
then looks for a space then takes the next 3 char. after the space and
concatenates the 2 together to create a Vendor ID. Here's the formula:
=LEFT(C2,3)&TRIM(MID(C2,FIND(" ",C2&" "),4)). So, for a vendor name that
contains ADMIRAL CRAFT EQUIP., my Vendor ID = ADMCRA. Problem: A lot of the
Vendor Names contain just one word such as AD-SENSATION or T.W.C., etc. My
formula for these 2 produces AD- and T.W repectively.

Anyone have an idea how to modify my formula to test if the Vendor Name just
contains one string of char. with no space inbetween and then just take the
first 6 char. or if a space do my concatenation?

Tks,
Don

Try something like =IF(ISERROR(FIND(A1,"
"),LEFT(A1,6),LEFT(A1,3)&TRIM(MID(A1,FIND(" ",A1&" "),4)))

Dave


--
Brevity is the soul of wit.


"Donald King" wrote:

I have a column which contains Vendor names in a spreadsheet with Vendor
data. I created a formula that takes the first 3 char. of the Vendor Name,
then looks for a space then takes the next 3 char. after the space and
concatenates the 2 together to create a Vendor ID. Here's the formula:
=LEFT(C2,3)&TRIM(MID(C2,FIND(" ",C2&" "),4)). So, for a vendor name that
contains ADMIRAL CRAFT EQUIP., my Vendor ID = ADMCRA. Problem: A lot of the
Vendor Names contain just one word such as AD-SENSATION or T.W.C., etc. My
formula for these 2 produces AD- and T.W repectively.

Anyone have an idea how to modify my formula to test if the Vendor Name just
contains one string of char. with no space inbetween and then just take the
first 6 char. or if a space do my concatenation?

Tks,
Don

Try this:
=CONCATENATE(LEFT(A1,3),MID(A1,FIND(" ",A1,1)+1,3))


"Donald King" wrote:

I have a column which contains Vendor names in a spreadsheet with Vendor
data. I created a formula that takes the first 3 char. of the Vendor Name,
then looks for a space then takes the next 3 char. after the space and
concatenates the 2 together to create a Vendor ID. Here's the formula:
=LEFT(C2,3)&TRIM(MID(C2,FIND(" ",C2&" "),4)). So, for a vendor name that
contains ADMIRAL CRAFT EQUIP., my Vendor ID = ADMCRA. Problem: A lot of the
Vendor Names contain just one word such as AD-SENSATION or T.W.C., etc. My
formula for these 2 produces AD- and T.W repectively.

Anyone have an idea how to modify my formula to test if the Vendor Name just
contains one string of char. with no space inbetween and then just take the
first 6 char. or if a space do my concatenation?

Tks,
Don

=LEFT(C2,3)&MID(C2,IF(FIND(" ",C2&" ")LEN(C2),4,FIND(" ",C2)+1),3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Donald King" wrote in message
...
I have a column which contains Vendor names in a spreadsheet with Vendor
data. I created a formula that takes the first 3 char. of the Vendor
Name,
then looks for a space then takes the next 3 char. after the space and
concatenates the 2 together to create a Vendor ID. Here's the formula:
=LEFT(C2,3)&TRIM(MID(C2,FIND(" ",C2&" "),4)). So, for a vendor name that
contains ADMIRAL CRAFT EQUIP., my Vendor ID = ADMCRA. Problem: A lot of
the
Vendor Names contain just one word such as AD-SENSATION or T.W.C., etc.
My
formula for these 2 produces AD- and T.W repectively.

Anyone have an idea how to modify my formula to test if the Vendor Name
just
contains one string of char. with no space inbetween and then just take
the
first 6 char. or if a space do my concatenation?

Tks,
Don

Hi all,
Thanks for all the help. I tried the suggestions from Dave and Teethless
but on cells with just one word the fuction returns a #value. Bob's works
just fine.

Tks,
Don

"Donald King" wrote:

I have a column which contains Vendor names in a spreadsheet with Vendor
data. I created a formula that takes the first 3 char. of the Vendor Name,
then looks for a space then takes the next 3 char. after the space and
concatenates the 2 together to create a Vendor ID. Here's the formula:
=LEFT(C2,3)&TRIM(MID(C2,FIND(" ",C2&" "),4)). So, for a vendor name that
contains ADMIRAL CRAFT EQUIP., my Vendor ID = ADMCRA. Problem: A lot of the
Vendor Names contain just one word such as AD-SENSATION or T.W.C., etc. My
formula for these 2 produces AD- and T.W repectively.

Anyone have an idea how to modify my formula to test if the Vendor Name just
contains one string of char. with no space inbetween and then just take the
first 6 char. or if a space do my concatenation?

Tks,
Don