Excel Spreadsheet Formulas for the Week

I'm in charge of the direct mailing at my office. Our leads come in via Excel sheets, which get fed through RoboAgent, and out pops an auto quote.


We recently had Database101 run our previous leads through their database to see if we could collect any phone numbers and/or emails.


We have 8 separate leads files, and 1 big amended file (with all the leads in no particular order). In order to get the quotes updated, I have to use the ReQuote feature, but wanted to update the individual leads files with phone/email. I opened the leads file, and copy/pasted the Amended file as Spreadsheet 2. So the Phone and Email columns were empty in Spreadsheet 1. Be sure that your columns are aligned! (ie if Phone column in Spreadsheet 1 is column C, then it better be C on Spreadsheet 2).  I used the following 2 links for the formula, just adjusting the columns to match and transplant:


=IF(ISNUMBER(MATCH($C7, Sheet2!$C:$C, 0)), INDEX(Sheet2!H:H, MATCH($C:$C, Sheet2!$C:$C, 0)), "")


where C is the address field and H is the email field. Whatever data colum field you need drug in, switch out H. In my case, G was the phone, so I used this formula twice in my spreadsheet.


http://www.excelforum.com/excel-general/675712-merge-two-workbooks-copy-column-data-based-on-numerical-id-match-of-another-column.html


http://www.excelforum.com/excel-worksheet-functions/652821-multi-speadsheet-information-merge.html


Then I discovered that my phone numbers were written xxxxxxxxxx. Well, RoboAgent has to fill out the area code field and then the number. How to split the area code from the rest of the number? No problem. The easiest answer was Norie's:


Select the column, goto Data>Text to columns..., selected fixed width. 


On the next step isolate the first 3 digits from the rest of the data. 


http://www.ozgrid.com/forum/showthread.php?t=78312


It did split the column heading as "Pho" and "ne"--an easy thing.

Comments

Anonymous said…
actually, for extracting the first three characters from any cell, you can place the following funtion in a different cell

=LEFT($A2,3)

This assumes that the telephone number is in column 'A'. Copy the formula all the way down the page, then copy the whole column and paste values to remove the formula. You can then extract the right 7 characters the same way into another column by using

=RIGHT(A2,7)
March 14, 2012 at 10:12 AM
Anonymous said…
Thanks for the great tip!
March 16, 2012 at 2:41 PM
Post a Comment

Popular posts from this blog

2015 Ford Fusion Replacement Rear Deck Subwoofers

Image
I bought a used 2015 Ford Fusion Titanium earlier this summer and didn't realize one of my rear subs was blown until after purchase. The Sony replacement through my Ford dealer was $80 for ONE, which seemed a little high. I scoured the internet for hours trying to find an affordable/better replacement, which is harder than you think since the original Sony speakers (BG1T-18971-CA) are 2.3 ohms (30w). Evidently, 2ohm subwoofers are a rarity. I called a couple audio shops in town and they had never even heard of them! I also did not want to replace true subs with a 3 way speaker. Finally after hitting several forums, I found CDT Audio (http://www.cdtaudio.com) having the 6x9 2 ohm subwoofers I needed! I emailed and called to make sure I was getting the right stuff, and ordered a pair of official refurbished CL-69Sub/CF for $100 (and cheap shipping!) http://www.cdtaudio.com/sep_components/subwoofers/cl69subcf.php I got in my trunk and looked to see if replacing them myself was i
Read more

Shark Navigator Swivel LEAD WARNING

Image
I was at Costco this weekend and was looking over the box for the Shark Navigator Swivel UV410. On the bottom of the box is the following warning: It reads: WARNING: This product contains chemicals, including lead, known to the State of California to cause cancer, and birth defects and other reproductive harm. Wash hands after handling. Scary AND inconvenient
Read more

Camp Hero State Park, Montauk NY

Image
Above: Trail map of Camp Hero State Park in Montauk NY On our last weekend on Long Island, we opted to skip going to the hustle-bustle of Manhattan and instead head East, past the Hamptons, to the very end of Long Island. On our way to  Camp Hero State Park  we had to stop off at the original  Lobster Roll  for, what else, lobster rolls! We grabbed them to go and kept heading east. A tip for all visitors: they aren't kidding about the ticks! Be sure your pet is up to date on their repellent, and I recommend human repellent too! I found several small ticks on my nether-regions, and a few chiggers made nice work of my legs too. We parked just south of the main entrance and ate our lunch along the bluffs near Battery 216, which was a small battery, nothing notable, though the view of the Montauk Lighthouse is impressive. Montauk Lighthouse, from Camp Hero Bluffs We then explored the trail near Battery 113 and climbed a steep trail to get onto the roof of the battery.
Read more