Results 1 to 6 of 6

Thread: Dynamic Find and Replace
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Find and Replace

    This is my first post, so apologies if I miss anything out!

    I have a selection of data in a Worksheet and I am currently using a find and replace array to correct some data before it get put into a Pivot Table.

    The problem I am stumped with is that the source cell (Column D) can contain a different set of characters which represent or show the storage space used up on a Server.

    It looks like this

    Column C Column D
    Disk Free Space 45.14 TB
    Backups 23.34 GB
    Email Storage Used 10.23 GB

    As you can see, Column D can show either GB or TB at the end of the number.

    What I'd like to to is do a Find and replace, or maybe a find, replace and amend so to speak.

    I'd like to be able to remove the GB & TB from column D and add the relevant one to the end of the text in Column C. It's not always the same, sometimes 'Backups' could have TB or 'Email Storage Used' could be TB also.

    I'm sure it's a simple thing to do, but I can't quite piece together what code is needed!

    Many thanks and I hope this makes sense.

    Phil

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Dynamic Find and Replace

    How about
    Code:
    Sub philCITSL()
       With Range("C2", Range("C" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace("if(@="""","""",@&right(" & .Offset(, 1).Address & ",3))", "@", .Address))
          .Offset(, 1).Value = Evaluate(Replace("if(@="""","""",left(@,len(@)-3))", "@", .Offset(, 1).Address))
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Find and Replace

    Thanks for this I should have said something that after I tried this will appear to be essential info!!

    There are rows of data in between each of the rows I want to change! So the data would look like this and the code moves the last three characters from each column. So it needs to look for Columns that ONLY contain a GB or TB.


    Column C Column D

    TEST REDACTED
    xyz Version 3.3
    Disk Free Space 36.38 TB
    Disk Space Used 0.00%
    Backups Total Size 1.48 GB
    Emails Total Size 0
    Memory 64 GB
    REDACTED 0.08%
    REDACTED Minutes 0.03%
    REDACTED Minutes 0.06%
    REDACTED 3342
    Total Rows in Data Tables 500230
    Total Screens 10794
    Email 0
    Average CPU 1.95%
    Average Load 0.14%
    REDACTED 2.26%
    Disks ALL OK
    Power Supply 1 50 Watts
    Power Supply 2 115 Watts



    Other than that, many thanks.
    Last edited by Fluff; Jul 18th, 2019 at 11:25 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Dynamic Find and Replace

    How about
    Code:
    Sub philCITSL()
       With Range("C2", Range("C" & Rows.Count).End(xlUp))
          .Value = Evaluate(Replace(Replace("if((isnumber(search(""TB"",@d)))+(isnumber(search(""GB"",@d))),@&right(@d,3),@)", "@d", .Offset(, 1).Address), "@", .Address))
          .Offset(, 1).Value = Evaluate(Replace("if((isnumber(search(""TB"",@)))+(isnumber(search(""GB"",@))),left(@,len(@)-3),@)", "@", .Offset(, 1).Address))
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Find and Replace

    Amazing. Works perfectly.


    Thank you so much.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Dynamic Find and Replace

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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