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 287108 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 #920 on: May 15, 2017, 12:44:37 PM »
still limited to regex though. נו נו
thanks.
how do I not loose the space/hyphen/comma?
You're welcome. Do you mean when replacing?
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 #921 on: May 15, 2017, 12:45:51 PM »
You're welcome. Do you mean when replacing?
yes

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 #922 on: May 15, 2017, 12:49:50 PM »
yes
Hmmm... try this:
Add an extra set of parenthesis around the (\A| |-), like this: ((\A| |-))
Then at the beginning of the "replacement" section, add $1.

Basically, the extra set of parenthesis "captures" the result of that portion of the string, and then you can use it later via $1. (If you captured more than one portion, you'd access the subsequent portions with $2, $3, etc.)

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 #923 on: May 15, 2017, 12:54:13 PM »
Hmmm... try this:
Add an extra set of parenthesis around the (\A| |-), like this: ((\A| |-))
Then at the beginning of the "replacement" section, add $1.
find isn't finding...

(i'm seeing that the wildcard method is Find:(a)(b) Replace:\2.
wildcard and regex are mutually exclusive, though...)

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 #924 on: May 15, 2017, 12:54:53 PM »
find isn't finding...

(i'm seeing that the wildcard method is Find:(a)(b) Replace:\2.
wildcard and regex are mutually exclusive, though...)
Looks like it worked! thanks!

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 #925 on: May 15, 2017, 01:32:14 PM »
Looks like it worked! thanks!
Great! You're welcome!
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 #926 on: May 15, 2017, 01:42:57 PM »
Great! You're welcome!
in the end I used
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))

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 #927 on: May 15, 2017, 01:45:11 PM »
in the end I used
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))
Cool! Why only those 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: 4255
  • Total likes: 812
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #928 on: May 15, 2017, 01:46:14 PM »
Cool! Why only those letters?
only ones that would be used in a number

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 #929 on: May 15, 2017, 01:52:33 PM »
in the end I used
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))
and yet if the range includes anything matching
Code: [Select]
((\A| |-|^|ק|ר|ש|ת))
טו
[א-ת]
(in 1 line)
 (e.g. יהושע) would still mess up/need a manual fix

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 #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: 4255
  • Total likes: 812
  • 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 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 #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: 4255
  • Total likes: 812
  • 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 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 #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: 4255
  • Total likes: 812
  • 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: 4255
  • Total likes: 812
  • 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 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 #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 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 #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 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 #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.