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

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 #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 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 #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: 4255
  • Total likes: 812
  • 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 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 #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: 4255
  • Total likes: 812
  • 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 ירמיה?

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 #945 on: May 15, 2017, 04:53:55 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 ירמיה?
I think you want to close the first set of parens before 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 #946 on: May 15, 2017, 04:55:54 PM »
I think you want to close the first set of parens before the טו.
I did. it's not showing up correctly here due to language switch.
copy and paste it and you'll see

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 #947 on: May 15, 2017, 04:57:47 PM »
I did. it's not showing up correctly here due to language switch.
copy and paste it and you'll see
Are you using regexreplace? Can you post your full formula so I can copy it?
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 #948 on: May 15, 2017, 05:02:22 PM »
Are you using regexreplace? Can you post your full formula so I can copy it?
i'm using gdocs with regex selected.
tried with each of those examples

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 #949 on: May 15, 2017, 05:06:34 PM »
i'm using gdocs with regex selected.
tried with each of those examples
What do you mean by "regex selected"?
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 #950 on: May 15, 2017, 05:07:06 PM »
What do you mean by "regex selected"?
it's a checkbox on the expanded search box

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 #951 on: May 15, 2017, 05:09:46 PM »
it's a checkbox on the expanded search box
Oh, I didn't even know that :) I was referring to the REGEXREPLACE formula where you tell it a cell, the regex to look for in there, and the string to replace it with.
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 #952 on: May 21, 2017, 08:12:19 PM »
Oh, I didn't even know that :) I was referring to the REGEXREPLACE formula where you tell it a cell, the regex to look for in there, and the string to replace it with.
would this serve the same purpose as Gdocs?
http://blog.malcolmp.com/2010/regular-expressions-excel-add-in

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 #953 on: May 21, 2017, 08:19:07 PM »
would this serve the same purpose as Gdocs?
http://blog.malcolmp.com/2010/regular-expressions-excel-add-in
I haven't tried it, but it looks like it might. Let me know if it works!
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 #954 on: May 21, 2017, 08:59:55 PM »
I haven't tried it, but it looks like it might. Let me know if it works!
looks like it's doing what it was intended.
should I be able to nest both replacements (טו\יה and טז\יו) into a single xREPLACE?

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 #955 on: May 21, 2017, 09:05:25 PM »
looks like it's doing what it was intended.
should I be able to nest both replacements (טו\יה and טז\יו) into a single xREPLACE?
I don't think so, but you can nest one formula inside the other
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 #956 on: May 21, 2017, 09:19:39 PM »
I don't think so, but you can nest one formula inside the other
but that would give me the whole cell doubled

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 #957 on: May 21, 2017, 09:38:17 PM »
but that would give me the whole cell doubled
Not if you put one inside the other. IE: build one xReplace formula, and use it's entirety inside as the "text" inside the other.
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 #958 on: May 21, 2017, 10:48:01 PM »
Not if you put one inside the other. IE: build one xReplace formula, and use it's entirety inside as the "text" inside the other.
Code: [Select]
=xREPLACE(xREPLACE("((\A| |-|^))טו",A1,"יה")"((\A| |-|^))טז",A1,"יו")giving me an error msg

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 #959 on: May 21, 2017, 10:51:42 PM »
Code: [Select]
=xREPLACE(xREPLACE("((\A| |-|^))טו",A1,"יה")"((\A| |-|^))טז",A1,"יו")giving me an error msg
It's hard to debug because I don't have the plugin installed, but at first glance it looks like you're missing the quotation marks around the [@[כרך (א)]] parts
Workflowy. You won't know what you're missing until you try it.