#### Splitting cells

```in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
1234567 and cell C1 to have just 89. what is the formula? i have tried text
to columns
```
 0
Utf
5/5/2010 8:51:01 AM
excel.misc 78881 articles. 5 followers.

5 Replies
986 Views

Similar Articles

[PageSpeed] 16

```If this is for extracting the first 7 characters use LEFT()

=LEFT(A1,7)
=RIGHT(A1,2)

--
Jacob (MVP - Excel)

"fazz" wrote:

> in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
> 1234567 and cell C1 to have just 89. what is the formula? i have tried text
> to columns
```
 0
Utf
5/5/2010 9:01:01 AM
```Hello Jacob - i did not explain this very well.The digits in cell A1 is
variable length. In cell C1 i need the last 2 digits of cell A1. In cell B1 i
need the remainder of cell A1. this maybe more or less than 7 digits

"Jacob Skaria" wrote:

> If this is for extracting the first 7 characters use LEFT()
>
> =LEFT(A1,7)
> =RIGHT(A1,2)
>
> --
> Jacob (MVP - Excel)
>
>
> "fazz" wrote:
>
> > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
> > 1234567 and cell C1 to have just 89. what is the formula? i have tried text
> > to columns
```
 0
Utf
5/5/2010 9:17:01 AM
```Hi

in C1
=RIGHT(A1,2)
in B1
=IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2),A1)

--
Regards
Roger Govier

fazz wrote:
> Hello Jacob - i did not explain this very well.The digits in cell A1 is

> variable length. In cell C1 i need the last 2 digits of cell A1. In cell B1 i
> need the remainder of cell A1. this maybe more or less than 7 digits
>
> "Jacob Skaria" wrote:
>
>> If this is for extracting the first 7 characters use LEFT()
>>
>> =LEFT(A1,7)
>> =RIGHT(A1,2)
>>
>> --
>> Jacob (MVP - Excel)
>>
>>
>> "fazz" wrote:
>>
>>> in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
>>> 1234567 and cell C1 to have just 89. what is the formula? i have tried text
>>> to columns
```
 0
Roger
5/5/2010 9:35:21 AM
```Change the 1st formula to

=LEFT(A1,LEN(A1)-2)

--
Jacob (MVP - Excel)

"fazz" wrote:

> Hello Jacob - i did not explain this very well.The digits in cell A1 is
> variable length. In cell C1 i need the last 2 digits of cell A1. In cell B1 i
> need the remainder of cell A1. this maybe more or less than 7 digits
>
> "Jacob Skaria" wrote:
>
> > If this is for extracting the first 7 characters use LEFT()
> >
> > =LEFT(A1,7)
> > =RIGHT(A1,2)
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "fazz" wrote:
> >
> > > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
> > > 1234567 and cell C1 to have just 89. what is the formula? i have tried text
> > > to columns
```
 0
Utf
5/5/2010 9:46:01 AM
```many thanks...thats done the job!

"Jacob Skaria" wrote:

> Change the 1st formula to
>
> =LEFT(A1,LEN(A1)-2)
>
> --
> Jacob (MVP - Excel)
>
>
> "fazz" wrote:
>
> > Hello Jacob - i did not explain this very well.The digits in cell A1 is
> > variable length. In cell C1 i need the last 2 digits of cell A1. In cell B1 i
> > need the remainder of cell A1. this maybe more or less than 7 digits
> >
> > "Jacob Skaria" wrote:
> >
> > > If this is for extracting the first 7 characters use LEFT()
> > >
> > > =LEFT(A1,7)
> > > =RIGHT(A1,2)
> > >
> > > --
> > > Jacob (MVP - Excel)
> > >
> > >
> > > "fazz" wrote:
> > >
> > > > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers
> > > > 1234567 and cell C1 to have just 89. what is the formula? i have tried text
> > > > to columns
```
 0
Utf
5/5/2010 12:24:05 PM

Similar Artilces:

blocking blank cells until other cells are compiled
Hello everyone. I'm working on several columns. Each column corresponds to a certai date. I would like the fields under the date cell to be "available only if the date cell is filled. How do I do this? Say in cell "A1" I have today's date and in "B1" tomorrow's (and s on). In cell "A2" I want to put the number of dollars I have spen during lunch and in cell "B2" I will want to do the same, but onl tomorrow of course. I would like to "compile" the whole month in terms of columns but would like NOT to be able to write in the...

Cell selection
Hi, Can anyone tell me why, when I select a cell near the bottom of a page, 3-4 rows seem to be sected as well? Thanks in advance Jason Hi, You are probably in Extend mode. Look at the bottom right of the Status Bar, look for Extend, its location and wording depend on version. If it is on press Esc or F8. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Boenerge" wrote: > Hi, > Can anyone tell me why, when I select a cell near the bottom of a page, 3-4 > rows seem to be sected as well? > Thanks in advance > Jason I am having this p...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Cannot hide cells
I have a spreadsheet that is setup columns A-IV and need to hide columns but after a while get the message "cannot shift cells off sheet" any ideas its driving me nuts Regards Craig How are you hiding the columns? It sounds like you might be adding columns or moving cells to the right to hide them from the view. If you have items in Column IV you can not do this. If you are doing it as<format><columns><hide> about howmany times do you do it before you have the problem. (I cant make it happen on my computer) If you are doing it another way, What is it? &q...

text in cell shows up as ####
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Text in cells in a coumn shows up as ####, apparently because there is a large amount of text. When I mouse over, the text shows. It also shows up in the formula bar. How can I copy and or print the text in these cell? <br><br>When I try to save these cells as text and transfer to Word, they continue to print as ###. text in cell shows up as #### <br> I am totally new to this whole environment! A new iMAC and this software. <br> However, in trying to learn how to use EXCEL, I found that...

Printing large cells
I have a spreadsheet in which many of the cells are very large. When the spreadsheet is printed, not all of the large cell contents print. How do I get all of the cell contents to print? ...

copying text on sheet 1 to corresponding cells on sheets 2 to 5
I want the text entered in A8 on sheet 1(named Productivity) to automatically transfer to A8 on sheets 2 thru 5. I used the simple formula =Productivity!a8 in the corresponding cells but when there is no data in Productivity A8 I get a 0 in the corresponding cells. I would like the corresponding cells to remain blank if there is not text in Productivity A8. I am really new to excel, like only used it two weeks! Is there a simple fix to this? -- newbie at large! Hi A formula will always return 0 if it's refering to an empty cell. This formula will let the cell look empty if no...

Excel limited cell formats
I have run into Excel's limit on 4000 cell foremats. I have since split the excel workbook into two separate workbooks. My question is, how can I launch these from a template and have the two workbooks link with each other, My original workbook has 25 separate sheets within the workbook for a total of 3.25mb size. I have a whole slew of macros assigned to different command buttons. Mike Johnson ...

My spreadsheet contains a list of people. The cell next to each nam contains multiple numeric values for identifying a specific piece o information. I would like to be able to click on one of those number (value) and a comment window pop up with the information associate with it, or be hyperlinked to the information further down th speadsheet. I want to avoid using multiple cells for this. Is this possible? Thank -- t2tru ----------------------------------------------------------------------- t2true's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=387 View this...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Splitting excel cells based on content
I have an excel workbook with a worksheet created by a dump from a database (DOORS in this case). The first column is unique, the second not. The second column may contain 0 to n identifiers that I want to look up on a separate sheet. The lookup is easy when the cell in the 2nd column has either 0 or 1 identifiers in it but when there are 2 or more (separated by a carriage return I think) my lookup fails as lookup is using the enitre contents of the cell. What i'd like to be able to do is to automatically (there's some 900+ rows in the worksheet) is to automatically insert additio...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

sales report by split sales persons
we have situations where 2 different sales persons sell to the same customer, depending on product group. We select the specific sales person when we enter the sales order. Unfortunately, later when we want to run a sales report, it assigns all the sales to whatever salesperson is shown in the Cards as the default sales person. We need a report that assigns sales to sales persons based on the data from the individual sales orders ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click th...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

cursor control in cells
Can anyone help please? For some reason i am no longer able to scroll across a cell by moving through teh arrow keys. The whole sheet moves instead. Can anyone tell me what I ahve done to lose this control please? Many thanx DAT Hi Dave You have probably inadvertently pressed the Skulk key. Press it again to take Scroll Lock off and you should be OK. -- Regards Roger Govier "Dave T" <datucker@ntlworld.com> wrote in message news:J8sUh.378\$V7.132@newsfe7-gui.ntli.net... > Can anyone help please? > For some reason i am no longer able to scroll across a cell by ...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...

Cell formatting: displaying lat/long coordinates
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Rather than formatting a cell to display time in hours and minutes and = seconds, I would like to display latitude and longitude in degrees, = minutes, and seconds. e.g. 43=BA 25' 34" Is this possible in Excel 2000? ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W...

Locate and delete specific cells
I need to locate all cells that have a specific value, then delete those cells and the cells to the immediate left of those with the specified value. Once the cells are deleted, I want to shift the remaining cells up. I have worksheets with names and values. The values are frequently "NA". I need to delete the values of NA and the name associated with that value, I then want to shift everything up so as to remove all the blanks. Right now I anm doing it manually, and I am sure there is an easier way of doing it. You can do that directly withour first deleteing the values, sele...

Auto copy dates from one cell to another
I have a multiple page worksheet. In A8 is for the Employee Name and in B8 is for the date. I would like for this information to automatically be placed at A96 and B96 which is the top of the second page within the worksheet and so on for the remainder of the pages. Position cursor in cell A96 and enter the formula: =A\$8, and in B96 the formula =B\$8 Copy these two cells to the appropriate cells on the other "pages" of your worksheet. Pete Hi Kelly, In cell A96 you fill out the formula =A8 In cell B96 you fill out the formula =B8 this formula tells excel to take the val...

Executing a macro from a cell
1. There is some way to make that excel, starting from a conditional structure in a cell, execute automatically a macro??? Something like this: A B 1 2 1 =if(A2=1,macro1(),macro2()) No, functions can return a value, they cannot change the format or run a macro. You can use event code to do it, but not a worksheet function. -- HTH RP (remove nothere from the email address if mailing direct) "filo666" <filo666@discussions.microsoft.com> wrote in message news:122D7DF6-CF7B-4AFB-A6F7-5F55DA090A86@microsoft.com... > 1. There is some way to make that excel, starting ...

Autosum when some cells are blank
Is there a way to autosum so that Excel will ignore the blank cells but still add all of the other cells in a row? I have a large spreadsheet with 1000 rows in it and would like to click on autosum w/o having to change the cell reference. Thanks! If you select your range first, then hit the autosum button, excel put the formula in next row. And one way to select the range is to click on the top cell and shift-click on the bottom cell. (Use the vertical scroll bar to get to the bottom cell.) (yeah, it still might be easier to just remember the first row and adjust the formula.) sue...

Displaying Large Amount of Data in a Cell
Exporting an Access table to excel - one of the fields is a memo filed that has a large amount of text in it. Two questions: 1. What is the limit on the amount of characters that a cell can hold? and 2. How can I view that text in the Excel field it was exported to? -- Paul Mazzola General Manager-High Speed Data Services Time Warner Cable Eastern Carolina Division Wilmington NC Ph: 910-772-5793 Fax: 910-772-5796 Hi, 1. A cell can only display 1024 characters in a cell. 2. I'm not sure there's a way to get to the rest... Any way you can export to a text file and break it up...

"Cannot empty the clipboard" when dragging cells
I just finished reinstalling Windows XP Professional (clean install) and = Office XP and now when I try to drag data from one cell to another, I = get the "cannot empty the clipboard" error. It still moves the data, = but it pops up the alert dialog everytime which is very annoying! I checked for the exact message and permutations in the MS Knowledge = Base and it came up without any links. Your help is greatly appreciated. Scott Robuck This was written for xl97, but maybe it still applies: http://support.microsoft.com/default.aspx?scid=kb;en-us;196620 XL97: "Cannot Empty ...

Validation cell in UDF?
I want to use a user-defined function as part of a cell validation routine. Is there a way from within the function to determine what cell is being validated? Does anyone have examples of something similar? Thanks, -- Greg Greg, Try Application.Caller.Address -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Greg Allen" <gregory.allen@sierraatlantic.com> wrote in message news:e1zFRUYfIHA.1204@TK2MSFTNGP03.phx.gbl... >I want to use a user-defined function as part of a cell validation routine. > Is there a way ...