Excel Help and Problems

[lubaby]

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)

You can do it in any column you want. =proper(FIELD YOU WANT CHANGED)

The formula results (fixed) depend on the data that you are referencing. If you want to remove the original (bad) column, you need to first copy the column of fixed data and 'paste special' it as text only (that's under 'paste other' as the 123). Otherwise if you delete the original name column the fixed formula data will disappear too.
Or just leave it in.

[MarkS]

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)

Where is your data? If it's in Column A, put this formula in column B. If you already have something else in column B, add a column between A and B. Once you've put in the formula and have everything as you want it in Column B, copy the column, right click and hit paste special and select paste values. At that point you can delete column A and the corrected data will move over to column A.

[churnbabychurn]

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)

You can't operate a formula in the cell that you are referring to in your formula.

[unavailable]

well thanks to all ddf'ers
i opted for the most simple way, =proper(a1) then dragged down, copied and pasted values, it worked!

[unavailable]

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

[churnbabychurn]

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

Click Data, text to columns, play with it.

[etech0]

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

easiest way:
Click Data ribbon
Select the column with the data
Click Text to Columns, and use & as the delimiter.

[MarkS]

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 columns
If all columns start with a title, do text to column using & and space as your delimiters. 
If you want to then make 1 column showing Mr & Mrs, you can the do a concatenate using     =concatenate(A1," ","&"," ", B1)

 

[unavailable]

Thanks it's really a help

[bubbles]

Use text to columns

If you want to then make 1 column showing Mr & Mrs, you can the do a concatenate using     =concatenate(A1," & ", B1)

FTFY 

[skyguy918]

FTFY 

I always look for smallest number of characters in the formula:

=A1&" & "&B1

[EJB]

Maybe it's too simple

Yup. You learn something every day.

[lubaby]

I always look for smallest number of characters in the formula:

=A1&" & "&B1

+1
Never saw the point to 'concatenate' anything, since the =A1&B1 works great.
Is it something new that didn't exist in earlier excels?

[skyguy918]

+1
Never saw the point to 'concatenate' anything, since the =A1&B1 works great.
Is it something new that didn't exist in earlier excels?

I've seen some discussion as to which method is more efficient, but no one knows for sure. CONCATENATE is most likely the newer of the 2 methods, as & exists as an operator in many programming languages that predate Excel.

[MarkS]

+1
Never 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

[smurf]

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

[skyguy918]

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.

[smurf]

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.

you're right,I forgot how to use concatenate. I haven't used it in a while.

My issue with blank cells is that i often need to concatenate a range of cells with a delimiter between each one. The simple way to do it is to store the delimiter in a cell and reference it between each cell in the concatenate. However with a blank cell I'll then have 2 consecutive delimiter.
So I use udf

[unavailable]

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

[bubbles]

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

if they are duplicates what's the difference? you can highlight duplicates instead of deleting right away under condition formatting > highlight cells rules > duplicate values.

If you let us know what you are trying to accomplish maybe can be of more help