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

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9745
  • Total likes: 427
  • 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: 3152
  • Total likes: 57
  • 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: 3152
  • Total likes: 57
  • 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