Dynamic Find and Replace

[philCITSL]

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

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Disk Free Space[/TD]
[TD]45.14 TB[/TD]
[/TR]
[TR]
[TD]Backups[/TD]
[TD]23.34 GB[/TD]
[/TR]
[TR]
[TD]Email Storage Used[/TD]
[TD]10.23 GB[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Fluff]

How about

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]

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

[TABLE="width: 342"]
<tbody>[TR]
[TD]TEST[/TD]
[TD="align: right"]REDACTED[/TD]
[/TR]
[TR]
[TD]xyz Version
[/TD]
[TD="align: right"]3.3[/TD]
[/TR]
[TR]
[TD]Disk Free Space[/TD]
[TD] 36.38 TB[/TD]
[/TR]
[TR]
[TD]Disk Space Used[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Backups Total Size[/TD]
[TD] 1.48 GB[/TD]
[/TR]
[TR]
[TD]Emails Total Size[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Memory[/TD]
[TD] 64 GB[/TD]
[/TR]
[TR]
[TD]REDACTED[/TD]
[TD="align: right"]0.08%[/TD]
[/TR]
[TR]
[TD]REDACTED Minutes[/TD]
[TD="align: right"]0.03%[/TD]
[/TR]
[TR]
[TD]REDACTED Minutes[/TD]
[TD="align: right"]0.06%[/TD]
[/TR]
[TR]
[TD]REDACTED[/TD]
[TD="align: right"]3342[/TD]
[/TR]
[TR]
[TD]Total Rows in Data Tables[/TD]
[TD="align: right"]500230[/TD]
[/TR]
[TR]
[TD]Total Screens[/TD]
[TD="align: right"]10794[/TD]
[/TR]
[TR]
[TD]Email[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Average CPU[/TD]
[TD="align: right"]1.95%[/TD]
[/TR]
[TR]
[TD]Average Load[/TD]
[TD="align: right"]0.14%[/TD]
[/TR]
[TR]
[TD]REDACTED[/TD]
[TD="align: right"]2.26%[/TD]
[/TR]
[TR]
[TD]Disks[/TD]
[TD] ALL OK[/TD]
[/TR]
[TR]
[TD]Power Supply 1[/TD]
[TD] 50 Watts[/TD]
[/TR]
[TR]
[TD]Power Supply 2[/TD]
[TD] 115 Watts[/TD]
[/TR]
</tbody>[/TABLE]



Other than that, many thanks.

 

[Fluff]

How about

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

 

[philCITSL]

Amazing. Works perfectly.


Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback