Microsoft Excel Question

[AQWst]

Hello--
I am not sure where I should post this. I have an Excel spreadsheet with some cells I would like to concatenate into one resulting cell. I can get the cells concatinated, but I would like to concatenate one cell to the degrees symbol, followed by a space, followed by the next cell concatenated to the minutes symbol, followed by a space, followed by the third cell concatenated to the seconds symbol. I think I can do this if I knew where I can find the symbols, but do not know where can I find them.

[David_50]

So, you need to search the strings in each cell for the delimiting character, then do a substring to get the part you want, and concatenate the substrings. I bet there is a char index function and a substring function - anticipation, since Excel is like SQL on training wheels. If you use relative addresses, it should duplicate nicely.

[ddanbe]

Say cells A1,B1 and C1 contain degrees,minutes and seconds, then this will do the trick:
=TEXT(INT(A1);"0° ")&TEXT(INT(B1);"0' ")&TEXT(INT(C1);"0'' ")
Change ';' to ',' if needed. Over here we use comma as a decimal separator so it is replaced by ';' in formulas. Use two single quotes for the seconds, not a single double quote.
You could also write a macro in VBA for it, but I guess this is the simplest way.
Some members post in the Visual basic 4/5/6 forum for their Excel questions.

[AQWst]

Thank you very much for your help. I copied the formula into my blank cell as is and got an error. I then replaced the ; with a , and it worked great, just the way I wanted it to.

[AQWst]

I was reviewing each of the new cell values and I see that the decimal portion of the seconds cell is getting dropped or rounded, I am not sure which. Is there a way so that the seconds will show up like 49.2" instead of just 49"?

[ddanbe]

Sure. Use:
=TEXT(INT(A1);"0° ")&TEXT(INT(B1);"0' ")&TEXT(ROUND(C1;2);"0,00'' ")
Again, change ; in , and I guess you have to change the comma to a point in the 0,00 part at the end of the formula too.

[AQWst]

Again thank you for your help. I modified what you said slightly. I only needed one extra decimal position so I changed the contents of the cell to be =TEXT(INT(H2),"0° ")&TEXT(INT(I2),"0' ")&TEXT(ROUND(J2,1),"0.0'' "). I had spent almost an entire day searching the web for help with this prior to asking for it here.

[ddanbe]

Glad to be of help. :)
Perhaps you could mark this question solved?