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.
AQWst -2 Light Poster
David_50 13 Junior Poster in Training
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 2,724 Professional Procrastinator Featured Poster
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 -2 Light Poster
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 -2 Light Poster
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 2,724 Professional Procrastinator Featured Poster
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 -2 Light Poster
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 2,724 Professional Procrastinator Featured Poster
Glad to be of help. :)
Perhaps you could mark this question solved?
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.