Need help with gaps in text when pasting

Im new to excel and I have used Google a lot when setting up my spreadsheet and now Im experiencing a problem that I can't seem to find the answer to on Google. I was hoping the experts on this site can help me.

I've got a spread sheet that has macros setup to automatically prefill some cells with data. Im then trying to copy and paste this data to Notepad. Whenever I paste it, there are all kinds of large spaces and gaps in between words that I need taken out. Not taken out completely, but just one small space. I've try the TRIM function but that doens't seem to work. I've attach a screen shot of my spreadsheet and then what it looks like when I paste it into Notepad. Can someone help me? Thank You.


spreadsheet.JPGNotePad.JPG

Thanks for the suggestion but I really need a solution within Excel itself so I don't have to repeat these steps over and over in notepad. The data chances with every entry i do and I do around 30 different entry a day.

Originally Posted by Lylestyle89

Thanks for the suggestion but I really need a solution within Excel itself so I don't have to repeat these steps over and over in notepad. The data chances with every entry i do and I do around 30 different entry a day.

Hi lylestly,

I believe Ron is trying to isolate what it is that is causing the spaces. I believe he is right in that you are pasting tab delimited values from Excel to Notepad. The solution will be based around the delimiter if this is the case. I will try to recreate the issue and see what I can find.

Ron, I tried what you said and that did not work. It still had the gaps/indents in between the words.

Originally Posted by L-Drr

Or fill column G in your excel file with

=TRIM(A2&" "&B2&" "&C2&" "&D2&" "&E2&" "&F2)
in G2 and drag down

and only copy column G to your notepad file

This works other than when I do this to the row with the date in it, the date changes to this really long number with a decimal in the middle. Example of how the date looks....... Login Time: 41520.1392361111

Hi Lylestyle,

I was thinking about your issue and I was going to recommend the same solution as L-Drr, so I'm glad you've had a head start on it.

The solution to your dates is as follows:

For the date use this: &TEXT(B1,"mm/dd/yyyy") change B1 to the cell that contains your date.

For the time use this: &TEXT(D1,"hh.mm AM/PM") change the D1 to the cell that contains your time.

Example image of change in output:

Date: 9/3/13 3:20 AM Time: 9/3/13 3:20 AM Date: 41520.1392361111 Time: 41520.1392361111
Date: 9/3/13 3:20 AM Time: 9/3/13 3:20 AM Date: 09/03/2013 Time: 03.20 AM

When you paste it into the .txt file you should see the number and decimals are gone and the correct date formatting in its place.

kelleytr,

Im not sure if I done what you said correctly, but it didn't work for me. My date on my spreadsheet is now is cell B5. I have text data in A5 that says LOGIN DATE AND TIME: So this is the formula I typed cell N5 (which is the column I choose for all my trim formula stuff) =TRIM(A5&" "&B5&" "&C5&" "&D5&" "&E5&" "&F5&" "&G5&" "&H5) &TEXT(B5,"mm/dd/yyyy")

This is what the cell N5 looks like after I put that in. LOGIN DATE AND TIME: 41310.6666666667 Then I tried to paste that in Notepad and it still looks the same. The date is still in decimal format. Thanks you for all your help on this matter.

I'm happy to help

Is it possible for you to make an excel file with no sensitive information that I could look at?

What you typed appears correct. I might not be seeing something that looking at the spreadsheet would reveal.

This is going to sound crazy because I have NO idea what Im doing in Excel, but I fixed it. HAHAHAHA I just removed "&B5&" from the first part of the formula. Now it looks like this =TRIM(A5&" "&C5&" "&D5&" "&E5&" "&F5&" "&G5&" "&H5) &TEXT(B5,"mm/dd/yyyy")

That was pure luck on my part. HAHAHA Thanks everyone for helping me.

I also dont know how to change the status of this topic to SOLVED.

I think u hv only one issue left which is Login time stamp row So use this formula

=TRIM(A4)&TEXT(B4,"mmm. Dd, yyyy")&TEXT(B4,"hh:mm AM/PM")