Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default What function to use? Len?

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 563
Default What function to use? Len?

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default What function to use? Len?

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

"mklafert" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default What function to use? Len?

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?

"Eduardo" wrote:

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

"mklafert" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 563
Default What function to use? Len?

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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?

"Eduardo" wrote:

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

"mklafert" wrote:

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default What function to use? Len?

That seems to work but tell me where the number is coming from?

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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


.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default What function to use? Len?

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.

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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?

"Eduardo" wrote:

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

"mklafert" wrote:

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


.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default What function to use? Len?

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).

--
Rick (MVP - Excel)


"mklafert" wrote in message
...
That seems to work but tell me where the number is coming from?

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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


.


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default What function to use? Len?

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" wrote:

That seems to work but tell me where the number is coming from?

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"mklafert" wrote in message
...
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


.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default What function to use? Len?

mklafert wrote:
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



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(A 1,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.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default What function to use? Len?

Glenn wrote:
mklafert wrote:
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



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)



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.



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(A 1,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.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default What function to use? Len?

A passing thought, why not just keep it simple, use running serial nums.
--
Max
Singapore
---

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Data Validation/Lookup function does function correcty Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 04:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"