trim spaces in a cell

How do i trim empty spaces in a cell containing an invoice number but the 
length of the invoice number in column B is of dynamic length?

For example,
Column B
123 4567-------->1234567
987 3------------>9873
5 55------------->555

Thanks.


Ringo Tan
0
ringotan (14)
8/30/2004 8:47:06 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
619 Views

Similar Articles

[PageSpeed] 36

Ringo

If there is only ever one space then the worksheet function below should 
work

=REPLACE(A1,FIND(" ",A1,1),1,"")

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"ringo tan" <ringotan@discussions.microsoft.com> wrote in message 
news:F87414FB-FCE8-4EB9-B086-B17C07251618@microsoft.com...
> How do i trim empty spaces in a cell containing an invoice number but the
> length of the invoice number in column B is of dynamic length?
>
> For example,
> Column B
> 123 4567-------->1234567
> 987 3------------>9873
> 5 55------------->555
>
> Thanks.
>
>
> Ringo Tan 


0
8/30/2004 8:58:03 AM
Hi Tan,

You can do this manually using the find/replace function 
on the edit menu.  Highlight the cells you want to change, 
click Edit/Find....select "Replace"...type a space in the 
top box and nothing in the bottom box..then "replace all"

HTH,

Don


>-----Original Message-----
>How do i trim empty spaces in a cell containing an 
invoice number but the 
>length of the invoice number in column B is of dynamic 
length?
>
>For example,
>Column B
>123 4567-------->1234567
>987 3------------>9873
>5 55------------->555
>
>Thanks.
>
>
>Ringo Tan
>.
>
0
anonymous (74721)
8/30/2004 9:05:34 AM
One way:

=SUBSTITUTE(A1," ","")

Eric


"ringo tan" <ringotan@discussions.microsoft.com> schreef in bericht
news:F87414FB-FCE8-4EB9-B086-B17C07251618@microsoft.com...
> How do i trim empty spaces in a cell containing an invoice number but the
> length of the invoice number in column B is of dynamic length?
>
> For example,
> Column B
> 123 4567-------->1234567
> 987 3------------>9873
> 5 55------------->555
>
> Thanks.
>
>
> Ringo Tan


0
Eric
8/30/2004 10:04:13 AM
A sub to do all

Sub TRIMEM()
For Each C In Selection
C.Value = Application.Substitute(C, " ", "")
Next
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"ringo tan" <ringotan@discussions.microsoft.com> wrote in message
news:F87414FB-FCE8-4EB9-B086-B17C07251618@microsoft.com...
> How do i trim empty spaces in a cell containing an invoice number but the
> length of the invoice number in column B is of dynamic length?
>
> For example,
> Column B
> 123 4567-------->1234567
> 987 3------------>9873
> 5 55------------->555
>
> Thanks.
>
>
> Ringo Tan


0
Don
8/30/2004 12:58:01 PM
Reply:

Similar Artilces:

Stepping in to a cell
Right, this might sound like a really basic question but I can't find the answer anywhere. How do I step in to a cell with data in it without either double-clicking or clicking on it and then clicking in to the text bar at the top of the page. I often use excel without the mouse when I am doing stuff and I cannot find any keyboard hotkeys to add or amend data in a cell without using the mouse. I can replace the data by simply beginning to type, but I can't seem to add to what's in there or edit it. Any help would be much appreciated. Thanks Use F2 to edit a cell. ...

two lines in a cell
I am trying to get multiple lines to display in a cell without merging . I have to display text such a 1. Hello my name i 2. Duncan LeBlan on two lines in a single cell. The cell width is long and currently I need to insert the appropriate spaces to have the second line of text move down. The issue arises when I print as the display in either Normal view or Print Preview shows that the text is on two lines, but it prints with parts of the second line on the first line Is there any command or function that can be used to allow me to get 2 or 3 lineds of text formatted on different lines ...

DataGrid control displaying blank first cell when bounded with ADO
I am preparing an MFC based dialog application using Visual Studio 2003 VC++ IDE. On my dialog, I am having DataGrid (DataGrid ActiveX control - Ver 6.0 (SP5)) and ADODC (Microsoft ADO Data Control - ver 6.0 (SP 4)) controls. The ADODC control is connected to an Microsoft Access Database using Jet.OLEDB4.0 provider. And DataGrid is bounded to the ADODC. The dialog box displays both the control, with the data from the table too. However, the first cell (1st row and 1st column) in the DataGrid control is always shown as blank. And when the Dilaog box is closed, an exception is thrown "Unh...

Macro that will add multiple emails based on a range of cell values
I need a macro that will basically look at one column and if it says =93Yes=94 put the email address in the email column in the .bcc. All the email addresses need to be in one email by the way. Here is what my data looks like starting in A4: Yes Job Title email1@work.com No Job Title email2@work.com Yes Job Title email3@work.com No Job Title email4@work.com The list of emails is going to be variable too from month to month. Thanks for the help, Tyson See http://www.rondebruin.nl/sendmail.htm Choose a Outlook example and clik on the tip link http://www.rondebruin.nl/mail/tips2.htm -- ...

Audio Trim
Greetings from Downunder In PPT 2010 we have (at long last) the ability to trim, fade in and fade out audio tracks. I have a track that needs the first 7 secs to be removed and I can overcome this by advancing the green slider to the right. But that 7 secs of audio is still there, just not being played. My question is -- can that 7 secs be REMOVED from the audio timeline permamently. All help always appreciated. /carl In article <es7nH7bjKHA.1824@TK2MSFTNGP04.phx.gbl>, Carl wrote: > Greetings from Downunder > In PPT 2010 we have (at long last) the ability to tri...

Can I retain functions from a previous cell when inserting a new r
I have an Excel spreadsheet. I need to insert a row, but have a column with SUM functions going down each row. Can I insert the row and have the SUM function automatically entered in the new row created? Hi see: http://www.mvps.org/dmcritchie/excel/insrtrow.htm -- Regards Frank Kabel Frankfurt, Germany "Philobr" <Philobr@discussions.microsoft.com> schrieb im Newsbeitrag news:B5E5E5B1-C5EA-446A-A43F-017BB67F6773@microsoft.com... > I have an Excel spreadsheet. > I need to insert a row, but have a column with SUM functions going down each > row. > Can I insert the ...

Trim function #2
How can I have displayed the figures (cells) used, when I run a Trim function. Hi not really sure what you're rying to achieve. Could you give an example? >-----Original Message----- >How can I have displayed the figures (cells) used, when I run a Trim function. >. > When you use the TRIM Worksheet Function by itself the result is displayed in the cell. =TRIM(B1) If you want to know what B1 actually contains, you could use CODE for each byte, but easier would be Chip Pearson's Cell View -- http://www.cpearson.com/excel/CellView.htm For a maco to trim all cells i...

Formating pivot table cells.
Hello, I'm having problems with preserving format of a pivot table. That's how it happens: - I clear all the filters - change e.g. font size of the first column - apply one of the filters - the format changes but it looks like these are random changes - one cell has one format, the other has different, one cell is alligned to the left, the other not and so on. I tried to apply "preserve format after update" (it may sound different, it's just my translation from polish) function but it both cases formating looks random. I'd like to set my format and I want this for...

How do I remove spaces
I have data as such - (space)number(space) How do I remove the spaces via a formula or macro? I have 40,000 entries so I would rather not do it manually. thanks Hi If you have all your entries in one column, use the TRIM function. Let's say you have your data in Col A, then in Col B enter the formula = TRIM(A1). That will remove the spaces, but leave your numbers as text, not numbers -- j.kasselman@atlantic.net.remove_2nd_at. Stilfontein, Northwest, South Africa "lovebaby" wrote: > I have data as such - (space)number(space) > > How do I remove the space...

freeze cells #3
that will freeze the whole columns I need it should be frized only til row 3 Frank Kabel Wrote: > Hi > move the cursor to cell D4 and goto 'Windows - Freeze Panes' > > -- > Regards > Frank Kabel > Frankfurt, Germany > > "shlomo" <shlomo.1e4id9@excelforum-nospam.com> schrieb im Newsbeitrag > news:shlomo.1e4id9@excelforum-nospam.com... > > > > Is there any way I can make the *freeze panes * on a few cells, (let > say > > from column a till column c, and row 1 till row 3). it should be > like a > > box that is...

Formatting cells in Excel
Help! My ability to format a cell in Excell has suddenly disappeared! Any date I input will only show the serial number format and I cannot get it to change. What can I do? Sounds like you are in "View Formula" mode. Hit CTRL + `(above Tab key) to toggle off/on. Gord Dibben MS Excel MVP On Fri, 16 Jun 2006 11:30:02 -0700, jdcc63 <jdcc63@discussions.microsoft.com> wrote: >Help! My ability to format a cell in Excell has suddenly disappeared! Any >date I input will only show the serial number format and I cannot get it to >change. What can I do? Gord --...

Can I split 1 cell into 2 cells in Excel?
I am working in a spreadsheet where I frequently use the filter and sort functions. I want to split one cell into two in one column, I don't want to use two columns and merge the rest of the cells as this upsets the filter/sort functions. Try this.... Highlight the required cells you want to split and DATA > TEXT TO COLUMNS > NEXT then enter the parameter that you want to split by (colon, space, etc) FINISH "Chalky" wrote: > I am working in a spreadsheet where I frequently use the filter and sort > functions. I want to split one cell into two in one column...

How can I wrap text within an Exel cell?
I am using Microsoft office 2003 Exel, and I wish to type text on several lines within a cell in order to limit the width of the cell. Is there any other way of doing this than merging cells? even i can answer that one click on the cell (or row) that you want text to wrap in, then click on format, then alignment and ick the wrap text box "Tecra" <Tecra@discussions.microsoft.com> wrote in message news:87C22626-5473-478F-9B00-59B3703F9D84@microsoft.com... > I am using Microsoft office 2003 Exel, and I wish to type text on several > lines within a cell in order to limit ...

Disc space occupation
Hello. I'm using Excell 2003. I have scanned 10 pictures to JPG format. Each below 200 kb My sheet takes up only 250 kb when it's empty for pictures. When I import the 10 scanned pictures I would expect the worksheet to grow up to a little less than 2.5Mb. Would'nt that be a fair assumption? Both MS-Word and PowerPoint does so. However my worksheet grows to 29Mb. !!! Using gif and compressing the pictures inside the document does not work with good result. I want to be able to send the worksheet by email, and also work with it without waiting to long each time I'm saving. Wha...

Extra spacing in cells that are wrapped
In certain cells of my Excel spreadsheet, when I go to Print Preview it appears as if there is a blank line inserted below the text. The formatting on all cells in this column is: wrapped text, general horizontal alignment, top vertical alignment, with text direction set to 'context'. I have tried changing the formatting to various options, as well as placing my cursor at the end of the text in the cell and pressing 'delete' to remove extra spaces. The information looks fine in normal view, however in Print Preview it looks like an extra line is inserted. It does print w...

Can I change the "cell" size in the style gallery
I've created a template and the styles that I want to apply, and I've restircted the available styles to those that I want in the document. What I couldn't restrict, I hid. The result is a list of "only" 24 available styles; of those, there are 8 or 9 that would be nice to have easily available. But I don't want to have to rember hot keys for individual templates. Is there a way to reduce the size of the cells in the Quick Style Gallery, eliminate the sample text, and leave only the abreviated alias? If not, is there an easy way to replace t...

Trim
I would like to know how to trim the data from one column? Example: From: 010203-04/02/05 to 04/02/05 From 010203-04/02/05 to 010203. Please anybody let me know how to us function or inpu formula? Thank you! -- Select column, do data / Text to Columns / delimited / in 'other' box put a hyphen - Hit Next and then lose whichever column you don't want by choosing the 'do not import' column option, or if you want both then just hit Ok till done. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pr...

How to calculate (generate) a cell reference
I need a way to generate a cell address (row,column) from the value in another cell and be able to use the contents of that in a calculation. Here's my situation: I have a table of mileage readings for my car taken at odd intervals. Note that the "[Row]" column is the actual row number in the spreadsheet and not part of the data. It is included because I want to reference it later. [Row] Date Odom Miles Days Mos Yrs M/Dy M/Mo Mi/Yr 13 3/13/05 48,041 1,141 50 1.64 0.14 23 695 8,335 14 7/10/05 50,922 2,881 119 3.91 0.33 24 737 8,843 15 10/29/0...

Expression for Blank Spaces
I have an Excel line graph that I update weekly. Column "A" with the weekly date, and "B" with the data. There are two more columns in the sheet, "C" with an 'if' formule that depends on the data in Col "B" and "D" with a weekly percentage increase of an index. Col "D" is extended out for an extra 100 weeks; the graph and the x axis accomodates that. If I do not extend the Col "C" formula out, line graph for C stops as desired, however, if I expend out the Col "C" formula, the line drops to the x ...

how to get rid of using the space bar?
each time i enters a ('), i must enter the space bar, before the (') mark appears. Fellow MVP Graham Mayor has dealt with this problem in the following: http://help.lockergnome.com/office/Apostrophes-Quotation-marks-Word--ftopict1014186.html -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Leonard Lau the microwave man" <Leonard Lau the microwave man@discussions.microsoft.com> wrote in message ne...

Reformatting Cells
I have this in my column: 858+00.000 R 2 858+00.000 R 2 858+00.000 R 2 858+00.000 R 2 858+00.000 R 2 858+00.000 R 2 859+00.000 R 2 859+00.000 R 2 859+00.000 R 2 859+00.000 R 2 859+00.000 R 2 etc.. This progresses all the way to 1350+00 R 2. All I want to do is remove the R 2 at the end, but keep the number. There must be a faster way than deleting the R 2 in the formula toolbar, then copy>paste in my column. Thanks! Maybe you could select the range to fix Edit|replace what: _R_2 (_ represents a space character) with: (leave blank) replace all But when I di...

TRIM function not working
I am trying to remove trailing spaces from entires in a column. In another column I have used the expression of the following format in each of the cells next to those that I want to change : =TRIM(C200) . This has made no difference to the length of the entries in the column and the trailing spacess have not been eliminated. Can anyone help please Peter Peter, It is likely that you have other ASCII characters that look like spaces - try this macro first Sub TrimALL() 'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm '-- http://www.mvps.org/d...

Line Spacing
I have a user that has a problem with line spacing that I cannot figure out. She has an Excel document that is emailed to her, and every so often, she has a line spacing that is about twice the size of the rest of the spacing in the document. I have tried to resize, but it doesn't save. Any ideas? Thanks. -- Manzy Hi, We really need more info, like the file when the problem is occuring. It could be that the is a macro in the file which is causing the problem. It could be that one cell on the row has word wrap applied automatically or manually. -- Cheers, Shane Devenshire ...

Copy Word table into Excel cell by cell
Is there a way to copy a Word table into Excel cell by cell when the Word cells contains multiple paragraphs of text? (Excel breaks each Word cell into several rows.) > Is there a way to copy a Word table into Excel cell by cell when the Word > cells contains multiple paragraphs of text? (Excel breaks each Word cell into > several rows.) In Word, hit CTRL+Shift+* to turn on "Show non-printing characters". This should show there are 2 "reverse p" characters (paragraph markers) between each para. So then do a Replace (CTRL+H) -- in Find What, use "^p^p"...

Removing space
How do I delete spaces at the beginning of a cell for the whol column?:confused -- Message posted from http://www.ExcelForum.com Manually...... In an adjacent column enter =TRIM(cellref). Double-click on the fill-handle at bottom right of this cell and it will replicate down as far as data in the original column. When happy with the results, Copy the column with the TRIM formula and paste special>values(in place). Then delete original column. VBA Macro.......... Sub TRIM_EXTRA_SPACES() Dim cell As Range For Each cell In Selection If (Not IsEmpty(cell)) And _ ...