Do I have to do the formula in column b. Why can't I set this formula in a? (Sounds like a question of an illiterate,I know)
is there a way to remove the titles; Mr&Mrs, Rabbi&Mrs ETC. they all came jumbled in one column, how can I copy then to separate column?tia
Use text to columnsIf you want to then make 1 column showing Mr & Mrs, you can the do a concatenate using =concatenate(A1," & ", B1)
FTFY
Maybe it's too simple
I always look for smallest number of characters in the formula:=A1&" & "&B1
+1Never saw the point to 'concatenate' anything, since the =A1&B1 works great. Is it something new that didn't exist in earlier excels?
Sometimes =A1&B1 doesnt work (possibly when the cells are formatted diferently) and I've found concatenate to work mire consistently
and it's easier to combine more then 2 cells that way.Regardless I don't like the way either method handles blank cells so I use a borrowed UDF
Disagree on the first point. In what way is it easier? You have to put a comma in between each reference in CONCATENATE anyway, just sub in an ampersand and drop the CONCATENATE function.And what's the issue with blank cells? A blank cell has the value "", which is what it puts in the cell value when you use either method. UDF's are always a last resort for me. They're notoriously inefficient relative to built in functions.
hi when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?tia