Copying Part of a row down part of a column

Hi,

  My problem is this.  I have a specific row of data that I need to
copy part of down a column, until the next row of data.

 The data is in a format   12345   abcd  efgh  ijkl

I only need to copy the numeric portion (12345) down the column, until
the next row I need to copy (same format) shows up.  What's the
best/quickest way to do this.  It's like an =IF function, but I'm not
sure of the syntax.

Thanks

0
1/6/2006 9:46:53 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
417 Views

Similar Articles

[PageSpeed] 52

Try this:
assuming your column A is the column with your data with blank rows i
between and column B is where you want the results then.

=IF(A2<>"",LEFT(A2,FIND(" ",A2)-1),B1)
start this formula in the same row as your first bit of data and cop
down.

HTH
J

--
pinmaste
-----------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=49887

0
1/6/2006 10:49:37 PM
This is copied from someone else's post (from quite a while ago.)  I don't 
have their name so I can't give proper credit:

Select column A. Hit F5. Click on Special. Click on Blanks, then OK. 
Type = and then hit Up Arrow. Hold Control Key and hit Enter.


"Not Excelling" wrote:

> Hi,
> 
>   My problem is this.  I have a specific row of data that I need to
> copy part of down a column, until the next row of data.
> 
>  The data is in a format   12345   abcd  efgh  ijkl
> 
> I only need to copy the numeric portion (12345) down the column, until
> the next row I need to copy (same format) shows up.  What's the
> best/quickest way to do this.  It's like an =IF function, but I'm not
> sure of the syntax.
> 
> Thanks
> 
> 
0
Kleev (20)
1/6/2006 11:04:01 PM
Thanks to everyone who responded.  What I found that works is using the
=MID function.  This did exactly what I needed.

0
1/6/2006 11:58:23 PM
Reply:

Similar Artilces:

column headings
What do all the report column headings stand for? MTD, MAT, LYM etc. I understand most, but need some idea to make these reports more meningful. Thanks. Beaver, Uh, which report? MTD is usually Month to Date. -- * "Beaver" <Beaver@discussions.microsoft.com> wrote in message news:776D5FD5-BC05-4565-81EF-9BB8A5A0BB16@microsoft.com... What do all the report column headings stand for? MTD, MAT, LYM etc. I understand most, but need some idea to make these reports more meningful. Thanks. Item movement history is the report I am in now. "Jeff" wrote: > Be...

Outlook 2003: Email address in 'From' column of Inbox.
Anyone know how to get the full email address to display in the 'from' column of the inbox in Outlook 2003? I have about half a dozen "Dave"s that all have different email address but they're indistinguishable in the inbox. Plus, I'm sick of just seeing someone's name when I really need to know the company name more than anything else. At least with a proper email address, I can easily work out if it's a legit email from a known company or spam. I don't want to have to read the post to see if it's valid. ------------------------------------------...

Strange behavior when copying formula.
Hi, Here is what I'm trying to do. I want to show the summation of the first two rows' number in the third row. I use the summation function to get the first result. Then, I copy and paste the result to the other two, but they show the same results as the first one. I check the formula of all three results, and they are all correct and referring to the right cells. Why is that? 1 2 3 1 2 3 2 2 2 Leo Do you have Tools/Options/Calculation set to Manual? If so, click the Automatic button. In article <eE#EeXFhDHA.2512@TK2MSFTNGP09.phx.gbl>, "Leo Leon...

Freezing? Rows
I have a question pertaining to Excel. I keep track of patients in excel sheet.. I add about 10 patients a week into this sheet. Then I sort it by patients’ name, so the sheet appears alphabetically. I know this is not the best way to keep the data... and I am afraid that by some mistake rows might get shifter and information for each patient will be shifted as well. Is there any way to prevent this kind of disaster from happening? Somehow "freezing" the information for each row for all the columns, so that individual patient's info cannot be shifted so easily? Any sugg...

Traspose Column data into Row
Hi, I want to transpose column data in to row. I have seen many posts (using INDEX or OFFSET functions) but all these solution assume a fixed block of data to be transposed, My problem is that the data i have not only has variable blocks to be transposed but also has some duplicate headings(headings are duplicate but the data in front of each heading has different value. Below is the example of data Col A Col B Col C PRODUCT COST COMPONENT COST$ A Raw Mat 10 A D L ...

resize all rows in the spreadsheet to fit the text?
I have an existing spreadsheet created by another user. Some of the rows are not automatically sized to display all text. Is there a command to resize all rows in the spreadsheet to fit the text? thanks email: softwaretest underscore 2003 at hotmail Church lady, try Ctrl a, to select the whole sheet, then format, row, autofit -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "the church lady" <tcl@mail.com&g...

2-D charting with multiple rows
I have a power user who wishes to increase the size of his 2-D graphics but is limited to using 32000 records. He needs to be able to use 64,000 records. Any suggestions? I think this might be something that is changed in regedit but I am not sure. Thanks! Yep, and you can bypass the 256 column limit with Windows API calls. Joking aside, how about two series, formatted identically. If you have duplicate entries in the legend, select the legend, then select the text of one entry, and press the Delete key. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorial...

Help with VBE Need to autoselect rows #3
Sorry and thanks for your help, i will be more patient in the future. I had to finnish a spreadsheet this morning and got carried away trying to find the answer, i will try the above suggestions out. Thanks jason -- greenfalcon ------------------------------------------------------------------------ greenfalcon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=13622 View this thread: http://www.excelforum.com/showthread.php?threadid=261680 ...

Automatically 'incrementing' formulas for new rows.
Here's my question. I am making a 'scoring' sheet for an online game. It is set up essentially as such... A B C D 1 (Player Name) (Question #1 Points) (Question #2 Points) =SUM(B1:C1) 2 (Player Name) (Question #1 Points) (Question #2 Points) XXXXXXX I would like D in each Row to add the sum of the previous two Columns for each player (represented by XXXXXXX). Do I need to manually input each formula for D? I have over 100 players and this will take forever! Is...

move rows from column to column
I have Column b with Name in one row and number in 2nd row want to move all numbers to Column a and leave name in Column b without having to move each one seperate. 1500 records. Use a help column, in the first adjacent cell type =ISNUMBER(A2) where A2 is the first cell with name copy down 1500 rows by either dragging the formula or double click the lower right corner of the help cell (as long as it is in an adjacent column) Then apply data>filter>autofilter and filter on FALSE in the help columns, select all visible cells and copy and paste somewhere else, then select TRUE from ...

Soft copy of training material
Hi all, I have completed the class room training of CRM 3.0 Installatio and Customization. I also got the printed training material. But soetimes, searching something in the book becomes very difficult. Can I have the soft copy of these training manuals? Does Microsoft provides it? We are Gold certified partners btw. If anybody has some idea about this, please let me know. Thanks, -Imran https://dynamics.microsoftelearning.com/eLearning should find everything from your course in the eLearning Courses (or at least, I'd presume so). Either way, the online courses are a handy thing ...

Bytes in Data Page Row
I am reading a book titled "Microsoft SQL Server 2008 Internals". In Chapter 6, which was authored by Kalen Delaney, Kimberly Tripp, and Paul Randall, the following table and Clustered Index is created: CREATE TABLE Employee( EmployeeID INT NOT NULL IDENTITY, LastName NCHAR(30) NOT NULL, FirstName NCHAR(29) NOT NULL, MiddleInitial NCHAR(1) NULL, SSN CHAR(11) NOT NULL, OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk') GO ALTER TABLE Employee ADD CONSTRAINT EmployeePK PRIMARY KEY CLUSTERED (EmployeeID) GO It states on page 321 of the aforementione...

Highlighting whole row in this macro
Can I add something to this so that when the A1:A100 field is changed, the whole row (A-L) is highlighted ColorIndex 6? Dim x, cl Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub Target.Offset(0, 1) = x Target.Interior.ColorIndex = 6 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) x = ActiveCell.Value End Sub target.entirerow.resize(1,12).interior.colorindex = 6 or since the target is already in column A: target.resize(1,12).interior.colorindex = 6 eastrivergraphics@gmail.com wrote: > &...

how to create row with data
imagine i have two rows: name and color: mary blue ken brown will blue what i want is having, in another row, just the names that have blue eyes, like mary will thank you On Mon, 11 May 2009 17:26:04 -0700, rafael <rafael@discussions.microsoft.com> wrote: >imagine i have two rows: name and color: >mary blue >ken brown >will blue > >what i want is having, in another row, just the names that have blue eyes, >like >mary >will Like: mary will or like: mary will ???? ty i have excel 2000 what i want is all names in a single row, wi...

Change column color in chart when column value is over/under goal
No use of VBA or macros expected. It is believed to be Excel chart feature. Any ideas on how to change column colors (Red/Green) if value exceeds or meets the preset goal. Eg. if goal is 4% - anything at or above 4 should show green and under 4 should be red. Assuming your data is in A2:A20 B2: =IF(A2>=4,A2,NA()) C2: =IF(A2<4,A2,NA()) copy B2:C2 down, add some labels to B1:C1, then chart B1:C20. This will give you two series, one for the aboves, one for the rest. Select each data series, right click, choose format, and set the colour as required. -- --- HTH Bob (there's ...

Software copy-protection strategies involving unlock codes
I would like to learn about strategies for protecting my software against illicit copying, by supplying users with unlock codes which they have to use to unlock the program's full functionality. Does anyone know of any good books on this topic, or other sources of information on it. TIA Simon I don't, although I've done a bit of work in this area myself. The first issue you need to deal with is how to stop a customer that has the code from being able to give it to someone else. I simply have the code tied to the customers name and company, if any. This way, they can st...

Linking columns for auto fill
Can I link two columns to auto fill Column A when corresponding data is entered in Column B? In A1 enter =IF(B1="","",B1) Copy down column A as far as you wish. Gord Dibben MS Excel MVP On Thu, 25 Mar 2010 09:11:01 -0700, Daisy <Daisy@discussions.microsoft.com> wrote: >Can I link two columns to auto fill Column A when corresponding data is >entered in Column B? ...

Hidding rows when sorting
Hi all I have a sheet, where I upload informaton - some rows are hidden (parts already in stock) So, when they sort by some coloumn, it shows all rows, and hides the one which does not match. Is there an event I can catch so I can hide even more? It is a file someone else made at some time, so: how do I add the sort option? WBR Sonnich "hides the one which does not match" Sounds like you are filtering, not sorting. Gord Dibben MS Excel MVP On Tue, 1 Jun 2010 09:46:57 -0700 (PDT), jodleren <sonnich@hot.ee> wrote: >Hi all > >I have a s...

Reference last entry in column
In Excel 2002, I need a way to reference the last entry in a column of data that has data added to it on a regular basis. Is there a way to do this that is fairly simple? I am trying to make a checkbook register in excel and I want to have the current balance shown in one place at the top so that when the list of entries gets long the user doesn't have to scroll to the bottom to see the current balance. I also want to have it show on a different sheet where the current balances for different accounts can all be seen one one page. Any help would be appreciated. Hi see: http://www.xl...

Lists
I have a user who is importing an excel spreadsheet into a Sharepoint List, but some of the column types that SP is selecting is incorrect. I can't find any place to change the type of these columns (like from text to text with link, etc). Is this possible? Yes, you can change the column type after importing. Just go to the list settings page and choose the column you would like to update. The very first section of the column settings page will be for the data type of the column. -RH "J. Belcher" wrote: > I have a user who is importing an excel sprea...

[External Copy or SYLK] file format is not valid
I am moving to a new computer. I have WIN98SE on my old computer and WIN XP on the new one. I have Excel 97 and I am trying to transfer my data to the new computer. I get the above message when I try to open a file in XP. What is the problem? Thanks in advance, Bill. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Copy from one publisher document to another
I am trying to help my father. He has created a bunch of individual publisher documents that he is now copying and pasting into one publisher document with multiple pages (he didn't know he could add pages originally). We are doing this by opening publisher twice and selecting all of an individual page and then copying. We then paste it into a blank page in the new multiple page publication. Our problem is that when we paste the items don't always land centered on the new page. Can anyone help us with this? My dad had Publisher 2003. Thanks, Barbara Select all, Cntrol A, then ...

limits on hiding columns
are there any conditions that would limit the number of columns that can be hidden in Excel? Alternatively, why would excel not hide any more columns? Spreadsheet is not protected by the way Hi AFAIK you can hide all columns except of one remaining one -- Regards Frank Kabel Frankfurt, Germany m99panama wrote: > are there any conditions that would limit the number of columns that > can be hidden in Excel? Alternatively, why would excel not hide any > more columns? Spreadsheet is not protected by the way You might be thinking of hiding sheets, Frank? I think you can hide all ...

Extra Rows in Excel 2007
I recently upgraded to Excel 2007 from 2002, and a lot of the spreadsheets seem to have something extra in the rows below the end of the data. Two examples: 1) I went to the bottom of the data in a spreadsheet and made a new entry. As soon as I hit enter, the spreadsheet started scrolling down and wouldn't stop for a couple of thousand rows. 2) I saved a spreadsheet as a csv file and another application that uses the file started having problems. So I opened the csv file in WordPad and saw a couple of hundred rows of commas. That never happened with Excel 2002. I opened the ...

Copy Posts from one Public Folder to Another
I have an Exchange 2003 Public folder tree that has multiple levels. I have a need for users to Post to one public folder and then have a copy of that Post copied to another public folder automatically. I have set this up for emails (plain forwarding) and it works fine but I have yet been able to get the Posts to duplicate to the other folder. Does anyone have any ideas? Your help is greatly appreciated. Thanks Glenn There are two ways to forward email out of a public folder: 1. Used a rule on the public folder. As owner of the public folder go to properties and to the folder assistant ...