Excel Help and Problems

[gozalim]

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

[etech0]

it works fine without the nesting

Can you post your formula that works fine, so I can compare them?

[gozalim]

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

[etech0]

Can you tell me what the parameters are for the function? It should tell you when you type =xReplace(

[gozalim]

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.

[etech0]

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

[gozalim]

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?

[etech0]

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יו")

[gozalim]

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יה")

[etech0]

looks like it's working! thanks!
(minor correction to code)

Code: [Select]

=xREPLACE("((\A| |-|^|ק|ר|ש|ת))טו",xREPLACE("((\A| |-|^|ק|ר|ש|ת))טז",[@[כרך (א)]],"$1יו"),"$1יה")

Great!!

[gozalim]

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

[etech0]

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")

[etech0]

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

[gozalim]

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",[@[כרך (א)]])

[etech0]

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.

[gozalim]

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.

it's a table so it does go to the whole column. I click the cell it 'takes' that [column title]

[etech0]

it's a table so it does go to the whole column. I click the cell it 'takes' that [column title]

Oh cool! I haven't dealt with tables much. So is @[כרך (א)] the name of your column?

[gozalim]

Oh cool! I haven't dealt with tables much. So is @[כרך (א)] the name of your column?

yes

[etech0]

yes

Nice!

[etech0]

10 Easy Excel Timesavers You Might Have Forgotten