Dynamic Find and Replace

philCITSL

New Member
Joined
Jul 18, 2019
Messages
3
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 CColumn D
Disk Free Space45.14 TB
Backups23.34 GB
Email Storage Used10.23 GB

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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
 

philCITSL

New Member
Joined
Jul 18, 2019
Messages
3
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

TESTREDACTED
xyz Version
3.3
Disk Free Space 36.38 TB
Disk Space Used0.00%
Backups Total Size 1.48 GB
Emails Total Size0
Memory 64 GB
REDACTED0.08%
REDACTED Minutes0.03%
REDACTED Minutes0.06%
REDACTED3342
Total Rows in Data Tables500230
Total Screens10794
Email0
Average CPU1.95%
Average Load0.14%
REDACTED2.26%
Disks ALL OK
Power Supply 1 50 Watts
Power Supply 2 115 Watts

<tbody>
</tbody>



Other than that, many thanks.
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,645
Messages
5,488,077
Members
407,623
Latest member
Deigs

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top