Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 4255
  • Total likes: 812
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 4255
  • Total likes: 812
  • 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: 4255
  • Total likes: 812
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 4255
  • Total likes: 812
  • 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: 4255
  • Total likes: 812
  • 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: 4255
  • Total likes: 812
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 4255
  • Total likes: 812
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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: 4255
  • Total likes: 812
  • 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!
וואס איז פשט?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #915 on: May 15, 2017, 12:36:16 PM »
Also, if you have the parsha in separate cells, can you just not run the find/replace on that column?
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: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #916 on: May 15, 2017, 12:36:55 PM »
looks good!
וואס איז פשט?
Pipeline is "or". So you're looking for \A (start of string) OR space OR hyphen. You can add in more options if you need.
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 4255
  • Total likes: 812
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #917 on: May 15, 2017, 12:37:20 PM »
Also, if you have the parsha in separate cells, can you just not run the find/replace on that column?
yes.
it's finding matos where that's the מ"מ of a zohar quote...

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #918 on: May 15, 2017, 12:37:58 PM »
yes.
it's finding matos where that's the מ"מ of a zohar quote...
aha
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 4255
  • Total likes: 812
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #919 on: May 15, 2017, 12:43:12 PM »
Pipeline is "or". So you're looking for \A (start of string) OR space OR hyphen. You can add in more options if you need.
thanks.
how do I not loose the space/hyphen/comma?