Excel Question #46

[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!

[Moonliner]

Originally Posted by Kevy Baby

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

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!

Purchase is not a problem, if you have excel you probably already have Microsoft Access in house. That's all you would need. If not, MySQL (Which runs this site) is free for the downloading albeit with a somewhat higher learning curve than Access.

[Kevy Baby]

Originally Posted by Moonliner

Purchase is not a problem, if you have excel you probably already have Microsoft Access in house. That's all you would need.

Since I am, for the moment, still running on Office 2003, I have no Access. Alas, much to my chagrin, I am updating to Office 2007 Professional (the disk was just placed on my desk), so I have it will soon.

I have been reticent to move to 2007 as it seems that everything has been moved in Excel. But I need to be only three years out of date and not seven (we do get a free upgrade to 2010 as well).