Excel Question #46

[Alex]

Originally Posted by Kevy Baby

I wonder if the time to create the above formula is less than just doing 12 search-and-replaces? Thanks for the tip though.

Well, it would be 13 search-and-replaces and then still one formula and column copy-and-paste (to convert the text to a date). And it took me about 90 seconds to do the formula.

The advantage of the formula is that you are less likely to make a mechanical error than doing a repetitive task 13 times.

But mostly it was with an eye towards this being something done more than once. If it is a one off then search and replace isn't much less efficient.

[Alex]

For the formula, when you paste into excel be carefule of extra white space. For example, vBulletin is adding a space between the SUBSTITUTE should always be one word so remove the occasional break).

[Ghoulish Delight]

F2

ETA: F2 also serves a similar purpose in Windows explorer. If you have a folder or file selected, F2 will bring you into filename editing mode (instead of having to do that click-and-wait thing I hate so much).

ETA more: ctrl-u on a Mac

[Kevy Baby]

Originally Posted by Ghoulish Delight

F2

Thank you - you are my hero!!!

Originally Posted by Ghoulish Delight

ETA more: ctrl-u on a Mac

Thank you for this. It is for work, where I work on a PC. Excel on the Mac sucks weenie

[mousepod]

Originally Posted by Kevy Baby

Excel on the Mac sucks weenie

My copy doesn't. Is there a special key I need to be hitting? And would it be cheating?

[Kevy Baby]

Is there a way to enter a manual line break in a concatenate formula?

For example, if:

Cell A1 = "Name"
Cell B1 = "Address"
Cell C1 = "City, ST Zip"

What would I need to enter in the concatenate formula so that cell A2 looked like this (regardless of column width of course):

Name
Address
City, ST Zip

Obviously, the starting formula is:
=CONCATENATE(A1,B1,C1)

I tried looking through the help menu but couldn't find anything




ETA: Never mind. I tried out this really cool thing called "Google" and it helped me find the answer: CHAR(10)

The formula would be:
=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1)

[Betty]

Originally Posted by Kevy Baby

Is there a way to enter a manual line break in a concatenate formula?

For example, if:

Cell A1 = "Name"
Cell B1 = "Address"
Cell C1 = "City, ST Zip"

What would I need to enter in the concatenate formula so that cell A2 looked like this (regardless of column width of course):

Obviously, the starting formula is:
=CONCATENATE(A1,B1,C1)

I tried looking through the help menu but couldn't find anything




ETA: Never mind. I tried out this really cool thing called "Google" and it helped me find the answer: CHAR(10)

The formula would be:
=CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1)

Hey - that a pretty neat trick. I learned something new today!

[Kevy Baby]

Okay, these two questions are a little different: is there a better way to accomplish the two tasks below?

1. Get a list of all entries in a column (in other words, if I have 50,000 records and column H has a listing by state, I want a list of the states covered in this list [obviously not all 50 states]).

1. Sort by Column H
2. Highlight Column H and use the Count Subtotal (Data > Subtotals).
3. Click on the "2" in the upper left corner to collapse the selection to just the counts (when you use the Count Subtotal, a small "1", "2", and "3" appear in the upper left corner of the sheet - this may have a name, but I don't know what that name is).
4. Highlight all of the Count Summary Sub-heads (again, this may have a name, but I am not sure what it is: it is the titles that appear in a new column that is created when the "Sub Total by Count" is run).
5. Select just the visible cells in that column (alt-;) and copy. Be sure to not select the last entry, which is the "Grand Count".
6. In a separate worksheet (or somewhere else), Edit > Paste Special > Values. Highlight all of those cells and using the Edit > Replace command, get rid of " Count" from each entry

2. Create a list of records with birthdays in a certain month (in this case, April).

1. Convert the Birth Date column to text. Note that this data came to me with the date already in a text format listed at YYYYMMDD (no dashes or slashes).
2. Create two new columns. In the first column, use the RIGHT function to reduce the date down to just the month and year (the last four characters): =LEFT(G2,4). In the second column, use the LEFT function to reduce the date down to just the month: =RIGHT(H2,2).
3. Copy/paste special this last column as values and then convert Text to Numbers. This leaves with numerical data of 1-12 in this column.
4. Auto-Filter the spreadsheet and then select for this column any number that isn't a 4.
5. Select all of the numbers remaining visible in this column (everything but the 4's) and then highlight only the visibles (alt-;). Delete (with the only option being the entire rows.
6. Under the Data > Filter menu, choose "Show All"

[Moonliner]

Originally Posted by Kevy Baby

1. Get a list of all entries in a column (in other words, if I have 50,000 records and column H has a listing by state, I want a list of the states covered in this list [obviously not all 50 states]).

2. Create a list of records with birthdays in a certain month (in this case, April).
[list=1][*]Convert the Birth Date column to text. Note that this data came to me with the date already in a text format listed at YYYYMMDD (no dashes or slashes).

When all you have is a hammer, every job looks like a nail.
-- Unknown.

Seriously dude, import this **** into a database.

#1 Then becomes: Select DISTINCT State from Your.bd

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'

[Kevy Baby]

Originally Posted by Moonliner

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'

I found a better to resolve for #2: I had the client provide me with records already suppressed to the month of April

Originally Posted by Moonliner

When all you have is a hammer, every job looks like a nail.
-- Unknown.

Seriously dude, import this **** into a database.

Well, yes, this might be a better tool. But then I would have to purchase and learn said new tool. If 95% of what I need to transport can be covered by a pickup truck, I am not going to buy a semi-truck and get a class A license for that other 5% - I'll just make a couple of extra trips as needed.

Besides, I got to learn how to make a lookup table today!