I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.

17,43,61,63 17 43 61 63

23,29,53,57,77,79 23 29 53 57 77 79

17,29,63,69,71,75,79 17 29 63 69 71 75 79

11,43 11 43

57 57

I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.

I have column of cells containing entries such as V1, V3 and V7. I'm trying (and failing) to come up with a formula to separate the numbers from the letters and add them together: V1, V3 and V7 would together give 11.

I can use the MID worksheet function to separate my numbers from my letters on a row-by-row basis, but I can't work out how to do the whole thing in one fell swoop. =SUM(MID(A1:A10,2,2)) doesn't work, for example.

the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).

so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225

to extract from a single cell: =VALUE(RIGHT(D8;4))

I tried to use an array formula but it seems to crash if a blank cell is in the array

I am looking for a formula which will sum numbers with less than symbols in front of them (i.e. they are text cells), and then replace the less than symbol again in the summed cell if initially present. The problem is that not every cell contains a < symbol, some of them are just numbers, and not every column contains a < symbol either.

e.g. I am looking for a formula which if entered in row 5 of this example would give these answers.

my spreadsheet has duplicate serial numbers in column A and the corresponding row in column B has mutliple descriptions for the same serial number. I need to combine those descriptions into 1 cell rather than having multiple rows for the same serial number. is there an IF formula that I can combine with a concatenate that will capture what I need?

A B C D E F G 23445566894 Brwn pdc aft Cat1 45687930596 $5 45687930596 Gld wdget adi Cat2 23445566894 $6

In the example above, I would like to do a vlookup in column C that matches the digits in column A to those in column F, with the output being the corresponding value in column G. I believe it involves the left function but not sure how to really use it here.

I am looking for a time saver macro,pretty easy to make i guess,as the theory is not difficult...but i am too newbie to make it. So i have an xls that has like 20k lines on Column A!And i have to seperate the numbers. I count the first 4 digits and I have to do it by adding a cell between them.

Example : 27289802 27289902 27289915 27289915 (add a null line) 27290202 27290302 27290316..................

When I select the cell formula extension button thing and drag it to the right, the formula copies exactly into the next cell. If I select multiple cells starting from 19-23 and then drag, excel just repeats the formula over, (19, 20, 21, 22, 23, 19, 20, 21, 22, 23, 19, 20...) This is seriously frustrating as I have to click into the formula bar on each cell and change the value of the column lookup.

I can't keep going manually as I need to go to about 300 cells...

I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.

What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.

I need only the the data in Cells A1 A5 etc. which means the 3 cells below (A2, A3, A4 ) should be deleted. A5 I need the data, and then A6, A7, A8 I do not need ... A9 need and so on.

the data should be in in one below the next with no spaces in between.

I have cell A1:A25. each with a number, and the same text "Hour(s)" So cell A1 would be 24 Hour(s) and so forth down to cell A25.

Due to the sheet being large, I can't just insert another column to list the text. I need for cell A26 to sum the numbers ignoring the test in the cells. I searched to forum, but didn't exactly find anything that works correct.

I want to separate the text and numbers into two different cells. Basically, I have the entire drawing details in one cell and I need to split it up, e.g. I have:

I do my indoor cricket team stats and I am having some trouble figuring out what formula to use in order to be able to sum a row of numbers where some cells may contain text and numbers or just text.

Please see the attached spreadsheet to understand what I'm talking about.

In the batting tab, when the batter gets out, they lose 5 runs. Outs are noted by St, R, B or C. In terms of runs, the scorers put wides and noballs to the batter on strike which are noted by W or N and this is worth 2 runs. Sometimes the batter on strike will get runs off a W or N so it's noted down as W+2 (which would give 4 runs) etc. So what I need to be able to do in the batting sheet is sum the total runs and count the number of outs.

In the bowling sheet, it's the same story as the batting tab, but I also need to be able to sum the number of extras given and count the number of wickets taken.

I have copied information from an outside source to Excel. Unfortunately, the information includes numbers preceding the text which I need to delete for all records. Is there an easier way of deleting this information without going to each individual cell to delete the numbers

I have the data below, there is alot more but I'll just be specific and i'll expand it myself to include the rest that I need.

I want to do sumif if in Column A that is between range R10 and R99, but I cannot figure out how to get the formula to work right since I have text instead of numbers. Also some of the other sumif's i need to do have only letters (for ex. RAA to RAZ or RDA to RDZ).

I generated my urls to online photos, I referenced cells where some are only numerical (ex. 479) while others contain a numerical/text mix (ex. 3014-RACK). Here is my url code in excel...

I need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.

I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Sc = "A" Sr = "1" With Worksheets(1).Range(Sc & Sr & ":A500").................

I want to change the character ~ with . in order to be able to make them numeric values to be feeded to other functions. But REPLACE seems not doing the job so I've been checking out other options such as seperating after and before the character ~. Details are below. I've been trying to use this formula to extract values from a delimited database which I open with excel. The formula that has brought me close is =IF(ISNUMBER(E51)=FALSE,LEFT(E51,LEN(E51)- FIND("~",E51)),E51)

14010~000 3210~0000

When I import the database, the figures above have originals as 14010.00000 & 3210.00000 but transfer to excel as above. As far as I have observed 9 character spaces are displayed & the DOT transfers to ~ for some reason. I need the LEFT section of the ~

I need to remove multiple instances of pages numbers from a 2000 character text string I have dumped into a cell. The spacing is not equal between the page numbers but they always take the form |#####-##| i.e. bar delimited 5 digit-2 digit. I tried SUBSTITUTE but it will not accept wildcards. I was thinking of looping through a SEARCH. The numbers are ordered ie |00001-01| to |00001-25| then |00002-01| etc, stopping at 25. This might lend to looping but I couldn't wrap my head around the VBA to accomplish that. A sample of the text

"|00006-01| (Defendants' Exhibit Nos. 1,2 marked for |00006-02| Identification.) |00006-03| BY MR. JOHNSON: |00006-04| Q. Doctor, I am handing you Exhibits 1 and 2. |00006-05| Exhibit 2, is that the one that you brought with you, |00006-06| the deposition notice of today? |00006-07| A. I believe so, yes." Auto Merged Post Until 24 Hrs Passes;I should have said this before but this is for use on Excel 2003 WinXP.

i m creating a macro to delete extra characters in a column. I have over 200,000.00 records in one column which consists of numbers with characters. For example:

if anybody can come up with a macro, so that I can just have the numbers and nothing else. I would like to exclude the following -~!@#$%^&*()_=+?/.";:|][{},^` within a cell.