right justify text ot columns?

I have a column with lots of figures ranging between one and fou
digits. I want to place digits into separate columns using text t
columns but right justified. I thought right justifying the figure
first then using text to columns would work but everything stil
appears left justified. Any suggestions

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

0
6/14/2004 11:04:46 AM
excel 39879 articles. 2 followers. Follow

7 Replies
1391 Views

Similar Articles

[PageSpeed] 25

Hi

The justification of a cell is just cosmetic. You can use Text to Columns
whatever the formatting is. An alternative is to use formulas:

=MID(A1,1,1)
=MID(A1,2,1)
=MID(A1,3,1)  etc
Once done, if you want it right-justified, just format it as such.

-- 
Andy.


"judoist >" <<judoist.17u7tw@excelforum-nospam.com> wrote in message
news:judoist.17u7tw@excelforum-nospam.com...
> I have a column with lots of figures ranging between one and four
> digits. I want to place digits into separate columns using text to
> columns but right justified. I thought right justifying the figures
> first then using text to columns would work but everything still
> appears left justified. Any suggestions?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
andyb1 (494)
6/14/2004 11:16:41 AM
Thanks, but not quite what i meant.

As an example, lets say all my figures ranging from 1 to four digit
are in column one. Columns 2 to 5 will be where the results will b
displayed once i have used text to columns to separate each number int
an individual digit. So number 3254 in column one would be 3(in colum
2), 2 in column three, 5 in column 4 etc. Number 24 in column one woul
be 2 in column two and 4 in column three. 

I'm trying to get Excel to shift everything to the right so number 2
is displayed as 2 in column 4 and 4 in column five. If the number was 
it would show as 1 in column five. 

Natha

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

0
6/14/2004 11:46:51 AM
Hi

Try these:
Col 2
=IF(LEN(A1)>3,MID(LEN(A1)-3,1,1),"")
Col 3
=IF(LEN(A1)>2,MID(A1,LEN(A1)-2,1),"")
Col 4
=IF(LEN(A1)>1,MID(A1,LEN(A1)-1,1),"")
Col 5
=RIGHT(A1,1)

-- 
Andy.


"judoist >" <<judoist.17u9s1@excelforum-nospam.com> wrote in message
news:judoist.17u9s1@excelforum-nospam.com...
> Thanks, but not quite what i meant.
>
> As an example, lets say all my figures ranging from 1 to four digits
> are in column one. Columns 2 to 5 will be where the results will be
> displayed once i have used text to columns to separate each number into
> an individual digit. So number 3254 in column one would be 3(in column
> 2), 2 in column three, 5 in column 4 etc. Number 24 in column one would
> be 2 in column two and 4 in column three.
>
> I'm trying to get Excel to shift everything to the right so number 24
> is displayed as 2 in column 4 and 4 in column five. If the number was 1
> it would show as 1 in column five.
>
> Nathan
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
andyb1 (494)
6/14/2004 1:22:22 PM
Sorry.
Col 2 should have been:
=IF(LEN(A1)>3,MID(A1,LEN(A1)-3,1),"")

-- 
Andy.


"judoist >" <<judoist.17u9s1@excelforum-nospam.com> wrote in message
news:judoist.17u9s1@excelforum-nospam.com...
> Thanks, but not quite what i meant.
>
> As an example, lets say all my figures ranging from 1 to four digits
> are in column one. Columns 2 to 5 will be where the results will be
> displayed once i have used text to columns to separate each number into
> an individual digit. So number 3254 in column one would be 3(in column
> 2), 2 in column three, 5 in column 4 etc. Number 24 in column one would
> be 2 in column two and 4 in column three.
>
> I'm trying to get Excel to shift everything to the right so number 24
> is displayed as 2 in column 4 and 4 in column five. If the number was 1
> it would show as 1 in column five.
>
> Nathan
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
andyb1 (494)
6/14/2004 1:23:46 PM
Hi again

Doesn't seem to be working. I use Excel 2000. Do you think that migh
have something to do with it? 

Natha

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

0
6/14/2004 3:14:14 PM
No - because I use XL2000 too!! What does it do - or what doesn't it do? It
works OK for me - with your data in A1. What results are you getting?

-- 
Andy.


"judoist >" <<judoist.17ujdo@excelforum-nospam.com> wrote in message
news:judoist.17ujdo@excelforum-nospam.com...
> Hi again
>
> Doesn't seem to be working. I use Excel 2000. Do you think that might
> have something to do with it?
>
> Nathan
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
andyb1 (494)
6/14/2004 3:21:28 PM
I expect that you would get  False out of testing such cells
as numbers    =ISNUMBER(D3)

Try using the TRIMALL macro on your "numbers"
    http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Before using TRIMALL suggest changing the cell format to what you
want  (forget about  right justify)  and specifically make sure they
are not formatted as text.
---
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

"judoist >" <<judoist.17u7tw@excelforum-nospam.com> wrote in message news:judoist.17u7tw@excelforum-nospam.com...
> I have a column with lots of figures ranging between one and four
> digits. I want to place digits into separate columns using text to
> columns but right justified. I thought right justifying the figures
> first then using text to columns would work but everything still
> appears left justified. Any suggestions?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
dmcritchie (2586)
6/14/2004 4:01:31 PM
Reply:

Similar Artilces:

Access data into Excel across columns
Is there a way to create an Access report that would take the field 'names' and expand the records from left to right (text vertical) rather than from top to bottom? The other alternative would be to bring the data into Excel and have it populate pre-formatted cells from left to right (across columns). I'm using Office XP Thanks in advance! ...

A hint in the right direction... please :)
We have a small business selling computer products and now we have a website to sell the products on. We have 3 main suppliers each with a few thousand products and currently I am using a macro (many infact) in excel to combine the current data files we get from each supplier into one file that gets uploaded to the website for import. However we would like to move the whole thing over to access to hopefully speed up the daily process of creating the file for upload. I get similar data from each supplier although obviously the columns arn't in the same order etc and one supplier has info th...

Column Width
Hi Guys !! I want to learn auto column width VBA application. When I enter data in the column, column width should adjust according to size of DATA. Can somebody help ! I will appreciate your efforts. Thanks in advance. Hi This is an event code, so it has to be inserted into the codesheet for the desired sheet. Also, the column width will never be less than 8: Private Sub Worksheet_Change(ByVal Target As Range) Target.EntireColumn.AutoFit If Target.ColumnWidth < 8 Then Target.ColumnWidth = 8 End If End Sub Regards, Per "Khan" <aldehides@hotma...

error loading columns for printing
i have been using frx 6.5 with sp7 in windows server 2003 for many months now. then i accidentally installed the FRx2003orXPSP2Update.zip. i now cannot open frx so i reinstall it. i successfully opened it without errors but when i generate a report in any output type, the error "error loading columns for printing" and "error setting device context. please reopen drilldown viewer and try again." appears. i need help. thanks, mel ...

fixed column
hi I have a problem I want to fix a column's value please help me "majid" <mr_farahmand@yahoo.com> wrote in message news:066401c34f77$9c26b1f0$a601280a@phx.gbl... > hi > I have a problem I want to fix a column's value please > help me You will have to provide more information about what you want. A column does not have a value. A cell may have a value, but as a column contains many cells it may contain many values. A column has a width - is this what you are talking about? What do you mean by 'fix'? Hi Majid! If you mean fix the maximum value th...

identifying a chart column individually in code
Let me try a different approach. I currently use a clusterd column chart that is created automatically with code. Presently, I need to select each column manually to format them according to a convention that we like at work. Each column that represents a subset of a given group is colored to represent the group of which it is a subset. Each column that is of a different group gets a different color. I think I can automate this process if I can tell the code to look at column 1, for example. Then column 2, and so forth through the entire set of columns. If I can do that, I can provide an I...

Excel spreadsheet
How do you slant column headings? Within one wide column I want to have 3 slanted column headings for that particular column. Can this be done? Thanks for your help. Christine Enter the three lines with Alt+Enter after each one. Then set the orientation from Format/Cells/Alignment tab. Andy. "Christine" <christine.lee@barco.com> wrote in message news:0f3001c37df5$64f6a0d0$a401280a@phx.gbl... > How do you slant column headings? Within one wide column > I want to have 3 slanted column headings for that > particular column. Can this be done? Thanks for your &...

inserting text
I have a sheet that lists the sales tax of different cities. I want to be able to inport the specific rate into a cell on another sheet so that it states Sales Tax (7.0%) Is there any way that I can link the percentage to the other sheet so that I don't have to change it when I change the city in an above cell. Thanks for any help Yes! Check out the VLOOKUP() function in Help. Although it'd be more helpful if you were more specific, how about a vlookup combined with a concatenation. This will take a little setup. For this suggestion you'll have to have the workbook wi...

column is added up by original figure not after rounding up
Microsoft Excel 2003 my format is multiplying cell by 0.245 the system automatically rounds up to the nearest unit (because i am using currency) When l total the whole column it adds by the original three digit and not the rounded up figure. Which means by totals are incorrect Change your formula to: =ROUND(A1*0.245,2) which will round to 2 decimal places (cents, or pence etc), or: =ROUNDUP(A1*0.245,0) if you really want to round up to units (dollars, pounds etc), Your total will now reflect what is on screen. Hope this helps. Pete Gwen wrote: > Microsoft Excel 2003 > > m...

Making phone calls right from Excel
Does anybody know of a little program to help me make phone calls (via the windows dialer and a modem) by double-clicking on a phone number in an excel table??? Thanks, Peter right click sheet tab>view code>copy/paste this Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Sub CellToDialer() 'John Walkenbach ' Transfers active cell contents to Dialer ' And then dials the phone ' Get the phone number CellContents = ActiveCell.Value If CellContents = "" Then MsgBox "Select a cell that contains a ...

Plain text freindly..
Please make this product (v10.6515.6626) easier to use for those of us who have standardized on plain text messages. Specifically: * Have ONE setting to configure the mailer to send and receive in plain text. * The reply option does not reliably start in plain text mode * Make the spell checker smarter to avoid checking quoted plain text. * Keep the inbox from making hits onto the internet (which is all spam anyway and therefore a privacy breech) * When replying, only quote *selected* text to keep messages short. * Make a "paste as quotation" option so that text may be inse...

Changing columns to rows
I have a table of data in a spreadsheet that is laid out as 12 columns and 8 rows. This makes it difficult to print to one page and I now want to "rotate" it so that the columns are now rows and the rows are now columns. Is there an easy way to do this? TIA ...

Printing column label on every page.
I have several column labels like date, hour, ect on one row on the top. I want to print this row label on every page. However I can't seem to do that because on the instruction option is grayed out. Here is the instruction. The last two instruction sentences are grayed and they give me no choice to do it. 1.. Click the worksheet. 2.. On the File menu, click Page Setup, and then click the Sheet tab. a.. 1.. Do one of the following: 2.. Print row and column headings 3.. a.. Select the Row and column headings check box, and click Print. 4.. Print row and column labels on ...

Arrangement of text in a cell
I have text in a cell of my spread sheet. It has last name then first name. Is there a way to re-arrange this so the first name appears first?? Brett -- brettg ------------------------------------------------------------------------ brettg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33879 View this thread: http://www.excelforum.com/showthread.php?threadid=536583 If they are separated by a space: =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)) -- Kind regards, Niek Otten "brettg" <brettg.2...

How to change font color of a text
hello, i am working with vc++ MFC.i am dealing with the changing of font and color of the text in a file. so i need some help in implementing this. Thanks in advance. yours, Siva Prasad.M To change the text color, you will need to change it for the control that is displaying text. Depending upong the control, you can use OnCtlColor to change the background/foreground color etc. To change font, you can use SetFont() on the control. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Siva Prasad" <siva.almighty@gmail.com> wrote in message news:udOEG3cAFHA.2608@TK2MSFTNGP10.phx...

How do I get columns in Office XP?
My Publisher Help pane is showing blank on creating columns. I cannot find any menu item on creating columns as we did in 2000. Insert a text box and right click on the box. Choose Format Text Box and then click on the Text Box tab. Set up the box to your needs. There is also a Columns button on the Standard tool bar. "annitaf" <annitaf@discussions.microsoft.com> wrote in message news:BD4CA13B-31B1-4ADF-B7E4-0829D0D9E3EC@microsoft.com... > My Publisher Help pane is showing blank on creating columns. I cannot > find > any menu item on creating columns as we di...

Count of text in cells
I have problem to solve,I have a column with office. The text in the Office cells with be, Office 1@810@NT but I now have a new columm with text in the following format:Test The Cell - Employment cells can contain mutiple values, Test The Cell - Education;Test The Cell - Employment;Test The Cell - Health . These values are separated by a ; NOt only do I need to count each occurance, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear Submitted via EggHeadCafe - Software Developer Portal of Ch...

How do you anchor a figure/illustration to text?
when I paste a figure in the text... and then the text changes (as in I add something) the rest of the text moves along as it should.... but the figure stays planted where I first put it, so it's now out of alignment How do I anchor the figure to the text paragraph? The text is not in a text box, it's just typed into the doc. Thanks!!! All text in Publisher is in a text box. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Yael" <Yael@discussions.microsoft.com> wrote in message news:732F5D27-0115-...

text gets cut in cells
when trying enter text in a cell (say the size of a letter for example) the text gets cut; only the cut version is printable, although the formula bar displays the whole text. How can I get all the text displayed in the cell (AND printed)? Cells can only print about 1024 characters, even tho they can contain about 32000 characters. In order to print/see more, use a text box. Bob Umlas Excel MVP "D B Kaufmann" <anonymous@discussions.microsoft.com> wrote in message news:c34401c47a31$9a865ea0$a401280a@phx.gbl... > when trying enter text in a cell (say the size of a >...

CString::Right() and ::Left() leaks memory
I'm running VC6. I was getting memory leaks when I compared the results of a Left() and Right() call to a string constant. This was the code that did it: const CString &sName = map.m_sName; if (sName.Left(4) == "Lang" && sName.Right(13) == "Category1Name") { I wasn't concerned that the length of sName might be shorter than the substrings, because the MSDN says that these methods can handle that situation. However, when I changed the code to: const CString &sName = map.m_sName; BOOL bRightTooShort = sName.GetLength() < 13, bLeftTooShort = ...

Enable/Disable Text Fields
Hello, I've searched through posting and I haven't been able to find what I'm looking for so I'm not even sure if this is possible. I have a form, which requires 3 different individual's approval: Person1, Person2, Person3. When the form loads, I have all threes fields enabled, with a command button next to them that is enabled: Approved. I want the individual to be able to click on "Approved" and have a form pop up asking for password, which is checked against the password I have stored in a table. If the password is a match, I want the "...

Is there a way to create a border out of text in Publisher?
WordArt? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "rmac" <rmac@discussions.microsoft.com> wrote in message news:9ADD98BD-4331-4BD6-A669-69DEC7C8828D@microsoft.com... > You can do it with BorderArt if you want to create a graphic from text and then use the graphic. ...

Turing Selection Off after inserting text into a Cedit Control???
CEdit::SetSel void SetSel( DWORD dwSelection, BOOL bNoScroll = FALSE ); void SetSel( int nStartChar, int nEndChar, BOOL bNoScroll = FALSE ); Parameters dwSelection Specifies the starting position in the low-order word and the ending position in the high-order word. If the low-order word is 0 and the high-order word is -1, all the text in the edit control is selected. If the low-order word is -1, any current selection is removed. CEdit *editbox = (CEdit *) GetDlgItem(IDC_EDITWINDOW); editbox->SetWindowText(OutString.c_str()); editbox->SetSel(-1, 0); Why doesn't the above code tu...

Mixed Column Types in One Chart
Looking for a way to mix column types in a single chart. Basically I want three clustered columns each month, BUT: first column is based on a single value, second column is based on a single value, third column is a stack of two values. Something like this (not sure this'll display right when posted, but worth a try, a picture's worth 10,000 words): d d d d a d b d a d a d c a b c a b c a ...

Button Text #2
Hi, I have some text on a button on my dialog. I want to force it so that the text is split. i.e one word is on top the other word is on bottom rather than one continuous line of text. I assume there is some way that this can be done through the dialog editor. Can anyone tell me how it is done? Thanks Macca use \n to slit the line and check property multiline "Macca" <Macca@discussions.microsoft.com> wrote in message news:F0BFC180-392F-4105-BE00-0C42C0BA0F7A@microsoft.com... > Hi, > > I have some text on a button on my dialog. I want to force it so that the text i...