Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to copy and paste text into cells with numbers?

0

Hello TeachExcel,

I have been watching your tutorials on how to extract text from cells. This is helpful but I am still having some issues which I think can be solved with some tweeking of the formulas.

I have a row of numbers going horizontal on my spreadsheet that I want to remain unchanged. Just keep the data as is. What I want to do is copy and paste text or fill in the cells with particular text before and after this number.

The way I want to have it appear in the cells is: Counts_at_5.8633_Degrees_Two_theta_Detector

So I want to have everything before the number and after the number applied to each cell that has a different number in the middle.

For example, I have Cell DM1 that says: Counts_at_5.8633_Degrees_Two_theta_Detector

The next cell, in Cell DN1, I just have 5.8800.

I want to copy and paste the text "Counts_at_" and "_Degrees_Two_theta_Detector" to DN1 without changing the number, 5.8800 to appear as:

Counts_at_5.8800_Degrees_Two_theta_Detector  and for each of the remaining cells with data. 

Is there a formula to copy and paste just the text before and after the number for each of my cells that has data? 

I have not been able to find a tutorial for this. I would greatly appreciate any help or suggestions

Thanks.  

excel
posted by: mguzusgs55 on Thu Feb 3, 2022 at 4:40 pm
Answer
Discuss

Answers

0

Hi Mario and welcome to the Forum

You can use a Custom number format for the cells to achieve what you need. 

In the attached revised file I've done that for just cell DN1 which appears to be "Counts_at_"5.8800"_Degrees_Two_theta_Detector" but its value is actually still 5.880017755.

For DN1 (or multiple cells), do this:

  1. Select cell(s)
  2. Right click, pick Format Cells...
  3. Click Number tab then Custom (under Category:)
  4. In Type, paste this (including quotation marks):
    "Counts_at_"0.0000"_Degrees_Two_theta_Detector"
    (Sample will show what the user will see, number of zeroes after the decimal point -in bold above- sets how many decimal places are seen)
  5. Click OK

You can use Format Painter to apply that to other cells (or the vaious Fill commands)..

You probably need to use Word Wrap for the cells too so you get column headings which legible but aren't really wide. I've added a second file below (if you want narrow columns) where a 45 degree Alignment is applied to row 1 (assuming that is your headers).

Hope this helps.

posted by: John_Ru (rep: 6152) on Fri Feb 4, 2022 at 4:08 am
Discuss

Discussion

Hi John_Ru, thank you very much for your help and advice. That worked very well. 

I applied that to all my cells and its exactly what I need to show. 

The next issue I have now is word wrap. So, my data just shows number signs in the cell, ####. How can I show some of the text without resizing the cell? 

That is the main problem I have now is showing what is in the cell without resizing. If I wrap text then I have to make the cells larger. 

Do you have any advice on how to do that without resizing the cell? 

Thank you very much. I appreciate the help so far! 
mguzusgs55 Feb 4, '22 at 12:09 pm
Mario

You get #### when the cell is too small to display the format. Your main choices are to use Word Wrap (and increase the height of the row), reducue the font size (which may make it difficult to read, sececially when prints or change the alignment. I think that might be a good choice for you  and will add another file to my Answer. To do that you:
  1. Select row 1
  2. Right click choose Format Cells...
  3. Pick Alignment tab In Orientation, move the "hand"/red dot up to 45 degrees (say)

You can then select all columns, double click on the dividing line between two columns- this autofits the width to the contents of the selected columns. Column DN is highlighted yellow in the second file to show that (and that the rows below are hoprizontal as normal so you can enter values).

I know this is your first question but please note we don't normally allow question after question under the same topic. Also. please check the Rules and note that (if I answered your question) you should mark the Answer as Selected (for the guidance of others / to help my reputation).
John_Ru (rep: 6152) Feb 4, '22 at 12:59 pm
Mario. Did you try the second file? With no comment from you for a week, it feels like I may have wasted my time... (but you're not alone in not communicating after an answer has been provided) 
John_Ru (rep: 6152) Feb 11, '22 at 10:03 am
Any comment on the extra help above, Mario?
John_Ru (rep: 6152) Feb 15, '22 at 9:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login