Removing a space #2

I asked a question earlier
 need to remove an extra space in a cell (ex. cell a1 is 5543 and a space) it is set as general not a number, I have over 5700 rows, doing it manually is impossible can i do it? in a formula
However it did not work and I Do not understand typing visual basic code that well, I do unterstand formulas
Plesae help??
0
anonymous (74722)
5/24/2004 11:46:04 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
561 Views

Similar Articles

[PageSpeed] 21

Hi,

You might try something like this:
=VALUE(LEFT(A1,LEN(A1)-1))

If you don't care about value (number vs text) you could shorten the
equation to
=LEFT(A2,LEN(A2)-1)

Good luck

Best regards,
Kevin


"REmoving a Space" <anonymous@discussions.microsoft.com> wrote in message
news:EDD27F60-4868-414A-A773-5D32DACFC0B1@microsoft.com...
> I asked a question earlier
>  need to remove an extra space in a cell (ex. cell a1 is 5543 and a space)
it is set as general not a number, I have over 5700 rows, doing it manually
is impossible can i do it? in a formula?
> However it did not work and I Do not understand typing visual basic code
that well, I do unterstand formulas.
> Plesae help??


0
nospam710 (114)
5/25/2004 12:31:32 AM
This must be one of those many  scattered postings of yours
that someone mentioned. .

You will not get much out of newsgroups if you do not stick to
one thread,  nor will you get much if you don't use your name
(first and last) when post..

You also have spent more time saying the macro doesn't work
you don't want to learn macros,  and you don't want to type then
you would have spent learning how to use a macro with the
information already supplied.

It appears that rather than doesn't work that
you did not install the macro, so how can you honestly say it
does not work if you didn't install it..

You say it's too much to type.
You don't have to type much of anything you copy and paste.

Once you've installed the macro  it would take you less than
1/2 minute from start to finish to change 2000 cells the next time..

You cannot  use  simply  Ctrl+H  (replace)  to remove the space or
char(160) they have to be reentered afterwards and that is what the
macro will do for you all at once.   All of the other solutions
which you did not really try would take a whole lot longer
than using a macro and there is no cleanup involved with
getting rid of the extra column  if you used a macro instead.

Also formulas that simply strip the rightmost character could
put your data at risk if some don't actually have an extra space.
You'd be much safer with a formula like
   =TRIM(SUBSTITUTE(D2,CHAR(160)," "))
but then after using the fill-handle to copy down or the
name box  and  Ctrl+D to copy down you are only 1/3
of the toward completion there is a lot more to do afterwards.
You are much better off with a macro.

So to repeat,  instructions to install and use a macro
   http://www.mvps.org/dmcritchie/excel/getstarted.htm
the TRIMALL macro
   http://www.mvps.org/dmcritchie/excel/join.htm#trimall
use of the fill-handle (if you don't use the macro)
   http://www.mvps.org/dmcritchie/excel/fillhand.htm

HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"REmoving a Space" <anonymous@discussions.microsoft.com> wrote ...
> I asked a question earlier
>  need to remove an extra space in a cell (ex. cell a1 is 5543 and a space) it is set as general not a number, I have over 5700
rows, doing it manually is impossible can i do it? in a formula?
> However it did not work and I Do not understand typing visual basic code that well, I do unterstand [understand] formulas.
> Plesae [Please] help??



0
dmcritchie (2586)
5/25/2004 4:38:56 AM
Reply:

Similar Artilces:

Money 2003 restoring a backup asking for a password that does't ex #2
I have microsoft money 2003 at my work and would like to have the same data there in my home. So i made a backup, put on a pendrive and brought it to home. Installed microsoft money 2003 in my home and tried to restore this backup. Money 2003 insists asking a password that does not exist in the original backup file. I already tried to use the repair tool and didn't work. So, what i have to do to make the backup work and does not ask a password that does not exists? Thankyou sorry about the spam, i was using the google chrome and it bugged, sorry again about the tripple post :( "...

forwarding in Exchange 2003 #2
I configured the server (Exchange 2003) to forward a user��s emails to test@xyz.com. It is very strange that all messages sent from xyz.com are rejected (please see the sample NDR below), there is not problem to other domains. test@xyz.com did not receive the message while the envelope recipient (admin@abc.com) did. Does anyone know how to solve this? Any help would be appreciated. Thanks. SC -------------------------------------------------------------------------------------------------------------------------------------------- Your message To: admin@abc.com S...

Disaster Recovery for CRM 1.2
Does anyone have any documentation on the steps to backing up CRM 1.2? We want to make sure we are backing CRM 1.2 up correctly if the server should go down. We were unable to find any documentation on Microsofts website for a step by step backup solution. Right now we are just backing the CRM databases using the scheduler in SQL; is this ALL that is required and if so do we backup ALL of the databases or just certain ones? The owners of the company want to make sure we can restore ALL of the data if needed. -- Thank You, Troy Hi Troy Microsoft have included details of backing...

2 Questions
Hi I run Win 2K with Excel 2K Question 1 I have some VB code in the "Sheet 1" module of my excel file. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column <> 12 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub I would like this code to run automatically whenever the file is opened. (I am assuming that this would remove the enable/disable dialog box option when opening the file) Can someone tell ...

getdlgitemtext #2
Hi, I meke code from example behind, and not working corectly, buffor "String1" they are no my text in IDC_EDIT1 it is a somthing wrong this bufor, may please send me good idea. void CDialogPASS::OnBnClickedButtonDp2() { CString String1; GetDlgItemText(IDC_EDIT1, String1); } mike 1010 Is IDC_EDIT1 an actual control? I've seen this fail when the control ID changes due to editing of the dialog. joe On Fri, 30 Jun 2006 17:44:33 +0200, "1010" <mig1010@wp.pl> wrote: >Hi, > >I meke code from example behind, and not working >corectly,...

missing buttons #2
I just installed the new Office 2003 edtion. I went into e-mails and my send button and now my paper clip button are missing. I've checked in where you can add buttons but they're not in their either. What can I do to find them. Are you using Word as your e-mail editor? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:4E889BAD-E127-4AF8-BC98-7AB117D0B90B@microsoft.com, Sharon wrote: > I just installed the new Office 2003 edtion. I went into e-mail...

Multiple Outlook #2
I imported an Outlook 2000 pst file into Outlook 2003. Now when I open Outlook my Personal Folders appears 4 times in the left hand "All Mail Folders" When I close Outlook it gives me an error and msvbvm60.dll is mentioned. Any help on how to correct this issue would be greatly appreciated. Mick ...

queue stuck #2
HI... i ahve exchange 2003 and i am facing problem that i am getting lot junk email and all messagess are stuck in queue ..after that my server hang so i need to restart server and clear the queue before restart it does't allow me to clear the queue ... pls help me .. tejal ...

Trouble sending emails #2
I am recieving emails, but every time I try to send one I get an error message saying, "The server does not support the required HTTP methods. Ther server responded 'Not Implemented'." I have no idea wht that means or what I need to change. Help! On Thu, 30 Dec 2004 15:05:06 -0800, Tina wrote: > I am recieving emails, but every time I try to send one I get an error > message saying, "The server does not support the required HTTP methods. Ther > server responded 'Not Implemented'." I have no idea wht that means or what I > need to change. ...

hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....

Cannot open assigned task in Outlook 2003 #2
I am using Outlook 2003 on a XP Home PC. When a co-worker creates and assigns me a task, it comes into my Inbox and I can see it in my preview pane, but I cannot open it when I double-click it and I cannot accept or decline it thru the preview pane. My co-worker has Outlook 2003 on a XP Pro PC and has no problem opening and accepting tasks from me. By the way....when I attempt to open it, absolutely nothing happens that I can see....no error, no change of screens, nothing! I have ensured that when the co-worker sends the task, that they do NOT have "text only" set for me. THANKS...

display name #2
hi i have this problem: when i send a mail massage from an application by exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and not display name (Customer Care) how can i resolve it? Thanks An application should allow you to specify display name for sender. Does it send via SMTP or MAPI? Matrix_pd wrote: > hi > i have this problem: when i send a mail massage from an application by > exchange 2003, in outlook i see the e-mail address (xx@mydomain .com) and > not display name (Customer Care) > > how can i resolve it? > > Thanks -- Please re...

double lookup #2
In Excel2000, I need help with the following formula: my data is arranged like this ( I can rearrange if needed).. joe pete sue rating 30 10 5 A 20 5 3 B 10 2 1 C I then have values for joe, pete, sue. I want to look up the name and then give me the rating. The numbers may not be exact. For example, I want the output to look like this ( with the formula looking up the rating) name amount rating (formula needed) joe 11 C pete 7 B sue 1 C Can someone please help me with the formula to fi...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

Attachment Size #2
Hi ! Is it possible to block the adding of attachments in an=20 outlook client ? Or block the adding for attachments that=20 are bigger than 2 MB for example ? Can i get an error=20 Message or something ? (I think about an GPO or Registry=20 change or something else....) I have Users around here with Pop3 Outlook 2000 and=20 Outlook 2003 Clients. The Problem is that some of them are=20 trying to send Mails with Attachments with 300 (three=20 hundred!!) MB or something else like that.... Because they=20 don=B4t look an the size of their attachments. So the Mail is going through our WAN over ...

Removing hidden mailto:s from Excel?
I received a spreadsheet attachment yesterday and found a mailto:(my address) embedded in a cell under a text string. It was evident in a tooltip that appeared when cell was hovered-over. I told the sender who now says that many of her spreadsheets - used to handle group memberships - have such hidden addresses. What terms would she use to search for these pests, either in My Docs or on opening each sheet? ...

Covert v. 2000 -> v. 2.0
Heelo All after serious cooruption we recover dmb file with Ontrac tools to v. 2000 but application are for v 2.0 are is possible conversion to v. 2.0 thanks Ervins ...

How to insert a URL with spaces into message body
Is it possible to insert a URL containing a space into a message body from a mailto hyperlink without using brackets? For example: <a href="mailto:jackblack@microsoft.com? body=<http://www.micro% 20soft.com>">jackblack@microsoft.com</a> This link will insert the URL '<http://www.micro soft.com>' (with a space between the 'o' and the 's') into the message body. However, the brackets will be visable too. If I remove the brackets, only the 'http://www.micro' portion will be hyperlinked. The 'soft.com' portion wi...

Dead space at end of column
I am working on a multi-paged document that is legal-sized, landscape orientation with three columns on each page. At the end of page two, I have a recurring problem with the cursor jumping to page 3 about two-thirds of the way down the column. I cannot type in that area. Displaying formatting characters reveals nothing to show that there is a problem there. This does not happen in any other location on the document. Check the jumping paragraphs to see if many of them are formatted as "Keep with next" and "Keep lines together." Or perhaps one as "Page b...

Changing password #2
Hi, If I change a domain user's password, will that user's Exchange Server 2003 mailbox's pssword be changed too? I mean, does Active Directory share the same password with Exchange Server 2003? Thanks! Exchange never had passwords separate from domain, not even in Exchange 4-5.5 era. So you would change just one password, which belongs to AD account. Michael wrote: > Hi, > > If I change a domain user's password, will that user's > Exchange Server 2003 mailbox's pssword be changed too? > I mean, does Active Directory share the same password > w...

removing history
Hello If you remove a customer's transaction history, period history, journal history, and distribution history, can you then delete that customer even if they have previous-year transactions? Thanks! childofthe1980s How can the customer have prior-year transactions if you remove the history? -- Charles Allen, MVP "childothe1980s" wrote: > Hello > > If you remove a customer's transaction history, period history, journal > history, and distribution history, can you then delete that customer even if > they have previous-year transactions? > &g...

2 fonts in Chart title in E2007
We are tranferring code from 2003 to 2007 and noticed a change in how VBA reads the following line: ActiveChart.ChartTitle.Characters(5,2).Font.Color = vbred IN 2003, it reads it correctly coloring letters 5 & 6 red. in 2007, it colors the whole Chart title red. It seems that regardless of the start and length I pass into the characters function, the formatting occurs to the entire chart title, not just the characters I wanted. Even recording the VBA in 2007 yields no help as it cannot record the partial font change, even though the GUI allows it. IN 2003 you can reco...

commissions #2
Can I make a mass change to the item properties to in the DB to allow commissions to be paid on all items.My items are currently set not to allow commissions. -- billr Billr3, Maybe, which method did you want? Did you realize that the default setting is to pay commission by sales rep? -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Ret...

User defined fields #2
Entered user defined fields the way I wanted, however, the software has now put it all in ascending order and there are no sort options available. Any way to correct without having to do it all over? Put in ascending order where?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "Darcy W." <Darcy W.@discussions.microsoft.com> wrote in me...

Removing Hyphens from a Number
I have a huge database of numbers (several thousand) with hyphens in them and I want to remove the hyphens in some automated fashion. For instance: 42-291-32455 change to 4229132455 Does anyone know of a way to do this? Thanks. --- Message posted from http://www.ExcelForum.com/ Use a formula to put number in an adjacent cell =SUBSTITUTE(A1,"-","") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Trebor249 >" <<Trebor249.zxxy0@excelforum-nos...