What function to use? Len?

  • Thread starter Thread starter mklafert
  • Start date Start date
M

mklafert

I am going to create customer ID - Alphanumeric. I have the whole company
names and I was trying to come up with a scheme that I can use an Excel
Function to make the process faster. I thought about Len but not sure - Is
there a command where I can say give me Say the first letter of the company
name and numbers behind it.

Such as

American Can Company - Result of Function - Ameri125

If this is even possible
 
With A1 holding: American Can Company
This formula =LEFT(A1,5)&LEN(A1) returns Ameri20
I am not sure where you got the 125 from
best wishes
 
Hi,
which is the logic behind the numbers

if the numbers is in a list let' say column B and the name of the company is
in column A you can use

=LEFT(A1,5)&B1
 
Actually I should trying to create some kind of scheme - not sure what yet -
but possible the following example - American Can Company - ACC101?? There
is no logic for the numbers - What I am doing is creating brand new ID for
about 3,000 customers and importing them into an accounting system. After
this they will be added one by one. But initially I just trying to come up
with a scheme that I can use a formula instead of manually typing an Id for
each company name. Does this help?
 
Try
=LEFT(A1,5)&SUMPRODUCT(CODE(MID(A1&"
",{1,2,3,5},1)))+CODE(MID(A1,LEN(A1)/2,1))
I experimented with
American Can Company
American Tool & Die
American Coal Company
IBM
and got what looked like useable codes

You do know how to use Paste Special -> Values to turn formulas into values?
best wishes
 
Yes I know how to use paste special - thanks let me try yours solution
Bernard seem liked his would work but the problem with his is I have two
customer name :

ACME HYDRAULICS
ACME TRUCK LINE
and using his I come up with the same Id for both companies. I figure
where he got the number. It is how many characters in the name. Let me
check yours.
 
The 20 is coming from the LEN(A1) that was concatenated onto the end of his
LEFT function call... 20 is the number of characters making up the name of
the company (including the spaces between the name parts).
 
The 20 is the total length of the words 'American Can Company'.

As you've seen already, there are cases where using almost anything other
than a dedicated unique number generator is going to occassionally give you
duplicates. But even that situation gives you big leg up. You could use one
of the schemes that folks come up with here to get the initial list, then
sort by the generated IDs, and modify the ones that are duplicated or even
try this (or think of it as a possible scheme, but as long as the list is on
1 sheet, then numbers should be unique <g>).

=Left(A1,5) & Text(ROW(),"000")
and when you're done select all cells with the formula in them and Copy
followed by Edit --> Paste Special with 'Values' selected to convert the
results into entries that won't change.

Or
=Left(A1,5) & Text(ROW()*5,"000")
to give yourself a 'gap' of 4 numbers between entries for others that may
get added to your list in the future. Larger gaps may require you to go to
more digits than just 3, as
=Left(A1,5) & Text(ROW()*5,"0000")


Also, now is probably a good time to decide if a 3-digit number is going to
be 'good enough' over an extended period of time, or if you may want to use 4
or 5 digits?
 
mklafert said:
I am going to create customer ID - Alphanumeric. I have the whole company
names and I was trying to come up with a scheme that I can use an Excel
Function to make the process faster. I thought about Len but not sure - Is
there a command where I can say give me Say the first letter of the company
name and numbers behind it.

Such as

American Can Company - Result of Function - Ameri125

If this is even possible
Click to expand...


Assuming you have your list of company names starting in A1 and some open
columns to work with, try this:

B1
=" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,"-"," "),","," "),"."," ")," "," "))&REPT(" ",4)

C1
=TRIM(MID(B1,FIND("~",SUBSTITUTE(B1," ","~",1))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",2))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",3))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",4))+1,1)&
MID(B1,FIND("~",SUBSTITUTE(B1," ","~",5))+1,1))

D1
=IF(LEN(C1)=1,LEFT(TRIM(A1),3),IF(LEN(C1)=2,LEFT(A1,2)&RIGHT(C1,1),C1))

E1
=TEXT(COUNTIF($D$1:D1,D1),"000")

F1
=UPPER(D1)&E1

Copy B1:F1 down to the end of your list of companies. Column F will contain
unique alphanumeric company ID's with three to five letters, followed by a three
digit number. Copy / Paste Special / Values on column F, then delete columns B
through E.
 
Glenn said:
Assuming you have your list of company names starting in A1 and some
open columns to work with, try this:

B1
=" "&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,"-"," "),","," "),"."," ")," "," "))&REPT(" ",4)
Click to expand...


Forgot to mention that in the SUBSTITUTE formula you should put in whatever
separators that are found in your list of companies. I included a dash ("-"),
period ("."), comma (",") and a space (" "), the last of which of course had no
effect because it was being replaced by a space.
 
Back
Top