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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top