+ Reply to Thread
Results 1 to 3 of 3

Limits of Replace and Substitute functions

  1. #1
    Registered User
    Join Date
    02-16-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    42

    Limits of Replace and Substitute functions

    Hi and thanks in advance,

    I am using Excel 2003

    I have a sheet that has in column A some long strings and I want a quick way, preferably without looping, to replace some characters with another character.

    The problem I have run into is that when I use Replace in VBA it is only applied to cells with no more than 1024 characters and so I tried using Substitute but that crashes if there is more than 1271 characters in a cell so I don't know how to process these long strings quickly.

    I tried the following and hit the limits mentioned above:

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Limits of Replace and Substitute functions

    Hello Deutz,

    A macro could be written using Regular Expressions to do the substitutions. I would need to see both the strings to be searched and the replacement strings to write the macro. Can you post a copy of the workbook?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    02-16-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2003
    Posts
    42

    Re: Limits of Replace and Substitute functions

    Thanks Leith,

    Afraid I can't post the workbook or the actual strings due to the sensitve nature of the data.

    Here is a much shorter dummy string which is in a similar format so hopefully you can see what I'm on about ...

    In cell A1 I might have something like this string below with each field delimited with the string ^*^ which I want to replace with a hash #

    A1: Joe Blow^*^12 Ashgrove Rd^*^Malvern^*^1000^*^45879856^*^^*^^*^14/07/2012

    A1 to become: Joe Blow#12 Ashgrove Rd#Malvern#1000#45879856###14/07/2012

+ 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