Formula to Caluclate difference separated by Hyphen in Single Cell

[petes]

All - Need Some help on Formula to calculate the difference for the figures appearing in single separated by Hyphen

In Column A of Excel I have values like:

413.0-413.1 in A1
414.00-414.07 in A2
414.10-414.12 in A4

I need a formula in Column B that will display the Result as:

2 in B1 for A1
8 in B2 for A2
3 in B3 for A4

Actullay, the Result is the difference in figures between the hyphen (-) and adding 1 to it. Since it is in the single cell, i am not able to think of any formula

Any Thoughts?? Thanks!!

 

[njimack]

=right(a1)-mid(a1,find("-",a1)-1,1)+1

 

[petes]

Thanks for the quick Reply... It is working Perfect if the Format of Column A is "Number". But Actually my format is "Text" and hence displaying #Value!

Whether any changes can be done??

 

[njimack]

Thanks for the quick Reply... It is working Perfect if the Format of Column A is "Number". But Actually my format is "Text" and hence displaying #Value!

Whether any changes can be done??

It works for me regardless of the format. Try this:
=RIGHT(D1)+0-MID(D1,FIND("-",D1)-1,1)+1

 

[petes]

Not Sure why I am getting #Value! in my spreadsheet... i will look into it and will let you know.

Thanks

 

[petes]

One Observation - If I manually type the same values in Column A again, I am getting the correct result. But there are thousands of records... not sure whatz the issue??

 

[njimack]

Try using Text To Columns on the entire column - changing the format from Text to General.

 

[barry houdini]

In the first row of your example you have a single digit after the decimal point, in the next two examples you have 2 digits. Assuming that each cell has the same number for both (1 or 2) and that the number before the decimal is always the same try

=RIGHT(A1,LEN(A1)-FIND(".",A1,LEN(A1)-3))-MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1,LEN(A1)-3))+1

 

[T. Valko]

Not Sure why I am getting #Value! in my spreadsheet... i will look into it and will let you know.

Thanks

You might have trailing whitespace characters in your data.

For example:

413.0-413.1

Might actually be:

413.0-413.1[space]

That will cause the #VALUE! error.

Try this tweak of njimack's formula:

=RIGHT(TRIM(A1))-MID(A1,FIND("-",A1)-1,1)+1

 

[petes]

after all my research and headache, I came to know that, it is NOT the Hyphen that is separating figures, but actually it is a Symbol (charcter code 2500).


Any formula available for this type of scenario...

Thanks!!