Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |