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

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #960 on: May 21, 2017, 10:53:18 PM »
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
it works fine without the nesting

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #961 on: May 22, 2017, 12:10:03 AM »
it works fine without the nesting
Can you post your formula that works fine, so I can compare them?
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #962 on: May 22, 2017, 12:22:37 PM »
Current setup is as follows:
2 new (hidden) columns. 1st has
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו")2nd references 1st with
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@HebNum1],"$1יו")
It feels like he's not using excel's built-in REPLACE function, rather 're-wrote' his own in a macro or script or something, so the nesting that excel built into REPLACE doesn't necessarily apply here. though i'm not actually sure

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #963 on: May 22, 2017, 12:32:27 PM »
Can you tell me what the parameters are for the function? It should tell you when you type =xReplace(
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #964 on: May 22, 2017, 12:39:19 PM »
Can you tell me what the parameters are for the function? It should tell you when you type =xReplace(

doesn't offer. also not 'highlighted' in cell.
the popup offers:
xREPLACE(pattern,searchText,replacementText,ignoreCase)
Replace all portions of the search text matching the pattern with the
replacement text.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #965 on: May 22, 2017, 12:52:19 PM »
doesn't offer. also not 'highlighted' in cell.
the popup offers:
xREPLACE(pattern,searchText,replacementText,ignoreCase)
Replace all portions of the search text matching the pattern with the
replacement text.
okay, so you need to put one formula in as the "search text" - 2nd parameter - in the other. It looks like you were using the 1st parameter
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #966 on: May 22, 2017, 01:04:08 PM »
okay, so you need to put one formula in as the "search text" - 2nd parameter - in the other. It looks like you were using the 1st parameter
so 2nd parameter should have entire 2nd code
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו")(with updated location) -I assume enclosed in a parentheses? plus the 'address' for the 1st exchange?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #967 on: May 22, 2017, 01:12:08 PM »
so 2nd parameter should have entire 2nd code
yes, excluding the = sign
(with updated location) -I assume enclosed in a parentheses?
no need
(with updated location) -I plus the 'address' for the 1st exchange?
you shouldn't need that either, since you're putting it all into one cell.

Here is my attempt (bear in mind that i didn't test it)
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו"),"$1יו")
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #968 on: May 22, 2017, 01:18:18 PM »
yes, excluding the = signno needyou shouldn't need that either, since you're putting it all into one cell.

Here is my attempt (bear in mind that i didn't test it)
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו"),"$1יו")
looks like it's working! thanks!
(minor correction to code)
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טו",xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו"),"$1יה")

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #969 on: May 22, 2017, 01:36:11 PM »
looks like it's working! thanks!
(minor correction to code)
Code: [Select]
=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טו",xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו"),"$1יה")
Great!!
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #970 on: May 22, 2017, 01:41:21 PM »
btw, once I've put it in- I'm looking for a formula that will return the 1st 4-digit number from a text body. the stuff google's turning up is getting complicated. it seems like regex simplifies somewhat, but i'm not getting it to work

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #971 on: May 22, 2017, 01:47:33 PM »
btw, once I've put it in- I'm looking for a formula that will return the 1st 4-digit number from a text body. the stuff google's turning up is getting complicated. it seems like regex simplifies somewhat, but i'm not getting it to work
Sure! Here it is for google sheets, you should be able to translate it to the excel version

=regexextract(A1,"\d\d\d\d")
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: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #972 on: May 22, 2017, 01:48:32 PM »
BTW if you're looking for help / to learn regex, these are 2 of my favorite resources:

https://regexone.com/ - a mini-course that walks you through learning regex
http://rubular.com/ - a great reference, and great for testing your regular expressions
Workflowy. You won't know what you're missing until you try it.

Offline gozalim

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2008
  • Posts: 3141
  • Total likes: 57
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #973 on: May 22, 2017, 02:02:57 PM »
Sure! Here it is for google sheets, you should be able to translate it to the excel version

=regexextract(A1,"\d\d\d\d")
thanks
Code: [Select]
=xMATCH("\d\d\d\d",[@[כרך (א)]])

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9556
  • Total likes: 376
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #974 on: May 22, 2017, 02:37:39 PM »
thanks
Code: [Select]
=xMATCH("\d\d\d\d",[@[כרך (א)]])
Cool! BTW, for your second argument/parameter, you can just refer to the cell containing the source text. Then you could copy the formula for an entire column.
Workflowy. You won't know what you're missing until you try it.