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

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
>.
>

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 cellRight, 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 cellI 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 ADOI 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 valuesI 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 TrimGreetings 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 rI 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 #2How 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 spacesI 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...

Formatting cells in ExcelHelp! 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 occupationHello.
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 wrappedIn 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 galleryI'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...

TrimI 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 referenceI 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 SpacesI 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 CellsI 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 workingI 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 SpacingI 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 cellIs 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 spaceHow 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 _
...