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
594 Views

Similar Articles

[PageSpeed] 57

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:

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Money Central Portfolio #2
My print function has gotten corupted. When I print it flips to WORD / format and prints in a format that is 4x normal. ...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Statements #2
How are statements sorted? Alphabetically by name or by account number? Does anybody know? -- Any help is appriciated, Deb Mine are done alphabetically by name. -- Elizabeth M. "Deb" <Deb@discussions.microsoft.com> wrote in message news:D58FEF32-7509-473B-A554-6A48E02697CF@microsoft.com... > How are statements sorted? Alphabetically by name or by account number? > Does > anybody know? > -- > Any help is appriciated, Deb ...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

Outlook Express 6 Spell Check #2
I am using Outlook Express 6 on Windows XP, and I have a problem with spell check. When you send an email, it says "spell check has been haulted, do you want to send anyway" and if I ask it to spell check, it says an error has occured in spell check. All my spelling options are correctly set, so I don't know what the problem is. Kimberly McNabb <knmcnabb@hotmail.com> wrote: > I am using Outlook Express 6 on Windows XP, and I have a > problem with spell check. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Database #2
Hi I'm new to Outlook. I hear of an Outlook database in other threads. Is this synonymous with the ..pst or .ost files people mention? Does it corrupt often and if so are there tools to repair it? Regards Craig. There's a utility called ScanPST.EXE that will scan and attempt to repair the file. Yes, typically when a user says Outlook database they're referring to their PST file "Craig Roberts" <craigroberts@jkr.co.uk> wrote in message news:C4C8E141.211DE%craigroberts@jkr.co.uk... > Hi > > I'm new to Outlook. > > I hear of an Outlook d...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

host unreachable #2
hi, if i send a email with a wrong address or mispelled it, i don't get a message that the address, destination is wrong until 4 days later after being deletedj from the outgoi queue im using E5,5 sp4 thks Is that for all domains, even for Yahoo and Hotmail? Rick wrote: > hi, > > if i send a email with a wrong address or mispelled it, i > don't get a message that the address, destination is wrong > until 4 days later after being deletedj from the outgoi > queue > > im using E5,5 sp4 > > thks ...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

remove shop in HQ
Hi, We have 4 shops, and we would like to delete one shop data because the data at HQ of that shop is incorrect and mixedup. However the database at shop is correct. How can we delete shop at HQ and resynchronize data from shop to HQ? We would like to clean data at HQ and upload all data include transaction from shop to HQ. Any idea? -- Jeed ...

Attachment problems
Hi, Is there anyway to unlock outlook 2002's block on ".exe" files. I know these are Level1 blocks, but can't find a way to configure it. I have recieved an important program and need to run it. Thanks in advance Scott Try here: Opening .exe Attachments with the Outlook E-mail Security Update and Outlook 2002 http://www.slipstick.com/outlook/esecup/getexe.htm -- Nikki Peterson [MVP - Outlook] "Scott White" <ddltrade@hotmail.com> wrote in message news:OmVN4JVnDHA.1672@TK2MSFTNGP09.phx.gbl... Hi, Is there anyway to unlock outlook 2002's block on...

Closing Excel #2
I have inherited an App desigend by an ex employee Sometimes the program leaves Excel in the task manager The programe uses various routines and calling various functions that define Excel sheets etc (badly named) I'm finding it hard to follow (my 1st experince of .NET) is there a way to KILL any Excel instence when exiting the program. I have come across some posts about GC (garbage collection) like this System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWsheet) oXLWsheet = Nothing 'System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWs) oXLWs = Nothing oBook.Clos...

hidden transactions #2
I have a problem with account balances: the balance for checking is way off on the hiome page than in the account register view. When I tried to find out why, I got some greyed out transaction when I went from bill schedule through right click to payee. This transaction cannot be changed or deleted, but in total all such transactions made up the difference in balance. Any suggestions? Oscar -- Elvart In microsoft.public.money, elvigor wrote: >I have a problem with account balances: the balance for checking is way off >on the hiome page than in the account register view. When I ...

MS Office Excel97: Removing Page Breaks
How do I remove page breaks? For some unknown reason they started appearing on a spreadsheet recently. I see dotted lines dividing up the sheet and large "Page ...." in the background. According to an Office 97 book I have, I should go to Tools->Options->View-tab and set the Page Break option off. There isn't any. If I right click outside the page area, I see PG menu items but none seem to work. There's also a PB View in the View menu, but clicking on it doesn't seem to do anything. Weird. -- Wayne T. Watson (Watson Adventures, Prop., Nevada Cit...

All attachments are removed because deemed unsafe
Hello, Is there anything I can do in order to be able to receive attachments that are sent to me? I usually get photo's or word docs. Any suggestions will be greatly appreciated. U Which version of Outlook and what is the OS? They are probably being blocked by your Internet Security, Anti-Virus or Firewall setup. Try accessing 'help' for whatever program you have and see if there's anything for 'attachments, email attachments' or just look around for a setting to change. "U" <anonymous@discussions.microsoft.com> wrote in message news:1769401c4...

Exchange Server C Drive No free space
Hi All, Need some advise. The Exchange Server during certain times C Drive will not have free space. I have check that during that period there is a temp file which will increase in size and takes up all the disk space in C. The file will not have any extension (the file name ex. T232C or R408). When this happened, the Exchange MTA service will stop, users will not able to send email. I have rum Spyware scan, Virus Scan, Online Virus scan, all seems to be cleaned). Anyone experience this before? Thanks in Advance for the help. -- Danny Chieng Technical Engineer On Wed, 26 Jul 2006...

Stats/PivotTables #2
I have to produce stats from data exported as a CSV file from a piece of custom made software we use here. The data displayed in Excel looks like this: 01/04/2003 05:31 Dr JESSICA STROUDLEY 01/04/2003 08:56 Dr JESSICA STROUDLEY 01/04/2003 11:16 Dr JESSICA STROUDLEY 02/04/2003 11:42 Dr JESSICA STROUDLEY 02/04/2003 05:31 Dr JESSICA STROUDLEY 02/04/2003 08:56 Dr JESSICA STROUDLEY 03/04/2003 11:16 Dr JESSICA STROUDLEY 03/04/2003 11:42 Dr JESSICA STROUDLEY I have to produce a spreadsheet saying how many examinations were done by a particular doctor over a certain timespan. Each date li...

How did I break the cell display limit? #2
If there is anyone out there who can help with this, I would be most most grateful. Here's the facts: - I'm working in a MS Excel worksheet. - I am aware of Excel's 255-character-per-cell display limit. (As understand it, each cell can hold up to 32,000 characters, but wil only display/print 255 characters.) This is a major pain in th petunski, as most of my cells contain more than 255 characters Therefore, most of my spreadsheet shows cells that look like this ###############. I have to look in the formula bar to see the actua contents of each cell. - In cell C35, I have 1,05...

OWA Setup Questions #2
Hi All, I am attempting to install the newly released owaadmin tool. My IIS server currently hosts 3 websites all of which are published to their own urls via host headers all of the sites are using ssl. When Installing the owaadmin tool, it creates a new "Default Web Site" as the orginal "Default Web Site" has been renamed. The new "Default Web Site" contains only the files and path names pointing to "%systemroot%\Program Files\Outlook Web Access Administration" what I am doing is removing this new "Default Web Site" site using the I...

Removing last Exchange 5.5 server from Exchange 2K site
I have migrated all my Exchange 5.5 over to Exchange 2K and all has been working fine for about 2 months. I would now like to remove the 5.5 from the site and have tried to follow the instructions in KB-284148. From the EX 2K machine I start the Exchange Admin program and try to access the EX 2K machine itself to view the site and I ge the following error "The list of Microsoft Exchange server computers is not available because no Microsoft Exchange server computer could not be located" I get exactly the same if I try and do it on othe Ex 5.5 machine. This is the first problem I h...