Working in Numbers, I have two tables. In the one table (Table A) I want to type some text. Is there a ways to separate and transpose the data into a different table (Table B)? Spaces and punctuation should be removed / ignored.
Basically looking for the opposite of CONCAT (Table C). Is there a way to do this?
MacBook Pro 13″, macOS 13.5
You can try this:
In A1 of Table B, filled down:
=REGEX.EXTRACT(Table A::$A$1,"\w",ROW())
If you want the result to be uppercase as in your screenshot then you can do this:
=UPPER(REGEX.EXTRACT(Table A::$A$1,"\w",ROW()))
And if you want to hide any out-of-range red warning triangles you can do this:
=IFERROR(UPPER(REGEX.EXTRACT(Table A::$A$1,"\w",ROW())),"")
The \w in the regex means any "word" characters (which don't include spaces and other special characters)
More on REGEX.EXTRACT here:
REGEX.EXTRACT - Apple Support (CA)
SG
You can try this:
In A1 of Table B, filled down:
=REGEX.EXTRACT(Table A::$A$1,"\w",ROW())
If you want the result to be uppercase as in your screenshot then you can do this:
=UPPER(REGEX.EXTRACT(Table A::$A$1,"\w",ROW()))
And if you want to hide any out-of-range red warning triangles you can do this:
=IFERROR(UPPER(REGEX.EXTRACT(Table A::$A$1,"\w",ROW())),"")
The \w in the regex means any "word" characters (which don't include spaces and other special characters)
More on REGEX.EXTRACT here:
REGEX.EXTRACT - Apple Support (CA)
SG
Thanks! That worked. How do you guys know this stuff? I've been scratching around for weeks (actually months, but always end up giving up and then coming back to it again) looking for ways to do this.
So I tested a bit further. I found that when I add a header row, then the count is wrong and I'm losing the first letter... have solved that by adding a value into the ROW function:
=IFERROR(REGEX.EXTRACT(UPPER(Table A::$A$1),"TH|CH|SH|\w",ROW(A1)),"")
It took me a while to figure this one out and also to realise that I had to deselect preserve row, but this function works now.
The only other glitch that I can find is when there is a space is between a "t" and an "h"... but just as I'm typing this I got the idea to also add "T H|" as a pair and this works! 😃
=IFERROR(REGEX.EXTRACT(UPPER(Table A::$A$1),"TH|CH|SH|T H| C H| S |\w",ROW(A1)),"")
Looks like you are getting the hang of it.
Instead of adding the new sets of characters with the spaces, you can remove the spaces from the source string, In the formula below I used substitute to replace them with "". This way your results will not have a space in them.
=IFERROR(REGEX.EXTRACT(SUBSTITUTE(UPPER(Table A::$A$1)," ",""),"TH|CH|SH|\w",ROW(A1)),"")
If you use the formula from your last post, I am wondering about the last two sets of characters you added. There is a leading space in " C H" and I was expecting the last one to be "S H" instead of " S ".
=IFERROR(REGEX.EXTRACT(UPPER(Table A::$A$1),"TH|\w",ROW()),"")
"TH|\w" means to find matches for "TH" or any word character. The | is "or". You can include additional sets/pairs of characters in the expression. Put them before the \w.
YES! Wow! Thank you! That's amazing. I've been trying for weeks to do this.
One more question on this. Is there a way to pair certain letter combinations together?
e.g. pairing TH together instead of having T in one cell and H in the next cell?
So I can create a second column that checks the previous column:
IF(CONCAT(A1:A2)="TH" "TH", A1)
But if I have other letter combinations I want to add, like "AH", "WH", "SH", then I get stuck, because I can't see how I can do an else statement. Or is there a way to make an array with the options?
Also when I get the TH in the cell, then H comes up again in the next cell.
I guess if =IFERROR(UPPER(REGEX.EXTRACT(Table A::$A$1,"\w",ROW())),"") can be adapted to check the letter combinations beforehand then only one column would be needed instead of two.
Trapsosing contents of one cell into multiple cells
