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

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #900 on: May 15, 2017, 12:18:11 PM »
How does regex work with hebrew?
shouldn't
Code: [Select]
\bטו\bfind th טו in
Code: [Select]
טו, ?
=regexreplace(A1," טו"," יה") should do it. If you include a space before each number (like in my example) then you shouldn't mess up other words that include those letters, hopefully.
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #901 on: May 15, 2017, 12:21:03 PM »
=regexreplace(A1," טו"," יה") should do it. If you include a space before each number (like in my example) then you shouldn't mess up other words that include those letters, hopefully.
problem with space before is line beginnings. or do i just do a separate search with "^טו"? if so, do i even need gdocs/regex?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #902 on: May 15, 2017, 12:22:10 PM »
problem with space before is line beginnings. or do i just do a separate search with "^טו"? if so, do i even need gdocs/regex?
Do lines ever start with the number, or do they always have a parsha before?

If you're going to do a regular find and replace, you shouldn't need gdocs.

Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #903 on: May 15, 2017, 12:22:52 PM »
Do lines ever start with the number, or do they always have a parsha before?

If you're going to do a regular find and replace, you shouldn't need gdocs.


parsha is in it's own cell

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #904 on: May 15, 2017, 12:24:26 PM »
part of my issue is i needed \W not \w (I need non-word characters)
« Last Edit: May 15, 2017, 12:29:43 PM by gozalim »

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #905 on: May 15, 2017, 12:25:54 PM »
parsha is in it's own cell
Aha. Then you could easily use regexextract to split the perek and pasuk into separate cells, then you could go the vlookup route and then sort by the vlookup columns.

You may find it simpler to store the perek and pasuk as numbers, and then use a column with formulas to display it with the hebrew letters.
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #906 on: May 15, 2017, 12:26:09 PM »
part of my issue is i needed \W not \w (i need non-word characters.
where?
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #907 on: May 15, 2017, 12:29:20 PM »
You may find it simpler to store the perek and pasuk as numbers, and then use a column with formulas to display it with the hebrew letters.
that would probably have been a great way to operate to begin with, question would be if can convert now...
Aha. Then you could easily use regexextract to split the perek and pasuk into separate cells, then you could go the vlookup route and then sort by the vlookup columns.
column includes other format data (general list of מקורות) so splitting lines would get messy
« Last Edit: May 15, 2017, 12:33:31 PM by gozalim »

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #908 on: May 15, 2017, 12:30:41 PM »
where?
find every טו which isn't part of a word
i.e. preceded by a non letter (either a line begining, a space, or a -)

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #909 on: May 15, 2017, 12:32:09 PM »
part of my issue is i needed \W not \w (I need non-word characters)
not that great.
Code: [Select]
\Wטוstill pulls up "מטות"

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #910 on: May 15, 2017, 12:33:03 PM »
find every טו which isn't part of a word
i.e. preceded by a non letter (either a line begining, a space, or a -)
Does \Wטו work?
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #911 on: May 15, 2017, 12:33:49 PM »
Does \Wטו work?
not that great.
Code: [Select]
\Wטוstill pulls up "מטות"

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #912 on: May 15, 2017, 12:33:59 PM »
not that great.
Code: [Select]
\Wטוstill pulls up "מטות"
Hmmm, I wonder if it thinks hebrew letters are non-word characters.
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9776
  • Total likes: 438
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #913 on: May 15, 2017, 12:34:31 PM »
How about (\A| |-)טו ?
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3153
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #914 on: May 15, 2017, 12:36:00 PM »
How about (\A| |-)טו ?
looks good!
וואס איז פשט?