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.

Edited 2 Years Ago by Reverend Jim: Moved to software development forum

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.

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.

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.

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"?

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.

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.

This question has already been answered. Start a new discussion instead.