Combining 2 columns to 1

  • Thread starter Thread starter jimbo_vr5
  • Start date Start date
J

jimbo_vr5

Hi

I have 2 columns A and B. In column A i have one word like "hello". In
column B i have a time "19:00" even though the column is pure
standard.

What i would like to do is combine these two columns to one column, so
that i have column C "hello 19:00".

I try to do this using the formula =A1&" "&B1 in column C, but when i
do that i get the result "hello 0,791666666666667" which is not what i
want.

How can i do this?

Best regards
Jimmy
 
It may be an international difference. Try using a semi-colon instead of a
comma.

=A1&" "&TEXT(B1;"hh:mm")

If you're not using an English version of excel, you may have to change =text(),
too.
 
Hi Dave

Same things happen with a semicolon in the formula.

What should i change =text() too?

Best regards
Jimmy
 
Hi Jimmy

The formula works fine for me.
Are you using US Excel or UK Excel or some other language version?
I think Dave was suggesting, that if you are using another language,
then as well as changing separator from comma to semi-colon, the TEXT()
function might have a different name.
 
And you could post the language of excel you're using. Maybe someone will know
the function name.

But you could try to let excel help.

Select C1 (or whereever the formula should be placed).
hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window.

Type this and hit enter:
activecell.formula = "=A1&"" ""&TEXT(B1,""hh:mm"")

If that doesn't help, maybe your language doesn't use hh:mm for hours and
minutes?????
 
This might help:

http://dolf.trieschnigg.nl/excel/excel.html

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

And you could post the language of excel you're using. Maybe someone will
know
the function name.

But you could try to let excel help.

Select C1 (or whereever the formula should be placed).
hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window.

Type this and hit enter:
activecell.formula = "=A1&"" ""&TEXT(B1,""hh:mm"")

If that doesn't help, maybe your language doesn't use hh:mm for hours and
minutes?????
 
Hi

That did the trick.

Apparently i run a danish version =TEKST(B1, "tt:mm")

Thanks for your help :)

Best regards
Jimmy
 
Just a curiousity question...

I understand how you can find the equivalent of =text() as =tekst(). But how
did you know to change the hh to tt?

Was it just an educated guess or was it something that excel helped with?

(I don't know much about international differences.)
 
Hi Dave

I understand that it might be a bit strange that i just came up with
tt ;)

The word hour in danish is time (not to confuse with the english word
time). So i took a guess that it would be the first letter of that
word like in english.

So nothing that Excel helped with. It was an educated guess, but with
a bit of logic behind it. But it was the answers i found here that
lead me on that path, so thanks a lot!

Best regards
Jimmy
 
I bet if you look at excel's help for =tekst(), you'll see other formatting
symbols, too.

(Well, I hope they'd be in there <bg>.)
 
Back
Top