Perform Caluclation that has text

[dbarnes]

How do I subtract cells that contain numbers and text.

A1 has 10GB
B1 has 2GB

I want to subtract A1-B1 and have the result cell (C1) show 8GB.

Thank you!

 

[gsistek]

Do the cells all have only two letters at the end?

 

[VoG]

Try

=LEFT(A1,LEN(A1)-2)-LEFT(A2,LEN(A2)-2)&RIGHT(A1,2)

 

[dbarnes]

Yes. Only 2 characters at the end. The formula in this thread worked. You guys are the greatest!!

 

[Scott R]

How do I subtract cells that contain numbers and text.

A1 has 10GB
B1 has 2GB

I want to subtract A1-B1 and have the result cell (C1) show 8GB.

Thank you!

You could keep your dataset to numbers only and custom format them as 0"GB".

 

[rfiorille]

I have a similar situation but my text is before the numbers.

In column 'A' I have i.e.;

A1 "ind00002"
A2 "ind00004"
A3 "ind00006"
A4 "ind00009"
A5 "ind00010"
etc...

The text is always the same, "ind", but the numbers change. I would like to subtract 1 from each number like the following:

A1 "ind00002" - 1 = "ind00001"
A2 "ind00004" - 1 = "ind00003"
A3 "ind00006" - 1 = "ind00005" etc...

The results can be in in any column, B, C, D - it doesn't matter. I just want to generate the results.

I'm using MS Excel 2007 in Windows 7. I appreciate the help and can clarify if what I'm requesting isn't clear.

Thanks!

 

[VoG]

Try like this

="ind"&TEXT(RIGHT(A1,5)-1,"00000")

 

[rfiorille]

Works awesome...YOU ROCK!!

Thanks VoG!