Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 79433 times)

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4365
  • Total likes: 192
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #120 on: May 22, 2014, 12:01:11 AM »
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.
« Last Edit: May 22, 2014, 12:05:06 AM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3706
  • Total likes: 11
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #121 on: May 22, 2014, 12:01:19 AM »
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.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7106
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #122 on: May 22, 2014, 12:03:24 AM »
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.

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 473
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #123 on: May 22, 2014, 03:27:39 PM »
well thanks to all ddf'ers
i opted for the most simple way, =proper(a1) then dragged down, copied and pasted values, it worked!

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 473
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #124 on: May 22, 2014, 03:30:06 PM »
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

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7106
  • Total likes: 252
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #125 on: May 22, 2014, 03:33:45 PM »
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.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9228
  • Total likes: 279
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #126 on: May 22, 2014, 03:35:29 PM »
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.
Workflowy. You won't know what you're missing until you try it.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3706
  • Total likes: 11
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #127 on: May 22, 2014, 03:40:52 PM »
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)

 
« Last Edit: May 22, 2014, 03:47:44 PM by MarkS »

Offline unavailable

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2013
  • Posts: 473
  • Total likes: 7
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #128 on: May 22, 2014, 06:32:56 PM »
Thanks it's really a help

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5047
  • Total likes: 10
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #129 on: May 22, 2014, 06:41:15 PM »
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  ;)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2745
  • Total likes: 126
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #130 on: May 22, 2014, 08:53:38 PM »
FTFY  ;)
I always look for smallest number of characters in the formula:

=A1&" & "&B1

Offline EJB

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 4845
  • Total likes: 129
  • DansDeals.com Hat Tips 14
    • View Profile
Re: Excel Problem
« Reply #131 on: May 22, 2014, 09:03:05 PM »
Maybe it's too simple ;)

Yup. You learn something every day.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4365
  • Total likes: 192
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Problem
« Reply #132 on: May 22, 2014, 09:08:59 PM »
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?
Once the game is over, the king and the pawn go back in the same box.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2745
  • Total likes: 126
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #133 on: May 22, 2014, 09:22:21 PM »
+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.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3706
  • Total likes: 11
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #134 on: May 22, 2014, 09:36:38 PM »
+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