Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fozeye
 
Posts: n/a
Default Editing text after concatenate

Hi,

I want to allow text editing after concatenation (is that a word?). So I
want to collate text entries from various cells on the worksheet and display
them in a single cell, so I have used the Concatenate formula. I can manually
convert the text to an editable format by copying the cell and pasting it
back into the same cell as "value" only. Does anyone know of an automatic way
of achieving the same result?

Thanks,


Fozeye.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Editing text after concatenate

Hi Fozeye,

Yes, "concatenation" is a word and it refers to the process of combining text from multiple cells into a single cell.

To allow text editing after concatenation, you can use the following steps:
  1. Select the cell that contains the concatenated text.
  2. Press F2 or double-click on the cell to enter edit mode.
  3. Place the cursor at the end of the text you want to edit.
  4. Press the space bar to add a space after the text.
  5. Press the backspace key to delete the added space.
  6. Press Enter to save the changes.

By adding and deleting a space, you are essentially converting the cell from a formula to a value. This will allow you to edit the text without changing the original formula.

Alternatively, you can use the "Text to Columns" feature to split the concatenated text into separate cells. Here's how:
  1. Select the cell that contains the concatenated text.
  2. Go to the "Data" tab in the ribbon.
  3. Click on "Text to Columns".
  4. In the "Convert Text to Columns Wizard", select "Delimited" and click "Next".
  5. Select the delimiter that was used to concatenate the text (e.g. space, comma, semicolon) and click "Next".
  6. Choose the format for each column (e.g. General, Text, Date) and click "Finish".

This will split the concatenated text into separate cells, which you can then edit individually.

Formula:
// No macro or Visual Basic code found in the text 
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Duke Carey
 
Posts: n/a
Default

If you are doing this all at once, then:
1) copy your formula to all the relevant rows/columns
2) select the cells with the formula
3) Copy
4) without moving the selection, right click and choose Paste SpecialValues

If you are doing it one set of entries at a time, then you either use a
macro or do it manually. You can record the macro, but be sure to tell it to
use relative references.

"Fozeye" wrote:

Hi,

I want to allow text editing after concatenation (is that a word?). So I
want to collate text entries from various cells on the worksheet and display
them in a single cell, so I have used the Concatenate formula. I can manually
convert the text to an editable format by copying the cell and pasting it
back into the same cell as "value" only. Does anyone know of an automatic way
of achieving the same result?

Thanks,


Fozeye.

  #4   Report Post  
Fozeye
 
Posts: n/a
Default

Thanks Duke,

I had thought of using a macro button, but wondered of it could just convert
to editable text using a recognised excel function, but perhaps not.

Thanks for responding.

fozeye

"Duke Carey" wrote:

If you are doing this all at once, then:
1) copy your formula to all the relevant rows/columns
2) select the cells with the formula
3) Copy
4) without moving the selection, right click and choose Paste SpecialValues

If you are doing it one set of entries at a time, then you either use a
macro or do it manually. You can record the macro, but be sure to tell it to
use relative references.

"Fozeye" wrote:

Hi,

I want to allow text editing after concatenation (is that a word?). So I
want to collate text entries from various cells on the worksheet and display
them in a single cell, so I have used the Concatenate formula. I can manually
convert the text to an editable format by copying the cell and pasting it
back into the same cell as "value" only. Does anyone know of an automatic way
of achieving the same result?

Thanks,


Fozeye.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I concatenate text that actually contains quotation marks? Excel Distress Excel Worksheet Functions 3 April 15th 05 02:23 PM
space between text strings with concatenate Jeff Excel Discussion (Misc queries) 2 March 3rd 05 07:54 PM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
concatenate text and date Tara Keane Excel Discussion (Misc queries) 2 February 11th 05 06:17 PM
concatenate a text string if two different cells contain the sam. Linda G Excel Worksheet Functions 5 January 17th 05 08:53 PM


All times are GMT +1. The time now is 05:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"