Provide space in text

Where text and numbers are at different locations in different cells what
formula could be used to suit this type of information. I want to be able to
put a space between the main body of text on the left and give a space
between the text and the start of any numbers are in the cell.

Example 1:

A1 =  Abelia Edward Goucher2 litre

Should be:
Abelia Edward Goucher 2 litre

Example 2:

A2 = Acer campestre Nanum180stem 6-8

Should be:
Acer campestre Nanum 180stem 6-8


Thankyou if can be of help.
Pat


0
5/12/2004 1:54:35 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
499 Views

Similar Articles

[PageSpeed] 10

If you double clcik in the cell with the data then you can move th
blink line to were you want and hit space.

this will palce a space were you want or just type it as you requir
it.

If this is no the answer please explain your question a bit mor

--
Message posted from http://www.ExcelForum.com

0
5/12/2004 2:21:51 PM
If the data is contained in the same cell, simply place a 
space using the space bar.

If you are concatenating data from several cells into a 
single cell, include '" ";' in your concatenation string.

If you have data in multiple adjacent columns simply 
adjust the column width to ensure a space.

Finally, if you are using multiple adjacent columns, you 
can format the cell using a custom numerical format as 
follows: " "###" litres".  The result of inputing a '2' 
would be ' 2 litres'.  In this fashion there will alway be 
a space before the numeral, you will always have the units 
behind the numeral, and finally, you will be able to use 
the cell contents in a formula, where if you combine the 
numerals and text as cell contents you can not.



>-----Original Message-----
>Where text and numbers are at different locations in 
different cells what
>formula could be used to suit this type of information. I 
want to be able to
>put a space between the main body of text on the left and 
give a space
>between the text and the start of any numbers are in the 
cell.
>
>Example 1:
>
>A1 =  Abelia Edward Goucher2 litre
>
>Should be:
>Abelia Edward Goucher 2 litre
>
>Example 2:
>
>A2 = Acer campestre Nanum180stem 6-8
>
>Should be:
>Acer campestre Nanum 180stem 6-8
>
>
>Thankyou if can be of help.
>Pat
>
>
>.
>
0
anonymous (74722)
5/12/2004 3:27:22 PM
> If the data is contained in the same cell, simply place a
> space using the space bar

There is over one thousand cells to change - time is not on my side.

> If you are concatenating data from several cells into a
> single cell, include '" ";' in your concatenation string.

The data to be changed is contained in one cell.

> If you have data in multiple adjacent columns simply
> adjust the column width to ensure a space.

Not relevant

> Finally, if you are using multiple adjacent columns, you
> can format the cell using a custom numerical format as
> follows: " "###" litres".  The result of inputing a '2'
> would be ' 2 litres'.  In this fashion there will alway be
> a space before the numeral, you will always have the units
> behind the numeral, and finally, you will be able to use
> the cell contents in a formula, where if you combine the
> numerals and text as cell contents you can not.

Its not a case of formatting the cells. It will require a function, perhaps
the opposite of what TRIM does.


<anonymous@discussions.microsoft.com> wrote in message
news:bee001c43835$9f29dec0$a401280a@phx.gbl...
> If the data is contained in the same cell, simply place a
> space using the space bar.
>
> If you are concatenating data from several cells into a
> single cell, include '" ";' in your concatenation string.
>
> If you have data in multiple adjacent columns simply
> adjust the column width to ensure a space.
>
> Finally, if you are using multiple adjacent columns, you
> can format the cell using a custom numerical format as
> follows: " "###" litres".  The result of inputing a '2'
> would be ' 2 litres'.  In this fashion there will alway be
> a space before the numeral, you will always have the units
> behind the numeral, and finally, you will be able to use
> the cell contents in a formula, where if you combine the
> numerals and text as cell contents you can not.
>
>
>
> >-----Original Message-----
> >Where text and numbers are at different locations in
> different cells what
> >formula could be used to suit this type of information. I
> want to be able to
> >put a space between the main body of text on the left and
> give a space
> >between the text and the start of any numbers are in the
> cell.
> >
> >Example 1:
> >
> >A1 =  Abelia Edward Goucher2 litre
> >
> >Should be:
> >Abelia Edward Goucher 2 litre
> >
> >Example 2:
> >
> >A2 = Acer campestre Nanum180stem 6-8
> >
> >Should be:
> >Acer campestre Nanum 180stem 6-8
> >
> >
> >Thankyou if can be of help.
> >Pat
> >
> >
> >.
> >


0
5/12/2004 4:29:09 PM
Reply:

Similar Artilces:

Use Plain Text as default format
I am using Outlook 2002 on Windows 2000. I cannot find a way to make a new mail message default to Plain Text format. Instead, a new message always starts out as Rich Text. And another issue, HTML format is not one of the options under the "Format" menu pull-down. Why? Can someone assist? Thanks. tools, options, mail format doesn't give plain text as an option? on the html one - are you in the message form's format menu or Outlook's tools options? The message form only shows plain text as an option - until you switch to plain text, then HTML is an option. -- ...

Unable to modify space allocated to System Restore
I wanted to decrease memory down to a reasonabel 5% and exclude external disk from restore monitoring. I could not, marking checks has been disabled by Group Policy. Now, my PC is stand alone and I have administrator privileges. It is true however that I have a LAN installed since I have at home three access points in fiber optics (Relatk Fast Ethernet). I could not find any place where modify this Group Policy which I do not recall to have installed. Perhaps some Key in the Registry or create a new account? May somebody help me? Thanks -- stefanoes stefanoes wrote: > I...

Text size too small
How can I increase text size _here_? I have *View* > *Text Size* set to *Largest" but I need it (to compose) at least two or three times this size. I wish to send in normal size, of course. I have the same problem when I read... (Before a recent reinstall I could use Ctrl and the mouse wheel to enlarge, but this doesn't work now :-((( ) "RT" <zulu.romeotangohotel@ntlworld.com> wrote in message news:uYy09XpJLHA.4120@TK2MSFTNGP02.phx.gbl... > How can I increase text size _here_? > I have *View* > *Text Size* set to *Largest" but...

Displaying Multiple Text Lines At Runtime
I need to display various text messages in my dialog app, and they change during the application. There will be multiple lines of text in one rectangular area. In order to handle that and format it well, should I not use the edit box with an attached CStatic variable? Should I just use text out with coordinates and maybe draw a rectangular area instead of using a control? Thanks, Matt There are several ways by which you can acheive this. 1. In your Status bars, create a new panel for displaying your messages. 2. Create a control bar, which can be docked/closed, and write your messages in...

Printing with Publisher 2007--text doesn't show.
I have created a document in 2007, with colored type on a black background. When I try to print it...I only get the black background, no text, no images. I have tried to change the "Order", sending type/image to front. Nothing seems to work. HELP! Q You will never see color on black. A way around would be to outline the font and images in white. You can outline the text in the Format, Font. After you select outline, highlight the text, fill with white. Images are harder, if they are vectors you can select the image, ungroup, delete the background, group the rest, appl...

No Outside Provider Mail
I do not want to be an MSN user but Windows 7 seems to be determined to make me one and won't allow me to sign up to the email program built in to the system using my old (and proper) blueyonder.co.uk address which is what I've been using for the past ten years. I want to use my email account NOW. Not google, not live, not hotmail, not yahoo. My outside provider. I need help big time with this - anyone know how to do this without having to pay Microsoft more of the money they don't deserve? Windows 7 does not have a built in email client. You must choose one ...

Is this text from Microsoft press really correct(exam 70-536)
Hi! Here I have some text from Microsoft Press(exam 70-536) It says: "The .NET Framework provides you the ability to both import Win32 functions using Dll import attribute and you can also use P/Invoke to make direct calls against Win32 DLLs." Now when I use P/Invoke I use the DLLImport attribute but what does the book mean in this text when it says "both import Win32 functions using Dll import attribute" but this is how P/Invoke is used and then the text says you can also use P/Invoke to make direct calls against Win32 DLLs. Does anyone understand what t...

form text field wont change font size word 2007
I have read the other discussion this issue? But it does not make any sense to me. I had a previously made a fill in form in a different version of word, i found how to edit it by protecting and unprotecting the doc. The font is set to comic sans MS, and when i fill in the fields, it makes them another font and style? How do i get them to be the same font as the rest of the document. Can someone put this in English for me? Thanks You will have to apply the formatting to the text paragraph before inserting the field. -- Stefan Blom Microsoft Word MVP "dlb1228...

Drive Space for Web Server and SQL Server
Hello, How much drive space is necessary for the CRM install on the web server and for a separate SQL server? I have read through the implementation guide and cannot find where it give disk space requirements. Thanks! JED JED, The web server install is fairly small (less than 200MB). The SQL footprint is completely dependant on the amount of data you choose to store. If you are going to be storing a lot of attachments, then you are going to need a lot more space. Matt "JED" <Jbyrd76@hotmail.com> wrote in message news:%23EQZwM4hDHA.1688@TK2MSFTNGP10.phx.gbl... Hello, Ho...

Vertical text, font corruption #3
Hi there. I'm trying to draw a vertical text. But I see the font is corrupted. I mean it looks ugly. No antialiasing and so. whe I draw the text horizontal it looks just fine. I'm using "lf.lfEscapement = - 90 * 10;" to make a font vertical and "pDC->TextOut(20, 22, sTitle);" for drawing. lf.lfQuality = ANTIALIASED_QUALITY for both vertical and horizontal. Have now idea what it could be... Any ideas? Thanks. ...

Retrieving JUST text from a node with child nodes
Hi all, Can't seem to figure out how to retrieve the text of a node with multiple child nodes (and I just want the text of the parent, not the InnerText or InnerXML that comes with it). My doc looks something like this: <parent attrib1 attrib2 attrib3> Extractable string <child> some more text </child> <child> yet more text </child> </parent> What's the best way to return "Extractable string"? You can either just select the text via an XPath expression like this: /parent/text() or, if your XmlD...

Text in cell
When I fill a cell with text, I end up not seeing all the typed text that shows in the address bar. Even if I make the cell higher, it does not allow for more text. I want to maintain the width I have, but increase heigth if need be. Is the amount of text allowed in a cell predetermined, or can I get more to show in the cell? Have you tried Format > Cells > Alignment > Wrap Text? -Dave "Ted" <tedwoodie@worldnet.att.net> wrote in message news:122401c37e4c$02b386c0$a001280a@phx.gbl... > When I fill a cell with text, I end up not seeing all the > typed text ...

How can I translate a comment to text in a cell?
A cell has existing text in it and it has a comment attached to it. I'd like to append the comment to the end to the existing text in the cell. Has anyone done this already? Is it possible without going into each and every comment? Thanks, Chris ASAP utilities has a UDF formula called "ASAPGetComment()". If you install this utility (which I highly and impartially recomend), you could use this formula to concatenate the two together. http://www.asap-utilities.com "DFIChris" wrote: > A cell has existing text in it and it has a comment attached to it. I...

Reading text files one line at a time
Hello everyone, I'm using Visual Studio .Net and I would like to know how can I read a text file, line by line? Knowing that at the end of each line there is a new line character. Thanks, Geo Use the CStdioFile MFC class. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Geo" <Geo@discussions.microsoft.com> wrote in message news:60119E6F-2BB2-4E4B-B689-F7BB1FE0706D@microsoft.com... > Hello everyone, > > I'm using Visual Studio .Net and I would like to know how can I read a text > file, line by line? Knowing that at the end o...

one text column to several columns/table ?
Occasionally I would like to print the contents of a big folder onto one sheet of paper. ( TO scribble make notes etc. ) Often it's an entire folder so I type "dir /b > pix.txt". ( remember DOS ? ) That gets me all the filenames in one file that I open with Excel - and of course all the data is in column A. Anyway in Excel to easily convert that into "as many columns as it takes to print onto one sheet of paper" other than me doing some math to figure how many columns I'd like and then cut/paste into B, C , D etc ? Hi Bobb - is there a reason not ...

Text Box Right Tab and Margin Alignment
I am using Publisher 2003 and cannot set a right tab which will line up with the right margin. When I right justify, it goes to the edge. A right tab will not. Am I missing something? All you ever wanted to know about tabs. Create or modify a tab stop http://office.microsoft.com/en-us/assistance/HP051150711033.aspx "Kairosleg" <Kairosleg@discussions.microsoft.com> wrote in message news:1A5FB6CE-F4B3-4741-84BB-FE52529E8B87@microsoft.com... >I am using Publisher 2003 and cannot set a right tab which will line up >with > the right margin. When I right justify, ...

Excel radial diagram text offset
I have a radial diagram. In this diagram the value arrays are displayed in relation to there total sum. The values of the arrays are displayed as absolute number not in percent of the total sum. How can I get the total sum of all the arrays to be displaed in the diagram without to be forced to calculate it separately? A chart does no calculations for you. If you want a value in the chart, you must calculate it, and point the chart at the calculation. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _...

sorting text in 2 columns by likeness
I have 2 columns of text with number values associated with them. Amy 21 Amanda 12 Bill 12 Amy 18 Chris 32 Bill 23 Kathy 8 Chris 12 Kim 15 Curtis 32 Todd 23 Katrhy 21 Todd 5 I need to be able to sort these like this Amanda 12 Amy 21 Amy 18 Bill 12 Bill 23 Chris 32 Chris ...

Outlook 2003 sending blank emails / text missing from body
Hi I have just upgraded three machines to office 2003, outlook works fine on two machines but the third has the blank email body problem on all sent mails. Has anyone come up with a solution to this:- Every time I send a mail the recipient receives a blank mail, even the mail within the sent items is blank. I have tried changing the mail setting etc but as yet no solution. The set up on the machine is identical to the other two machines; the only difference is that this machine had an existing Outlook 2002 account set up that was imported when 2003 was installed. Therefore it had an existi...

Bullet & Text combo
Is there a way to create a bullet list style that uses a combination of a bullet and text; e.g., a red arrow and the word "important?" I have a client that wants several styles like this in a new template. -- lcshrm Apparently that cannot be done with Microsoft Word. If you really have to make that work the best thing to do is to create a table with three columns with invisible gridlines. One column would have the arrow,m the other one the word important and the n the last one the text you want to include in that row. Let me know if this information was useful...

separte text
Hi, I have a query setup as a trigger below in sql 7: declare arow cursor for select sonitem, spart, soq, sdisc, sunitprice, sprice from salesitems where sona = @xson and soq>0 open arow while 1=1 begin fetch next from arow into @xitem,@xpart,@xqty,@xsdisc,@xsunitp,@xsprice if @@fetch_status <> 0 break select @xmess1 = @xmess1 +ltrim(convert(char(4),@xitem)) + " "+@xpart + " " +ltrim(convert(char(10),@xsdisc))+ " " +ltrim(convert(char(10),@xqty)) + " " +(convert(char(10),@xsunitp)) ...

How do I clear blank space at the top of a word-wrapped text cell?
Often when I set cells to word-wrap and then auto-fit the row height, cells with text will end up with a blank line or two at the top. It's not extra spaces in the text because when I go to the formula bar, there's nothing before the start of the text. I can manually adjust the size of each row, but that's a hassle and not very consistent! Thanks. In the same Format, Cells, Alignment, select Top in Text Alignment Vertical. HTH, Carole O "mirage1210" wrote: > Often when I set cells to word-wrap and then auto-fit the row height, cells > with text will ...

Text box is displayed when cell is activated
I have a spreadsheet that was created by someone else. When you activate specific cells, what appears to be a text box appears with specific information (e.g., Enter a 4 digit branch number here") about what is required in this column. I would like to remove these but I cannot find how they are linked to the specific cell. The cell contents are displayed in the menu bar (Branch). Can anyone help? ...

Formating Text in a List Box
How do I increase or format the text in a list box? Thanks! In the Properties Box you can set the Font, Style and Size. PC "PSTEWART" <PSTEWART@discussions.microsoft.com> wrote in message news:C26CF8F6-8C68-4809-9F49-54041CCCE9B1@microsoft.com... > How do I increase or format the text in a list box? Thanks! Thanks for your post. When I create a "list box" from the Forms toolbar, the properties box does not have any options to change the font size. I am assuming that the text size is set to the default text (font and size) of the workkbook.. "PC&...

How do I convert Organizer 6.0 file to text file for import to Outlook
Many people have said to just export an organizer file as a text file, then import to Outlook. I can't even get past that step. I am using Organizer 6.0 and Outlook 2002. When I open Organizer and go to Export, there is not even a file available to export. The only file I am aware of is an .OR6 file, but that is not one of the options on the export function. Any help? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...