Removing spaces from text #4

I'm in excel and i have a bunch of text data that has an extra space at
the end of the text on the right hand side for each cell.  Is there any
easy way to remove this space?

0
lj1 (84)
4/12/2006 9:13:49 PM
excel 39879 articles. 2 followers. Follow

8 Replies
612 Views

Similar Articles

[PageSpeed] 41

Use the TRIM() function.

-- 
Kind regards,

Niek Otten

"lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com...
> I'm in excel and i have a bunch of text data that has an extra space at
> the end of the text on the right hand side for each cell.  Is there any
> easy way to remove this space?
> 


0
nicolaus (2022)
4/12/2006 9:18:59 PM
I tried using that function but the results still had the spaces.  I
used trip and then copy-paste-special values.  The result still had the
space.  Any other ideas?

0
lj1 (84)
4/12/2006 9:58:08 PM
I tried using that function but the results still had the spaces.  I
used trip and then copy-paste-special values.  The result still had the
space.  Any other ideas?

0
lj1 (84)
4/12/2006 10:05:11 PM
I tried that and it didn't work (I used trip and then
copy-paste-special-values the data, but the space was still there) any
other ideas?

0
lj1 (84)
4/12/2006 10:55:11 PM
Then you don't have spaces

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

-- 

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"lj" <lj@spu.edu> wrote in message 
news:1144877136.828236.248070@g10g2000cwb.googlegroups.com...
>I tried that and it didn't work (I used trip and then
> copy-paste-special-values the data, but the space was still there) any
> other ideas?
> 


0
Peo
4/12/2006 10:59:50 PM
Try to use this if you want to clean all the space in cell A4.

=TRIM(SUBSTITUTE(A4,CHAR(32),""))

Regards,
Dodong

0
4/17/2006 7:57:25 AM
On 12 Apr 2006 14:58:08 -0700, "lj" <lj@spu.edu> wrote:

>I tried using that function but the results still had the spaces.  I
>used trip and then copy-paste-special values.  The result still had the
>space.  Any other ideas?

Most likely you obtained the values from an HTML document and they are nbsp's
(non-break spaces).

To remove:

=SUBSTITUTE(A1,CHAR(160),"")


--ron
0
ronrosenfeld (3122)
4/17/2006 11:11:33 AM
Dodong,
Thank you so much for this. Saved me a big problem looking up a 16,00
record file and matching to a 1,560 record list.
Kind regards
David


dodong Wrote:
> Try to use this if you want to clean all the space in cell A4.
> 
> =TRIM(SUBSTITUTE(A4,CHAR(32),""))
> 
> Regards,
> Dodon

--
davidthegolfe
-----------------------------------------------------------------------
davidthegolfer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3180
View this thread: http://www.excelforum.com/showthread.php?threadid=53245

0
4/19/2006 10:15:39 AM
Reply:

Similar Artilces:

Remove AD
Dear all, What is the best way to remove AD services from an existing Exchange 2003 server? Thanks. You shouldn't. If Exchange is installed on a Domain Controller, it should not be demoted to a member server. You should install another Exchange server and move mailboxes, replicater public folders, et al. Then remove Exchange from the old server. Now you can demote it to member server. Install Exchange again and move stuff back. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "KA Kueh" <kka@ksm.com.my> w...

sending attachments in rich text format
This is a multi-part message in MIME format. ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit Can anybody tell me how I send an attachment with an e-mail from within Microsoft Office 2007 in rich text format please Wendy ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: image/gif; name="QMARK.GIF" Content-Transfer-Encoding: base64 Content-ID: <7D394D5C2EB043D9A973E27399E2B9B5@andyPC> R0lGODlhEwAfAPcAAP8ICP8PDv8VFf8dHP8kI/...

Outlook 2002 on Exchange 5.5 -- RTF to Plain Text issues
The owner of our company wants ALL email sent to him in Rich Text Format so he can use an abbreviation program he has that works with MS Word when replying. I created a document telling people how to change their settings for Outlook to ensure all messages are sent in Rich Text. My problem is that since he travels so much he wants to be a POP user on our server. If I send him a message from my PC directly connected to the Exchange server he receives it in RTF. If the President of the company (who is also a POP user) sends him a msg in RTF he receives it in Plain Text. He wants me to expla...

Excel Formulae #4
I've been trying hard to find a formula which would enable me (from one cell) to Add several cells and Subtract the answer from another cell (subtracting items of expenditure from a starting total). I could manage this with Lotus - but Excel does not seem to behave the same. Advice would be much appreciated. Thanks, Ken. One way: Say you wanted to subtract A1, A2, A3, J4 and N5 from L6: =L6-SUM(A1:A3,J4,N5) In article <173d01c3fbd6$3df0f300$a401280a@phx.gbl>, "KenS." <anonymous@discussions.microsoft.com> wrote: > I've been trying hard ...

Printing single labels directly from CRM 4.0
Hi! We are currently moving from CRM 3.0 to CRM 4.0 while at the same time introducing many new users to the system. One feature that we would like to have, partly to satisfy new users, would be to print individual account's or contact's addresses to a labelwriter. Basically, the idea would be that from any view you could select 1-n accounts or contacts, then click print label on the toolbar, and then the labels would print from your selected labelprinter. Especially when printing only one or a few labels, this approach would be preferable to printing eg through mailmerge. I'm n...

Attachments convert to text format
When I send a message with an attachment in Outlook XP some receivers claim that all they receive is a text mail. My default mail format is plain text. If I change it to HTML I fear some recivers would not see the content at all. How can I solve it ? Note: I change the format of a particular mail if it contains an attachment, but sometimes I miss to do that. Tork2001 wrote: > When I send a message with an attachment in Outlook XP some receivers claim > that all they receive is a text mail. > My default mail format is plain text. If I change it to HTML I fear some > recivers ...

copying text on sheet 1 to corresponding cells on sheets 2 to 5
I want the text entered in A8 on sheet 1(named Productivity) to automatically transfer to A8 on sheets 2 thru 5. I used the simple formula =Productivity!a8 in the corresponding cells but when there is no data in Productivity A8 I get a 0 in the corresponding cells. I would like the corresponding cells to remain blank if there is not text in Productivity A8. I am really new to excel, like only used it two weeks! Is there a simple fix to this? -- newbie at large! Hi A formula will always return 0 if it's refering to an empty cell. This formula will let the cell look empty if no...

publisher: 4 Millions Domains data with Category
Successfull Internet and Direct Marketing products on www.promotionsite.net * NEW * DOMUS Domains Toolkit Fall 2004 - Unique on the Net 4 Millions "Whois" Domains data with Expiration Date and Category*. Ultimate Version (October 2004) - Our best rate starts from US *$149*. A wonderful tool for Internet and Direct Marketing. Available in Basic, Advanced and Full Editions. It contains a domain database with 4 millions *FRESH* October 2004 New records .com, .net, .org. 4 Gigabytes MS Excel data zipped on CD-Roms/Dvd. Compression 3:1. MS Excel or Text tab del...

text in cell shows up as ####
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Text in cells in a coumn shows up as ####, apparently because there is a large amount of text. When I mouse over, the text shows. It also shows up in the formula bar. How can I copy and or print the text in these cell? <br><br>When I try to save these cells as text and transfer to Word, they continue to print as ###. text in cell shows up as #### <br> I am totally new to this whole environment! A new iMAC and this software. <br> However, in trying to learn how to use EXCEL, I found that...

Hide text box
I am trying to hide a text box on a subform if a check box is = to false. I am close in doing so, but not there yet. This is what I have In the AfterUpdate property of the check box I have the following: Private Sub RevisedGrant_AfterUpdate() If Me![RevisedGrant] = True Then If MsgBox("Are you sure you want to revise this grant?", vbYesNo) = vbNo Then Me![RevisedGrant] = False MsgBox "Grant will NOT be revised", vbOKOnly Forms![DataEntryFrm]![ItemSubFrm]![Total$ofItemsRequestedSubfrm].Form![Revised Total Approved].Visibl...

Figures locked in text format
I imported data from a web page and used the feature that allows me to post the info to Excel with the destination formatting of the file I am posting it to. However, when I try to manipulate the data, as I have done in the past with other data, it will not let me reformat the text to number- no matter what I do to it. I've tried everything! I want to be able to sum the column, etc. Give us an example of the data you are working with. And what have you tried to "do to it"? Regards, Fred "Marcia" <Marcia@discussions.microsoft.com> wrote in ...

List number of permutations for text nums
I've got 4 digit text nums in A1 down In B1 down, I would like to list the corresponding number of permutations, eg: 7777 1 1777 4 0044 6 2477 12 1234 24 Game for any formula, udf or other vba solution Thanks for insights Sub permutations() Dim I As Integer, J As Integer, Rng As Integer Rng =3D Cells(Rows.Count, "B").End(xlUp).Row J =3D 1 Range("A1").Select Do Until ActiveCell.Value =3D "" For I =3D 1 To Rng Cells(J, "D").Value =3D "'" & ActiveCell & Cells(I, &qu...

Removing gray shading in protected forms (drop down box)
Hello- I am creating some contract templates for my company's sales force using Word 2003. I am using the forms toolbar to insert checkboxes and drop down boxes from which to select deliverable specifications. The problem is that when a user selects an option from the drop down box, the gray shading remains. I know the sales teams HATE the gray shading because they don't think it looks very good when sending to customers. Is there a way to disable the shading when an option is selected? Thanks. You can remove form field shading altogether using the corresponding b...

Convert Text to Number on 4,508 excel files.
Hi all. I'm faced with a rather unique problem.I have a large amount of improperly formatted excel files. They all have various cells formatted as text, yet they contain numbers and perform calculations on the cells. I now need to get these in a proper state. What I need is: Conversion of all text boxes containing numbers over to proper format of number. Do it automagically. I don't relish the thought of opening each one individually and fixing it. Any thoughts, tips or ideas on this? It could possible if you process that works by Scripting . In Scripting engine 5.6 Filesystem...

Microsoft Query #4
I receive message SQL0104 when changing a column heading in Microsoft Query while using the Client Access ODBC driver to connet to an iSeries 400 server. I have been informed by IBM that this is a problem with Microsoft's Query in that it incorrectly quotes the new Column Heading name before sending it to the ODBC Driver . Is anyone aware of a update or solution? Thanks Nick Thanks everyone, I just located the following Microsoft Knowledge Base Article 298955, I followed the procedures below, and the SQL0104 error went away. 1. Quit all Office programs.. 2. Click Start, and ...

delete ghost spaces
Got this code.... Doesnt always work for some reason. Any alternate way to put this? (Or is something wrong with it?) Sub eat_spaces() Dim c As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each c In Range("G2:T1500") c = Replace(c, " ", "") Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub I'm going to guess some of your spaces are non-breaking spaces (usually acquired from webpage text), so try using this line of code i...

Error Message when viewing CRM 4.0 Reports
We recieve the following event error when attempting to access any report. http://<reportserver>/reportserver failed. Error: The request failed with HTTP Unauthorized 401 Any suggestions? This is a new install. "Chris Timms" <ChrisTimms@discussions.microsoft.com> wrote in message news:0E4DDBE2-58EC-408F-82E8-3B7EF531D392@microsoft.com... > We recieve the following event error when attempting to access any report. > > http://<reportserver>/reportserver failed. Error: The request failed with > HTTP Unauthorized 401 > > Any suggestions? Thi...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

This Document has been removed from Sales Order Processing History #2
Hi, We have imported data from GP Version 7.0 to GP Version 9.0. We did not upgrade it. All the history and all the transactions were imported into verions 9.0. Now I am trying to using Sales Inquiry from Inquiry >> Sales >> Transaction by Customer. Selected a customer, all details appeares including invoices, payments and other documents. But when I try to drill down on one SOP invoice I get above mentioned error. I checked the Sales Order Processing module, I found those documents and tried to drill down on them, it worked fine. But not from Receivable side. I have al...

Importing Unicode Text File
Hi, I have a CSV (comma seperated) text file. The text in this file is Unicode (UTF-8). I can open the file in Notepad and, if I use a Unicode font, see the characters are correct in my text file. However, when I try to open this in Excel, it gets mangled- -the Japanese characters get converted into jibberish. I've tried opening a blank file, then using "Import External Data", but the Japanese characters in the file still wind up getting corrupted. What's BETTER is to change the "File Origin" to something other than my default of Windows(ANSI) to someth...

text box outline
In my text box, I can click on the text and a square dotted blue line that surrounds the text. I can then click on some other text and the same thing happens. How can I remove the Blue line. It seems to be in the edit mode but I have even saved it and locked it (protect) mode. Still has to blue outline. Help! and Thanks The dotted blue line is to show you the Text Box boundary for when you don't have a printable formatted and View Text Boundaries are set to OFF. Does pressing the ESCAPE key hide them? But they should clear when you move the cursor elsewhere. If they...

Autofit rows
I am having an issue with autofitting rows in a spreadsheet of imported text in Excel 2003. There are no merged cells in the document, and none of the cells go over the 1024 character limit. When I select all rows and attempt to autofit the rows to the contents of the cells, some of the rows resize properly, and some leave one or several lines of white space below the bottom of the text in the longest cell. I can resize them manually, but this is data that is imported daily and runs to several thousand rows. If I widen the column where I am having the problem, once it reaches a certain ...