Merging 2 cells together and prefixing

[fuzzyjon]

Hi there

I hope someone can please help me with a problem that I have as shown below:-

OK, I have a telephone number which is split into two separate cells. The first cell is the dialling code (STD code) and the second cell is the actual number. These cells are next to each other e.g. A1 and B1.

So, A1 = e.g. 01302 and B1 = e.g. 820353

I want to merge them into 1 cell so that the STD code and number are together separated by a space.

e.g. C1 = 01302 820353

Furthermore I then need to prefix the C1 cell with a t: e.g.

C1 = t: 01302 820353

I hope that makes sense!

Thank you for looking and I hope someone can help.

Best wishes

Jon

[Duke Carey]

="t: "&cell1&" "&cell2

"fuzzyjon" wrote:

>
> Hi there
>
> I hope someone can please help me with a problem that I have as shown
> below:-
>
> OK, I have a telephone number which is split into two separate cells.
> The first cell is the dialling code (STD code) and the second cell is
> the actual number. These cells are next to each other e.g. A1 and B1.
>
> So, A1 = e.g. 01302 and B1 = e.g. 820353
>
> I want to merge them into 1 cell so that the STD code and number are
> together separated by a space.
>
> e.g. C1 = 01302 820353
>
> Furthermore I then need to prefix the C1 cell with a t: e.g.
>
> C1 = t: 01302 820353
>
> I hope that makes sense!
>
> Thank you for looking and I hope someone can help.
>
> Best wishes
>
> Jon
>
>
>
> --
> fuzzyjon
> ------------------------------------------------------------------------
> fuzzyjon's Profile: http://www.excelforum.com/member.php...o&userid=34378
> View this thread: http://www.excelforum.com/showthread...hreadid=541570
>
>

[John Michl]

try ="t: " & A1 & " " & B1

- John
www.JohnMichl.com

[Bondi]

Hi Jon,

One way could be:

=CONCATENATE("t: ",A1," ",B1)

Regards,
Bondi

[Beege]

fuzzyjon

="t: "&A1&" "&B1

that's a space after t: and between ""

Beege

"fuzzyjon" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there
>
> I hope someone can please help me with a problem that I have as shown
> below:-
>
> OK, I have a telephone number which is split into two separate cells.
> The first cell is the dialling code (STD code) and the second cell is
> the actual number. These cells are next to each other e.g. A1 and B1.
>
> So, A1 = e.g. 01302 and B1 = e.g. 820353
>
> I want to merge them into 1 cell so that the STD code and number are
> together separated by a space.
>
> e.g. C1 = 01302 820353
>
> Furthermore I then need to prefix the C1 cell with a t: e.g.
>
> C1 = t: 01302 820353
>
> I hope that makes sense!
>
> Thank you for looking and I hope someone can help.
>
> Best wishes
>
> Jon
>
>
>
> --
> fuzzyjon
> ------------------------------------------------------------------------
> fuzzyjon's Profile:
> http://www.excelforum.com/member.php...o&userid=34378
> View this thread: http://www.excelforum.com/showthread...hreadid=541570
>

[fuzzyjon]

Hi Duke

Thanks for the quick reply....

I just have one more question.

I need to send my data to a company but with only certain columns showing. Hiding them is not possible so I have to actually delete the columns that are not needed.

So, after I have merged the 2 cells together as you showed me, I need to copy the data from the merged cell to another cell so that it doesn't show #REF when I delete the original 2 cells.

I hope I'm explaining myself OK! Here goes another way.....

Once I have merged A1+B1 into C1 as you showed me, I only need to keep cell C1 (otherwise this will confuse the printer's software). But if I remove cells A1 + B1, cell C1 cannot function because I have effectively removed the data that the formula in C1 is looking at. Therefore, do I need to somehow copy the data in C1 (rather than the formula) to another cell so that I can remove A1 and B1 (and then C1) without causing a problem?

Phew!

Thanks

Jon

[Duke Carey]

After you've concatenated all these values, select all the formulas, copy
them and WITHOUT CHANGING THE SELECTION, use Edit->Paste Special->Values.
After that you can freely delete the original columns

"fuzzyjon" wrote:

>
> Hi Duke
>
> Thanks for the quick reply....
>
> I just have one more question.
>
> I need to send my data to a company but with only certain columns
> showing. Hiding them is not possible so I have to actually delete the
> columns that are not needed.
>
> So, after I have merged the 2 cells together as you showed me, I need
> to copy the data from the merged cell to another cell so that it
> doesn't show #REF when I delete the original 2 cells.
>
> I hope I'm explaining myself OK! Here goes another way.....
>
> Once I have merged A1+B1 into C1 as you showed me, I only need to keep
> cell C1 (otherwise this will confuse the printer's software). But if I
> remove cells A1 + B1, cell C1 cannot function because I have
> effectively removed the data that the formula in C1 is looking at.
> Therefore, do I need to somehow copy the data in C1 (rather than the
> formula) to another cell so that I can remove A1 and B1 (and then C1)
> without causing a problem?
>
> Phew!
>
> Thanks
>
> Jon
>
>
>
> --
> fuzzyjon
> ------------------------------------------------------------------------
> fuzzyjon's Profile: http://www.excelforum.com/member.php...o&userid=34378
> View this thread: http://www.excelforum.com/showthread...hreadid=541570
>
>

[fuzzyjon]

Thanks so much Duke.

You don't realise how much manual work you've saved me there!!

Have a great weekend.

Jon