Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
In column A and B I have text representing an Entity and its Intercompany,
respectively. In column C I have concatenated the two. So now in Column C, for example, I have "BillTom" in row 1 and "TomBill" in row 900. I need Excel to convert the text into some sort of code, ASCII or number that shows they are EQUAL. I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. I cannot build an IF formula or use Find/Replace, as I will have thousands of these types of combinations. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23
wrote: In column A and B I have text representing an Entity and its Intercompany, respectively. In column C I have concatenated the two. So now in Column C, for example, I have "BillTom" in row 1 and "TomBill" in row 900. I need Excel to convert the text into some sort of code, ASCII or number that shows they are EQUAL. I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. I cannot build an IF formula or use Find/Replace, as I will have thousands of these types of combinations. Thanks! You could mark entries which are duplicates by your rules. Would that be OK. For example, in a helper column or as a conditional formatting formula: =COUNTIF($C$1:$C$10,B1&A1) would be 1 or more if the reverse concatenation existed in column C --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
I can't mark thousands of rows every month, I want Excel do do the
marking/matching via a formula or macro. Anything else? "Ron Rosenfeld" wrote: On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 You could mark entries which are duplicates by your rules. Would that be OK. For example, in a helper column or as a conditional formatting formula: =COUNTIF($C$1:$C$10,B1&A1) would be 1 or more if the reverse concatenation existed in column C --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
Record a macro while doing it
Regards, Peo Sjoblom "MaxNY23" wrote in message ... I can't mark thousands of rows every month, I want Excel do do the marking/matching via a formula or macro. Anything else? "Ron Rosenfeld" wrote: On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 You could mark entries which are duplicates by your rules. Would that be OK. For example, in a helper column or as a conditional formatting formula: =COUNTIF($C$1:$C$10,B1&A1) would be 1 or more if the reverse concatenation existed in column C --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
Please reread my original request. I don't think you guys get what I'm
trying to do. Record a macro while doing WHAT? I'm looking for a function or formula that converts text to a number. Sort of like the CODE function but for more that just the first letter of a cell. I cannot manually mark each row to distinguish it. Again there will be thousands of rows and hundreds of text combinations. "Peo Sjoblom" wrote: Record a macro while doing it Regards, Peo Sjoblom "MaxNY23" wrote in message ... I can't mark thousands of rows every month, I want Excel do do the marking/matching via a formula or macro. Anything else? "Ron Rosenfeld" wrote: On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 You could mark entries which are duplicates by your rules. Would that be OK. For example, in a helper column or as a conditional formatting formula: =COUNTIF($C$1:$C$10,B1&A1) would be 1 or more if the reverse concatenation existed in column C --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
"MaxNY23" wrote in message
... In column A and B I have text representing an Entity and its Intercompany, respectively. In column C I have concatenated the two. So now in Column C, for example, I have "BillTom" in row 1 and "TomBill" in row 900. I need Excel to convert the text into some sort of code, ASCII or number that shows they are EQUAL. I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. I cannot build an IF formula or use Find/Replace, as I will have thousands of these types of combinations. Thanks! Max, Not familiar with VBasic or macros myself, I'd use CHBASE from the morefunc.dll that may not be installed. I'd convert separately your first and second columns from base 36 to Base 10 give a value, a code if you will, for each side, and compare both sides against each other in another column.... way out there, but I've such a simple mind... Beege |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
On Wed, 22 Mar 2006 09:11:27 -0800, MaxNY23
wrote: I can't mark thousands of rows every month, I want Excel do do the marking/matching via a formula or macro. Anything else? You did not indicate HOW you wanted the duplicates to be marked, so I gave you a general solution to detect duplicates. If you are not able to apply that information to your problem, then you will need to be more detailed in explaining, the sentence you wrote: "I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. exactly what you mean by "SHOWS". If you apply the formula I gave you as a conditional format formula, you could format your duplicates differently from the non-duplicates. To me, this is one way of "SHOW"ing that various entries have been duplicated. I guess you mean something else by "SHOWS" but you'll have to be more specific. --ron |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
I previously copied the following formula from the site (don't remember who
originally contributed it,) and I modified it slightly to get rid of Upper and subtracting 33. Note: this is far from perfect, but I doubt there is a perfect solution to your request. =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)) ),1))) TomBill 691 BillTom 691 FrankJohn 897 JohnFrank 897 DAD 201 BEB 201 -- Kevin Vaughn "MaxNY23" wrote: In column A and B I have text representing an Entity and its Intercompany, respectively. In column C I have concatenated the two. So now in Column C, for example, I have "BillTom" in row 1 and "TomBill" in row 900. I need Excel to convert the text into some sort of code, ASCII or number that shows they are EQUAL. I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. I cannot build an IF formula or use Find/Replace, as I will have thousands of these types of combinations. Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
I also think you need to be a bit more explicit about the nature of the
"Entity" and its "Intercompany". Your example just shows two 4 character names, but is this representative of what you will have in reality? Could you, for example, have "ApplesPears" and "PearsApples", or "PineappleGrapes" and "GrapesPineapple" - how would you know where to split the combined strings? Pete |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
On Wed, 22 Mar 2006 13:35:28 -0800, Kevin Vaughn
wrote: I previously copied the following formula from the site (don't remember who originally contributed it,) and I modified it slightly to get rid of Upper and subtracting 33. Note: this is far from perfect, but I doubt there is a perfect solution to your request. =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),1))) TomBill 691 BillTom 691 FrankJohn 897 JohnFrank 897 DAD 201 BEB 201 Is the OP interested in telling whether or not TomBill and BillTom are composed of identical word strings, or is he interested in knowing if the ASCII codes of the letters add up to the same value? There are many combinations that will add up to 691: TomBill BillTom KimJune JuneKim WilmaJo etc. --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
Is the OP interested in telling whether or not TomBill and BillTom are
composed of identical word strings, or is he interested in knowing if the ASCII codes of the letters add up to the same value? There are many combinations that will add up to 691: TomBill BillTom KimJune JuneKim WilmaJo etc. His attitude is hardly encouraging either -- Regards, Peo Sjoblom |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
Pete_UK wrote...
I also think you need to be a bit more explicit about the nature of the "Entity" and its "Intercompany". Your example just shows two 4 character names, but is this representative of what you will have in reality? Could you, for example, have "ApplesPears" and "PearsApples", or "PineappleGrapes" and "GrapesPineapple" - how would you know where to split the combined strings? You should quote or summarize relavant context. If the OP's goal is just to check whether some simple swapping of substrings in one string produces another string, e.g., AAAABBB - AAAA BBB - BBB AAAA - BBBAAAA in which case AAAABBB and BBBAAAA are deemed equivalent, then this could be done using built-in functions and defined names. The name could be seq referring to =ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,1024,1)) and if the two strings were in A1 and A2, the formula would look like =SUMPRODUCT(--(EXACT(MID(A1,seq,64)&LEFT(A1,seq-1),A2)))0 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
On Wed, 22 Mar 2006 18:15:20 -0800, "Peo Sjoblom"
wrote: Is the OP interested in telling whether or not TomBill and BillTom are composed of identical word strings, or is he interested in knowing if the ASCII codes of the letters add up to the same value? There are many combinations that will add up to 691: TomBill BillTom KimJune JuneKim WilmaJo etc. His attitude is hardly encouraging either Perhaps he wants us to read his mind? --ron |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
This is true which is why I pointed out the trivial example of DAD and BEB
equalling the same amount. But I admit your examples adding up to 691 are better than my examples. -- Kevin Vaughn "Ron Rosenfeld" wrote: On Wed, 22 Mar 2006 13:35:28 -0800, Kevin Vaughn wrote: I previously copied the following formula from the site (don't remember who originally contributed it,) and I modified it slightly to get rid of Upper and subtracting 33. Note: this is far from perfect, but I doubt there is a perfect solution to your request. =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),1))) TomBill 691 BillTom 691 FrankJohn 897 JohnFrank 897 DAD 201 BEB 201 Is the OP interested in telling whether or not TomBill and BillTom are composed of identical word strings, or is he interested in knowing if the ASCII codes of the letters add up to the same value? There are many combinations that will add up to 691: TomBill BillTom KimJune JuneKim WilmaJo etc. --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
On Thu, 23 Mar 2006 08:20:48 -0800, Kevin Vaughn
wrote: This is true which is why I pointed out the trivial example of DAD and BEB equalling the same amount. But I admit your examples adding up to 691 are better than my examples. -- Kevin Vaughn Ah, <sound of hand slapping forehead. At the time I posted, I didn't understand why you had those examples and I just skipped over it. --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert text strings to a code or number
:)
BTW, how long did it take to come up with your examples? I would have liked to come up with examples like those, but I thought it would take a while with a lot of trial and error for examples that long (especially ones that made sense like yours did.) -- Kevin Vaughn "Ron Rosenfeld" wrote: On Thu, 23 Mar 2006 08:20:48 -0800, Kevin Vaughn wrote: This is true which is why I pointed out the trivial example of DAD and BEB equalling the same amount. But I admit your examples adding up to 691 are better than my examples. -- Kevin Vaughn Ah, <sound of hand slapping forehead. At the time I posted, I didn't understand why you had those examples and I just skipped over it. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to number for a range of data in a column | Excel Discussion (Misc queries) | |||
convert number to text in Spanish Language | Excel Worksheet Functions | |||
convert number to text and format it. | Excel Worksheet Functions | |||
convert number to text and format it. | Excel Worksheet Functions | |||
convert number into corrseponding text | Excel Discussion (Misc queries) |