+ Reply to Thread
Results 1 to 10 of 10

Automatically replace

  1. #1
    Registered User
    Join Date
    10-31-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Automatically replace

    Hello, I would like to know if it is possible to have an Excel Spreadsheet automatically replace characters that are typed or pasted into a cell. For example, if I type or paste B3:0/0 in a cell, could it automatically make the value B3[0].0 instead? Thank you.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Automatically replace

    if it is a consistent change such as :0/ being replaced by [0]. then you could use find and replace. otherwise you probably need VBA (which I don't have as a skill set).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    10-31-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Re: Automatically replace

    Quote Originally Posted by Sambo kid View Post
    if it is a consistent change such as :0/ being replaced by [0]. then you could use find and replace. otherwise you probably need VBA (which I don't have as a skill set).
    Thank you for the reply. Yes however, the 0/0 could also be 3/15, for example, the constants vary.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Automatically replace

    well I'll be interested in seeing what is proposed then. I could write a formula to do it in a different cell that would depend on examples you have but it would only be in another cell, not the original one. To get a good formula in VBA you might have to provide a good sample of how the actual data looks so the script can be written. But for anything, VBA or formulas a sample workbook with the variations you have and an example of the results will go a long way in getting you the help you need.

  5. #5
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Automatically replace

    The answer to this depends on what conditions the replacements follow. Providing a sample would greatly help us help you, including examples of your desired output.

    It also depends on if you mean as the values are typed in or at some later point. Additionally, why do you need to do this? Is it in an effort to get everyone to enter stuff in the same syntax or something else?

    It may be possible to use custom formatting to accomplish this (however that would only handle changing everything to 1 thing/format). If you need it in cell "as its typed" this would require VBA, likely based on a worksheet_change event or something like that. If you are ok with it being displayed as you want in another cell, then a formula could likely do it.

    If the reason behind this is to get everyone to enter things in the same syntax, then you likely want data validation to force them to enter it correctly.

  6. #6
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Automatically replace

    Quote Originally Posted by Ceilingwalker View Post
    ...if I type or paste B3:0/0 in a cell, could it automatically make the value B3[0].0 instead...
    maybe yes, maybe no ... :
    Please Login or Register  to view this content.
    Code from ThisWorkbook module.
    Last edited by mjr veverka; 10-31-2017 at 03:26 PM.

  7. #7
    Registered User
    Join Date
    10-31-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4

    Re: Automatically replace

    So, what I would have is a series of values: B3:0/0, B3:0/10, B3:5/0, B3:120/13......... and I would need to replace them with B3:[0].0, B3:[0].10, B3:[5].0, B3:[120].13......

  8. #8
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Automatically replace

    Are these series already entered in the worksheet cells or will they be only/just entered manually (futurity) ?
    Are there schemes like: B3:1/130, B3:12/1, B3:12/13, B3:12/130, B3:120/1, B3:120/130, i.e.: B3:#/###, B3:##/#, B3:##/##, B3:##/###, B3:###/#, B3:###/###, others ?

    Edit:
    Please Login or Register  to view this content.
    Last edited by mjr veverka; 10-31-2017 at 05:06 PM.

  9. #9
    Registered User
    Join Date
    10-31-2017
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    4
    Quote Originally Posted by porucha vevrku View Post
    Are these series already entered in the worksheet cells or will they be only/just entered manually (futurity) ?
    Are there schemes like: B3:1/130, B3:12/1, B3:12/13, B3:12/130, B3:120/1, B3:120/130, i.e.: B3:#/###, B3:##/#, B3:##/##, B3:##/###, B3:###/#, B3:###/###, others ?

    Edit:
    Please Login or Register  to view this content.
    I will either enter them or paste them from elsewhere.

  10. #10
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Automatically replace

    Check if it will work.
    The procedure transferred to the module of the given sheet in which the data will be entered/pasted, e.g. Sheet1 module:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replace formula with its value automatically
    By TalkTime in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2014, 11:40 PM
  2. Replace 1 with A from two sheets automatically
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2014, 11:05 AM
  3. [SOLVED] Automatically replace the formula in a column
    By PLidgard in forum Excel General
    Replies: 3
    Last Post: 11-06-2013, 01:48 PM
  4. Automatically find and replace texts and numbers
    By a_27826 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-19-2013, 01:22 PM
  5. [SOLVED] Macro to Automatically Find/Replace
    By Cansa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 10:57 AM
  6. [SOLVED] how do i automatically replace formula with results
    By Eric in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2006, 02:15 PM
  7. Replies: 1
    Last Post: 09-30-2005, 04:05 PM

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