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 117005 times)

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3255
  • Total likes: 96
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #975 on: May 22, 2017, 02:38:42 PM »
Cool! BTW, for your second argument/parameter, you can just refer to the cell containing the source text. Then you could copy the formula for an entire column.
it's a table so it does go to the whole column. I click the cell it 'takes' that [column title]

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #976 on: May 22, 2017, 02:43:43 PM »
it's a table so it does go to the whole column. I click the cell it 'takes' that [column title]
Oh cool! I haven't dealt with tables much. So is @[כרך (א)] the name of your column?
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3255
  • Total likes: 96
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #977 on: May 22, 2017, 02:44:34 PM »
Oh cool! I haven't dealt with tables much. So is @[כרך (א)] the name of your column?
yes

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #978 on: May 22, 2017, 03:14:09 PM »
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #979 on: May 22, 2017, 11:32:10 PM »
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3255
  • Total likes: 96
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #980 on: May 23, 2017, 04:43:55 PM »
I'm trying to transpose last name first. like
Code: [Select]
Mr. Donald J. Trump to
Code: [Select]
Trump, Mr. Donald J.seems excel doesn't have a good way to identify "last" though regex should be able to
Code: [Select]
\s\w+\z but not managing to get that fit into the rest of the code
worked something out. replaced \z with $ and used multiple columns to concatenate as needed
« Last Edit: May 23, 2017, 05:10:08 PM by gozalim »

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #981 on: May 23, 2017, 06:18:17 PM »
I'm trying to transpose last name first. like
Code: [Select]
Mr. Donald J. Trump to
Code: [Select]
Trump, Mr. Donald J.seems excel doesn't have a good way to identify "last" though regex should be able to
Code: [Select]
\s\w+\z but not managing to get that fit into the rest of the code
worked something out. replaced \z with $ and used multiple columns to concatenate as needed
With regex there's pretty much always a way :)
Code: [Select]
=regexreplace(A2,"(.*) (\w*)","$2, $1")
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2645
  • Total likes: 48
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #982 on: May 24, 2017, 10:56:14 AM »
I'm trying to transpose last name first. like
Code: [Select]
Mr. Donald J. Trump to
Code: [Select]
Trump, Mr. Donald J.seems excel doesn't have a good way to identify "last" though regex should be able to
Code: [Select]
\s\w+\z but not managing to get that fit into the rest of the code
worked something out. replaced \z with $ and used multiple columns to concatenate as needed

The trick is to concatenate 2 strings together
Code: [Select]
Mr. Donald J. Trump Mr. Donald J. Trump then work from there removing until the second period and the last word etc.

Offline talmid chuchem

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2014
  • Posts: 977
  • Total likes: 41
  • DansDeals.com Hat Tips 5
    • View Profile
Re: Excel Problem
« Reply #983 on: June 04, 2017, 03:36:46 AM »
With regex there's pretty much always a way :)
Code: [Select]
=regexreplace(A2,"(.*) (\w*)","$2, $1")

The trick is to concatenate 2 strings together
Code: [Select]
Mr. Donald J. Trump Mr. Donald J. Trump then work from there removing until the second period and the last word etc.
Why not just use the right () function, & the select left() - count(right())?

Could be I mentioned the wrong function to count the letters, haven't done it for some time.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #984 on: June 04, 2017, 07:59:02 AM »
Why not just use the right () function, & the select left() - count(right())?

Could be I mentioned the wrong function to count the letters, haven't done it for some time.
I've done it that way, but regex is simpler (once you know how to use it) since you don't need to fuss with nested functions.
Workflowy. You won't know what you're missing until you try it.

Offline talmid chuchem

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Oct 2014
  • Posts: 977
  • Total likes: 41
  • DansDeals.com Hat Tips 5
    • View Profile
Re: Excel Problem
« Reply #985 on: June 04, 2017, 04:23:12 PM »
I've done it that way, but regex is simpler (once you know how to use it) since you don't need to fuss with nested functions.
What's regex?

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4720
  • Total likes: 318
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #986 on: June 04, 2017, 05:04:05 PM »
What's regex?
The past 6+ pages of this thread.
Once the game is over, the king and the pawn go back in the same box.

Offline User6669

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 3326
  • Total likes: 154
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
".איזהו חכם, הלומד מכל אדם"

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5552
  • Total likes: 1150
  • DansDeals.com Hat Tips 259
    • View Profile
Re: Excel Problem
« Reply #988 on: June 19, 2017, 12:08:05 PM »
Does anyone know a formula to reverse the order of text within a cell?

EG: the text TCRVO is אברהם in hebrew but I have it written in excel as OVRCT so is there any way to reverse the order?

TIA
If it's not free shipping it's not worth it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 10962
  • Total likes: 1045
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #989 on: June 19, 2017, 12:13:40 PM »
Does anyone know a formula to reverse the order of text within a cell?

EG: the text TCRVO is אברהם in hebrew but I have it written in excel as OVRCT so is there any way to reverse the order?

TIA
You can try this: https://superuser.com/questions/121618/any-excel-function-that-will-reverse-a-string

The second answer looks better than the first.
Workflowy. You won't know what you're missing until you try it.