Excel Reports help

[Tubacap]

I'm looking to quickly email everyone with a particular specialty in any given unit.  I can pull an excel report with every member trained or trainee from eServices that lists their name and all their personal information.  Unfortunately, the phone and email address are all in the same column, so I can't just select the column and copy and paste.  Does anyone know of a way to extract just the emails from excel?

It's a resource report from Ops Quals reports downloaded as an excel.

Thanks!

[Eclipse]

What's the delimiter in the combined column?

You should be able to copy that column out, save it as a CSV, and break it into seperate columns, or import it and break it
up via the import tool.

[arajca]

You should be able to resize the columns. I have found that when properly sized, the phone and email info are in separate columns.

[Tubacap]

Eclipse
For the Excel dumb, how do I do that?

arajca
Tried that, and that's how it used to be, but now it lists it all in the same column.

[Eclipse]

Eclipse
For the Excel dumb, how do I do that?

PM with an email and send me the file, I can do it in about 30 seconds.

There's a few different ways.

Copy the column into notepad, save as a .txt file, then reopen with Excel.  The import wizard should see the spaces and ask you how you want to
separate.

Open that .txt with notepad and do a global replace of the space with a ",", save as a .csv and reopen with excel.

There's probably 12 more elegant ways to do it as well.

[coudano]

If you are pulling the resource report from ops quals in e-services, it's not a question of resizing columns
this isn't a capwatch pull, as far as i can tell that he is referring to



Looks like the A column lists contact type (EMAIL, HOME PHONE, CELL PHONE)
And column B lists the contact info (the actual data)
Different people have different numbers of contact types (some have only 1, some 2, and some 3+)

The row preceding the first contact info has no type (blank A column), and the B+ columns hold the member's name, capid, etc etc etc


You could do something like go over to column M and use a formula like this
=IF(A5="EMAIL",B5,"")

"If contact type (A5) is EMAIL then put the contents of contact data (B5) into this cell or else leave this cell blank"

Then iterate that field down the entire N column
This will only populate the N column if the A column says EMAIL (it will skip the HOME PHONE and CELL PHONE) entries

Then you can copy the N column and paste it into an email client
You might have to add a , or ; delimiter between each email address, in order to make it resolve
looks like gmail is smart enough to do it automatically for you

You can alter the above formula to add a delimiter for email
=IF(A5="EMAIL",B5&",","")
just change the red , to ; if you need a semicolon instead of a comma or whatever

[coudano]

example file attached
won't let me attach a xlsx
so i'll make it into a pdf

[starshippe]

. . first off, admittedly this is a less than intuitively obvious method of obtaining exactly the same result as the "if email" example. it is only offered to demonstrate some of the capabilities of cell functions.

. . secondly, this is not a delimiter problem. the email addy is the sole occupant of the cell.

. . considering the following, the find function looks for an "at" sign in row x of column b. this function will return either a number, showing the position of the at sign, or #value!, if none is found. the isnumber function will return a true if the find function returned a valid number, or a false if it didnt. the if selects column b, followed by a comma, if the isnumber function returned a true, and a blank if it returned a false.

. . =IF(ISNUMBER(FIND("@",$B34)),$B34&",","")

. . obviously this is going to give u a problem if anyone has an @ sign in their phone number, but again, its purpose is to demonstrate functions and how they can be nested. as in the if email example, this would also have to be iterated down the entire column.

bill