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

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3783
  • Total likes: 357
  • 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: 12125
  • Total likes: 2174
  • 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: 2655
  • Total likes: 51
  • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2014
  • Posts: 1027
  • Total likes: 50
  • 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: 12125
  • Total likes: 2174
  • 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 Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2014
  • Posts: 1027
  • Total likes: 50
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5184
  • Total likes: 587
  • 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.

Offline User6669

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

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5997
  • Total likes: 1821
  • DansDeals.com Hat Tips 262
    • 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: 12125
  • Total likes: 2174
  • 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.

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 5997
  • Total likes: 1821
  • DansDeals.com Hat Tips 262
    • View Profile
Re: Excel Problem
« Reply #990 on: June 19, 2017, 12:16:45 PM »
If it's not free shipping it's not worth it.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5184
  • Total likes: 587
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #991 on: June 19, 2017, 12:18:49 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 you wanna do it on the Formula level, I found this online:

Code: [Select]
=IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))&IF(LEN(A1)<6,"",MID(A1,LEN(A1)-5,1))&IF(LEN(A1)<7,"",MID(A1,LEN(A1)-6,1))&IF(LEN(A1)<8,"",MID(A1,LEN(A1)-7,1))&IF(LEN(A1)<9,"",MID(A1,LEN(A1)-8,1))&IF(LEN(A1)<10,"",MID(A1,LEN(A1)-9,1))&IF(LEN(A1)<11,"",MID(A1,LEN(A1)-10,1))&IF(LEN(A1)<12,"",MID(A1,LEN(A1)-11,1))&IF(LEN(A1)<13,"",MID(A1,LEN(A1)-12,1))&IF(LEN(A1)<14,"",MID(A1,LEN(A1)-13,1))&IF(LEN(A1)<15,"",MID(A1,LEN(A1)-14,1))&IF(LEN(A1)<16,"",MID(A1,LEN(A1)-15,1))&IF(LEN(A1)<17,"",MID(A1,LEN(A1)-16,1))&IF(LEN(A1)<18,"",MID(A1,LEN(A1)-17,1))&IF(LEN(A1)<19,"",MID(A1,LEN(A1)-18,1))&IF(LEN(A1)<20,"",MID(A1,LEN(A1)-19,1))&IF(LEN(A1)<21,"",MID(A1,LEN(A1)-20,1))&IF(LEN(A1)<22,"",MID(A1,LEN(A1)-21,1))&IF(LEN(A1)<23,"",MID(A1,LEN(A1)-22,1))&IF(LEN(A1)<24,"",MID(A1,LEN(A1)-23,1))&IF(LEN(A1)<25,"",MID(A1,LEN(A1)-24,1))&IF(LEN(A1)<26,"",MID(A1,LEN(A1)-25,1))
This is only for 26 characters, if you need more you can extend the nested formula.
« Last Edit: June 19, 2017, 12:23:18 PM by lubaby »

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12125
  • Total likes: 2174
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #992 on: June 19, 2017, 12:19:20 PM »
Workflowy. You won't know what you're missing until you try it.

Offline SPLP

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2011
  • Posts: 1702
  • Total likes: 6
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #993 on: June 20, 2017, 12:46:11 PM »
Have excel on laptop and I save my doc's to laptop and Dropbox

some excel doc's on Dropbox do not open -and yet  other excel docs open as xlsk

this what i see when can not open a spread sheet
 
                             original xlsk
                   Office Open XML spreadsheet

how can I open?
« Last Edit: June 20, 2017, 12:49:32 PM by SPLP »

Offline SPLP

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2011
  • Posts: 1702
  • Total likes: 6
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #994 on: June 21, 2017, 02:28:07 AM »
sorry to bump so soon--but-- need the info on my I-pad and this is the only device I have with me

Offline Yammer

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2015
  • Posts: 3697
  • Total likes: 215
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #995 on: June 30, 2017, 11:10:52 AM »
I would like to move every second row to column B and every 3rd row to column C.

Anyway to do do it ?

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3357
  • Total likes: 539
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #996 on: June 30, 2017, 11:16:41 AM »
I would like to move every second row to column B and every 3rd row to column C.

Anyway to do do it ?
This is what I usually do. Set B1 =A2, C1 = A3 and D1 =1, and E1 =MOD(D1-1,3)=0. Copy that all the way down to the bottom of the list. Then select the whole group of value and copy/paste values. Now sort by column E and delete all the FALSE rows.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 289
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #997 on: June 30, 2017, 03:11:58 PM »
So I added fancy clickable (non macro) buttons to a a couple of thousand rows... Now it's all too slow. how do I get rid of em all?

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 289
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #998 on: June 30, 2017, 03:14:06 PM »
I would like to move every second row to column B and every 3rd row to column C.

Anyway to do do it ?
Just do it manually in two rows as = then drag down the pattern

Offline bermo

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Feb 2014
  • Posts: 1549
  • Total likes: 17
  • DansDeals.com Hat Tips 11
    • View Profile
Re: Excel Problem
« Reply #999 on: July 19, 2017, 06:20:44 PM »
i am trying to add up hours using =TEXT(G27-F27, "h")
is it possible to add a few times slot like 8-10 AM 2-4PM it gives me a error when i try
=TEXT(G27-F27,I27-H27, "h")or things similar
TIA