• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

A couple Excel questions answered

Get PDF file

by on
in Excel Training,Office Technology

Changing case in Excel

Q. How do I change text case in Excel?

A. It seems like it should just be a button to click, right? Well, remember that Excel is processing data, not words. So, if you want it to change your data, you need to use a formula. There are a handful of text functions, and you should know to deal with text case. Let’s assume that this text string has been entered in A1.

   charlene WILson

=UPPER(A1) would deliver this: CHARLENE WILSON

=LOWER(A1) would give us: charlene wilson

=PROPER(A1) results in Charlene Wilson

Depending upon the outcome you need, one of these three functions will likely do the job.

VLOOKUP problems

Q. I’m trying to do a VLOOKUP to join two files based on phone number. But in one it’s using (aaa)nnn-nnnn, and the other is just numbers. Is there anything better than a Find/Replace?

A. Yes. You might be able to use the SUBSTITUTE function. First, be sure that your (aaa)nnn-nnnn data really contains those characters and is not simply formatted as a phone number. The easiest way to tell is to look in the formula bar. If you see the parentheses and dash in the cell, but not in the formula bar, the cell is only using the numbers. It has just been formatted with the Special Format of Phone Number. Assuming it is not that, you would want to do a nested SUBSTITUTE function. The SUBSTITUTE function allows you to “see” cell contents differently than it was entered, for example, with no dashes or parentheses. Starting with the left parenthesis, and assuming the phone number (312) 555-1212 was entered in A1, your formula would look like this.

=SUBSTITUTE(A1,”(“,””)

This basically says in A1, when you find a ( character, change it to null (empty double quotes). Now, here’s the fun part. You will wrap this SUBSTITUTE formula inside another to get rid of the right parenthesis.

=SUBSTITUTE(SUBSTITUTE (A1,”(“,””),”)”,””).

Pressing enter here would give you 312555-1212. So now wrap it one more time to get rid of the dash.

=SUBSTITUTE(SUBSTITUTE (SUBSTITUTE (F1,”(“,””),”)”,””),”-”,””)

You can either create a new column with this formula copied all the way down and use it for your lookup, or put the whole thing inside your VLOOKUP for the lookup value, like this:

=VLOOKUP(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE (A1,”(“,””),”)”,””),”- ”,””),LookUpArray,2,0)

As always, when doing compound, nested functions, start working in separate cells until you know your functions are working. Then assemble them into a nested function.

Leave a Comment