combine text with a time function

Question

I need a worksheet function to combine a cell with text and a cell with a time into one cell eg:

I have in C1 Doom ( format = General )

I have in C2 2:38 pm ( format = Custom / h:mm AM/PM )

What I need is in C2 Doom 2:38pm

I use : Windows 10

     : Office 13

can some one help please ?

bill

Answer 1

Hi Rich

What sort of cell format do I use ,if any in your example above?

bill

Your original Column C would be formatted for time--any time format could be used. The whole column or range can be formatted that way even though you are entering text in some of the cells. That's ok. The column with the formulas is just formatted general. The time format for this entry is contained in the formula and the formula result is actually text since it is a concatenate formula.

Rich~M

Answer 2

Then the formula provided by our colleague is the one to use

=C1&" "&TEXT(C2,"H:MM AM/PM")

Answer 3

Hi Bill. I am an Excel user like you.

I have a question regarding your question. You say you have the time in C2, but you want "Doom 2:38 PM" in C2. Where is the time coming from? It either needs to come from another cell or to be inserted as a current time. If you are taking the time from an entry in C2 and entering the result in another cell like D1 you can use this:

=C1&" "&TEXT(C2,"H:MM AM/PM")

If you need to enter a current time at the moment that the data is entered you can use this:

=C1&" "&TEXT(NOW(),"H:MM AM/PM")

This will update the time anytime the formula is updated/re-entered, but since it is entered as text it will not update any other time.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 4

Hi Jeovany

Thanks for your time.

C1 and C2 change every few rows so the information in the cells change

every so many rows. eg:

C5 and C6 might be Rand and 4.00 pm

and so on down the page.

bill

Answer 5

So, there are still questions about your layout/data that affect the options that might be available.

The problem you have with what you say you want to do is that Excel cannot take data that is in a cell and use it in a formula that over-writes that data in the same cell. When you put the new formula in, the previous data is gone and no longer available. The formula must use data that exists at the same time in another cell.

Here is a possible scenario if this matches the layout of your data. If not, then there might be some other alternatives, but we would need to know the layout in order to go any further in helping you. This layout seems like it may match with your layout.

If your data is in groups of two as in Column C above, you could enter formulas in any column as in Column E above (formulas written out in Column F) to put the data in a separate column and hide the original column. Or, if you need the layout to follow a particular look, these formulas could be put on a separate sheet in Column C giving you the same appearance as if the two pieces of data had been combined in C2, C4, C6, etc.

In order to drag these formulas in the groups of two, select both formulas as in E1:E2 above and drag the fill handle down which will alternate the two formulas all the way down.

If your data doesn't stay with the alternating label/time example, we would need to have more information about how it is laid out to proceed.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M