+ Reply to Thread
Results 1 to 16 of 16

Convert text strings to a code or number

  1. #1
    MaxNY23
    Guest

    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. #2
    Ron Rosenfeld
    Guest

    Re: Convert text strings to a code or number

    On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 <MaxNY23@discussions.microsoft.com>
    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. #3
    MaxNY23
    Guest

    Re: 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. #4
    Peo Sjoblom
    Guest

    Re: Convert text strings to a code or number

    Record a macro while doing it


    Regards,

    Peo Sjoblom


    "MaxNY23" <MaxNY23@discussions.microsoft.com> wrote in message
    news:CD97A0BA-E8C6-4409-985E-4BE9F9DDF841@microsoft.com...
    >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. #5
    MaxNY23
    Guest

    Re: 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" <MaxNY23@discussions.microsoft.com> wrote in message
    > news:CD97A0BA-E8C6-4409-985E-4BE9F9DDF841@microsoft.com...
    > >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. #6
    Beege
    Guest

    Re: Convert text strings to a code or number

    "MaxNY23" <MaxNY23@discussions.microsoft.com> wrote in message
    news:0042ADC8-221B-4ADA-993B-B13BDE2929E7@microsoft.com...
    > 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. #7
    Ron Rosenfeld
    Guest

    Re: Convert text strings to a code or number

    On Wed, 22 Mar 2006 09:11:27 -0800, MaxNY23 <MaxNY23@discussions.microsoft.com>
    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. #8
    Kevin Vaughn
    Guest

    RE: 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. #9
    Pete_UK
    Guest

    Re: 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. #10
    Ron Rosenfeld
    Guest

    Re: Convert text strings to a code or number

    On Wed, 22 Mar 2006 13:35:28 -0800, Kevin Vaughn
    <KevinVaughn@discussions.microsoft.com> 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. #11
    Peo Sjoblom
    Guest

    Re: 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. #12
    Harlan Grove
    Guest

    Re: 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. #13
    Ron Rosenfeld
    Guest

    Re: Convert text strings to a code or number

    On Wed, 22 Mar 2006 18:15:20 -0800, "Peo Sjoblom"
    <peo.sjoblom@^^nwexcelsolutions.com> 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. #14
    Kevin Vaughn
    Guest

    Re: 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
    > <KevinVaughn@discussions.microsoft.com> 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. #15
    Ron Rosenfeld
    Guest

    Re: Convert text strings to a code or number

    On Thu, 23 Mar 2006 08:20:48 -0800, Kevin Vaughn
    <KevinVaughn@discussions.microsoft.com> 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. #16
    Kevin Vaughn
    Guest

    Re: 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
    > <KevinVaughn@discussions.microsoft.com> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1