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 106820 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 #930 on: May 15, 2017, 01:56:03 PM »
and yet if the range includes anything matching
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))
טו
[א-ת]
(in 1 line)
 (e.g. יהושע) would still mess up/need a manual fix
Because I think you want first \A or - or space or ^, and THEN another OR group for the letters.
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 #931 on: May 15, 2017, 02:02:12 PM »
Because I think you want first \A or - or space or ^, and THEN another OR group for the letters.
the letters isn't what messes up יהושע. (note the ו in there). what throws it off is that we aren't filtering by those followed by letters
(in a sense we want to say:
preceded by "\A or - or space or ^ etc."
EXCEPT WHERE
followed by:[א-ת]

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 #932 on: May 15, 2017, 02:38:46 PM »
the letters isn't what messes up יהושע. (note the ו in there). what throws it off is that we aren't filtering by those followed by letters
(in a sense we want to say:
preceded by "\A or - or space or ^ etc."
EXCEPT WHERE
followed by:[א-ת]
So then maybe we need another OR group for "end of string / space / hyphen / etc", after the טו.
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 #933 on: May 15, 2017, 02:40:00 PM »
So then maybe we need another OR group for "end of string / space / hyphen / etc", after the טו.
then you run into those words that end with יה\יו
« Last Edit: May 15, 2017, 02:55:21 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 #934 on: May 15, 2017, 02:43:19 PM »
then you run into those words that end with יה\יו
Why? We aren't replacing those strings, we're replacing טו and טז
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 #935 on: May 15, 2017, 02:46:02 PM »
Why? We aren't replacing those strings, we're replacing טו and טז
misunderstood your goal. addding the same list at the end narrows those out. good.
just replace A with Z and ^ with $.

lemme test that
« Last Edit: May 15, 2017, 02:55:07 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 #936 on: May 15, 2017, 02:55:51 PM »
So then maybe we need another OR group for "end of string / space / hyphen / etc", after the טו.
will that play nice with
$1

?

ETA: seemed initially like not
(was somehow excluding any "טו," and when i added "|," to the script, it got the comma somehow removed.
was an easy enough fix in my case, as specifically commas (as opposed to other breaks) were always supposed to be accompanied by a space... so was able to replace all "טו "with "טו, "

worse problem was : קטו with $1 became טו...
« Last Edit: May 15, 2017, 03:08:46 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 #937 on: May 15, 2017, 03:08:56 PM »
will that play nice with
$1

?

You'll want to include parentheses around that OR group as well, and you can plug it into the result with $2
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 #938 on: May 15, 2017, 03:09:19 PM »
(was somehow excluding any "טו," and when i added "|," to the script, it got the comma somehow removed.
was an easy enough fix in my case, as specifically commas (as opposed to other breaks) were always supposed to be accompanied by a space... so was able to replace all "טו "with "טו, "
not following
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 #939 on: May 15, 2017, 03:10:30 PM »
worse problem was : קטו with $1 became טו...
That's because you included a ק in your first OR group
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 #940 on: May 15, 2017, 03:19:52 PM »
not following
if we're having comparisons both before and after the text, how do we ensure that it always chooses the right 'section' of the search to replace?

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 #941 on: May 15, 2017, 03:40:27 PM »
if we're having comparisons both before and after the text, how do we ensure that it always chooses the right 'section' of the search to replace?
You'll add in the first OR group with $1, and the second one with $2. Assuming that I'm understanding your question correctly.
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 #942 on: May 15, 2017, 03:41:42 PM »
You'll add in the first OR group with $1, and the second one with $2. Assuming that I'm understanding your question correctly.
If you're doing them both separately, you run into my original question

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 #943 on: May 15, 2017, 03:42:35 PM »
If you're doing them both separately, you run into my original question
Each OR group is surrounded by parenthesis, you do them both at once. You'll reference them in your replacement string with $1 and $2.

Can you post the formula you're working with now so that we're on the same page?
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 #944 on: May 15, 2017, 04:10:30 PM »
If I run
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))טו((\A| |-|^|,))
in a single search than I run into problems with
Code: [Select]
$1יה

and If I do them separately, 1st
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))טוthen
Code: [Select]
טו((\A| |-|^|,))
what stops the 1st search from catching יהושע? or the last on from catching ירמיה?