-2

I'm not all that great with Excel formulas, I have an Excel column 'Logical Diskspace', each cell of which has a similar value as below. 

 | DISK C: (NTFS)20.9731 GB (3.7077 GB Free Space)  | DISK E:
 (NTFS)107.3767 GB (22.7083 GB Free Space)  | DISK F: (NTFS)107.3767 GB
 (38.0516 GB Free Space)  | DISK G: (NTFS)53.6781 GB (21.7887 GB Free
 Space)  | DISK P: (NTFS)4.7252 GB (0.5997 GB Free Space)

What I need is to find the total used capacity excluding the C: drive.

Taking the above example, it should calculate as follows:

Exclude C: + (107.3767-22.7083)+(107.3767-38.0516)+(53.6781-21.7887)+(4.7252-0.5997)

Note that the number of drives may vary but would always be delimited by a '|' sign.

Could someone help me with a programmatic approach to achieve this?

Community
  • 1
  • 1
Mohit Srivastava
  • 1
  • You might to use regular expressions: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Trimax Sep 21 '15 at 11:03
  • 1
    Instead of regular expressions you could also use the `split` function: http://stackoverflow.com/search?q=[excel-vba]+split – Ralph Sep 21 '15 at 13:03

1 Answers1

0

VBA is not necessary but this would be very tedious to do more than one time without say Record Macro activated. Just a series of Find and Replace:

Find, in turn:

Free Space) |DISK
Free Space)
: (NTFS)
GB

and replace each with nothing (the first and last above start with a space).

Find ( Replace with -
Find, in turn F, G and P and replace each with +

Find |DISK*E and replace with =.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Not necessary, but still fun... FWIW, a quick macro I keep handy for bulk find-replace operations: `Sub rename() For Each c In Range("Table1[Column1]") Cells.Replace What:=(c.value) _ , Replacement:=(c.offset(0,1).value) _ , LookAt:=xlPart _ , SearchOrder:=xlByRows _ , MatchCase:=False _ , SearchFormat:=False _ , ReplaceFormat:=False Next c msgbox "success" End Sub` – Peter Vandivier Sep 22 '15 at 02:14
  • 1
    yea, with only 1 row of sample input, it didn't seem prudent to demo out a sample post. Also, given the OP phrasing, I doubt anyone who could use the sub will look here for it, just wanted to include as a bonus followup to your answer. – Peter Vandivier Nov 03 '15 at 14:54