Excel  using a range (in one cell) in a formula
eflan
Hello,
I have a number of excel spreadsheets related to ticket sales that I'm trying to get organized. In one, we're keeping track of the actual ticket number (the one stamped on the ticket itself). Is there a way to use a range that's in one cell in a formula?
For example, I have numbernumber (ie 11111112) in the cells. Is there a way to extract those numbers in order to get a count? Using extra cells is fine, I would just really like this scripted. Or would I need to manually convert them all into two cells vs just one per ticket type?
I'm taking over some office work for a music festival, and I have a couple hundred incomplete entries that need to get sorted.
Thanks in advance.
Erin
I have a number of excel spreadsheets related to ticket sales that I'm trying to get organized. In one, we're keeping track of the actual ticket number (the one stamped on the ticket itself). Is there a way to use a range that's in one cell in a formula?
For example, I have numbernumber (ie 11111112) in the cells. Is there a way to extract those numbers in order to get a count? Using extra cells is fine, I would just really like this scripted. Or would I need to manually convert them all into two cells vs just one per ticket type?
I'm taking over some office work for a music festival, and I have a couple hundred incomplete entries that need to get sorted.
Thanks in advance.
Erin
3
answers
Last reply
More about excel range cell formula

If you have a standard 2 values per cell (like your example), you could just count each cell as 2. I have a feeling that it's not consistent (meaning, sometimes you have 11111112, and other times you have 11131120), otherwise you'd have figured that out on your own.
So, diving deeper...
You could insert a math function between the values. I'm struggling to remember how to get Excel to enter an "=" before the numbers in mass/batch. This would result in a negative number, but you could still add those up at the end, just dropping the negative, and that would be your "count".
The only way I've figured out to do this, that works, is the following:
 Create a .csv file manually. E.g., right click on your desktop or a folder where you want this to exist, select "New/Text Document". Before using this TXT, rename to any name and change the .txt to .csv. If you've done it successfully, it should change from a notepad icon to a funny looking Excel icon.
 Open this new CSV with Notepad NOT Excel.
 Copy your column of numbernumber and paste it into the CSV file.
 Do a find & replace; In the find field put a single dash/minus '' Or however the data is separated. In the replace field put a comma plus a single space ', ' then click "replace all"
Your data should then look like "number, number". If not, modify the above Find&Replace with the right characters to get it that way. If your data is inconsistent, you're going to have a lot of work to make it that way before this will work.
 Once the data looks like number, number save the csv file and close it from Notepad and then open it in Excel. Those two values should now appear in their own cells.
 In cell C1 you can execute your SUM function of cells A1 and B1. Then copy C1 and select cells C2 down to however far you need to go and paste that once.
 If you do the SUM like the following, you will end up with a positive number, so no messing around with cleaning up the data:
=(B1A1+1)
 Then you can add up all these "counts" in col C and you have your ticket count.
If you wanted to then include this data in your main XLS file, you could just copy and paste columns A:C without messing up the data. Could clean it up in the XLS by hiding the columns that hold the values from A&B from the CSV, showing only the values from C...
I know there are a couple other ways of doing this, but they're escaping me at the moment...
Hope this helps
EDIT: Edited the formula to add a +1, because this is a "counting" exercise, not a "value" exercise.
In the event the person/people who did data entry made the first entry as:11111112 and the second 11121113 and the third 11141115, then there is not going to be an easy way to do this. But as long as nothing overlaps (e.g., 11111112, 11131115, 11161120, 11211122) then as stated above will work. 
Unfortunately, there's going to be overlaps: 3 days of ticket sales with two levels each. Fortunately, each day/level is in its own column... but that means I'll have to do 6 conversions. Yippee.
Thank you a ton for your help  I'll play around with it later on tonight after I'm caught up preparing 3day passes. I'm sure that this will be an excersise in patience, but it will be easier than trying to go through 150+ entries and doing the math manually.
I REALLY appreciate your help.
Ask a new question
Read More
Office
Formula
Excel
Internet Explorer
Apps
Related Resources
 Excel Formula
 Excel APPCRASH office 2000
 How do i check who has locked excel 2010
 How to select item using combobox in msexcel?
 In Excel 2007, can I drag a cell to another column or row?
 Excel linking documents within formula help  Suddenly not working
 I replaced an excel file with the other one how do i recover it
 How to create an empty cells in excel file
 Can't insert cell to right
 Automatic number sequence in excel
 Excel formula help needed
 How to convert excel file 1997 to 2010
 How to recover overwritten excel file
 Office
 Home xp / office 2000 pro  excel help