write now i am doing the below calculation in scientific calculator. in scientific calculator:-

8~55’0” * 0~4’0”+SHIFT+DMSThe output is 0~35’40”

the same i need in excel. could you please provide anybody. thanks in advance.

See here for a couple of User Defined Functions (UDF) for converting DMS to Decimal and back. https://support.microsoft.com/en-us...

You could do the calculations in multiple cells or combine the UDFs as follows:

=Convert_Degree(Convert_Decimal("8° 55' 0""")*(Convert_Decimal("0° 4' 0""")))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Just for fun, I "upgraded" the process. Try this: In B1 I entered Alt-0176 <space> to get

° <space>

In B2 I entered '' <space> to get' <space>(That's 2 single quotes and a space.)

In B3 I entered " to get"(Double quote)In Column A1:A3 and A5:A7 I entered the values shown.

In A9 I entered the formula shown.

Notes:

1 - In B2 you need to enter 2 single quotes to get one single quote because the first single quote is seen by Excel as a Text designator and does not show in the cell.

2 - In B1 and B2, you have to include the space after the

°and'in order to get the answer that you posted.A B 1 8 ° 2 55 ' 3 0 " 4 5 0 6 4 7 0 8 9 =Convert_Degree(Convert_Decimal(A1&B1&A2&B2&A3&B3)*Convert_Decimal(A5&B1&A6&B2&A7&B3))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thank you DerbyDad for giving the answer and it is working fine. thanks alot. --sankar

I'm glad it worked for you. What I find interesting is that if you leave out the spaces, you get a different answer. In fact, if you hadn't of included an example of both input and the expected output, I might have posted a solution that only

appearedto work - because it would have returned an answer, albeit an incorrect one.If I was going to use that UDF in a "production environment" I might add some error checking to make sure that the input included spaces to ensure that the correct output was produced every time.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hi Derby, I am using 2nd option which is closed to my requirement. and the output also should be in separate cells.

could you please help me.

A B C1 ° ' "2 08 55 003 00 04 004 0 35 40

You may need to play with this to make it work for every situation, but this is the general concept for one method: Degrees:

=LEFT(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*

Convert_Decimal(A3&A1&B3&B1&C3&C1)),2)*1Minutes:

=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*

Convert_Decimal(A3&A1&B3&B1&C3&C1)),4,3)*1Seconds:

=MID(Convert_Degree(Convert_Decimal(A2&A1&B2&B1&C2&C1)*

Convert_Decimal(A3&A1&B3&B1&C3&C1)),9,2)*1

If you know anything about VBA, you could probably adapt the UDF's to spit out just the Degrees portion, Minutes portion and Seconds portion. In other words, create multiple UDF's, one for each section of the overall answer.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hi DerbyDad, Thank you for the answers infact this left and mid functions are working fine.

If a value is single digit, if I increase number of characters the left function the output will come two chars.

it is not working for some values.

if any suitabe VBA code is there for this problem coluld you please provide me. this will be very helful.

thank you,

Sankar

sisrajsan@gmail.com

Do me a favor and save me some work. In your original post, you used your calculator to come up with the correct answer for a specific pair of inputs. With that information, I was able to come up with a solution that matched your example. As noted earlier, just leaving out a space results in an "answer" that looks OK, but is actually incorrect. Without your example input and output, I probably wouldn't have noticed the incorrect answer.

So, use your calculator again and post a few examples of pairs and the corresponding

correctanswers so that I have a target to aim for. Include combinations that my suggestions work for as well as combinations that don't work, pointing out which ones are which.In other words, help me help you by providing some real life examples - with the correct answers - so I can hone in on an solution that works in all cases.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll