EXTRACTNUMERS(Test 1 Test)
would give the result: 1
That's perfect. But in combination with some letters, you would get false results. This is the case for example in some addresses at least in Germany. There could be Main Street 1 and Main Street 1B.
You even get different results for App Formula in a "real" column and for App Formula in a virtual column.
| Value | Real Column | Virtual Column | Maybe...? |
| 1b | Blank | 1,000,000,000 | billion |
| 1B | Blank | 1,000,000,000 | Billion |
| 1G | 1,000,000,000 | 1,000,000,000 | Giga |
| 1k | 1,000 | 1,000 | kilo |
| 1K | 1,000 | 1,000 | Kilo |
| 1M | 1,000,000 | 1,000,000 | Mega |
| 1T | 1,000,000,000,000 | Blank | Terra |
It seems to be an intended behavior. But since it is not in the Documentation and real column differs from VC, I think it should be corrected.
I tested all other letters. They all give blank results, so they are not affected.
We now use some substitute expressions to workaround this:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
UPPER([Adresse]),
"B", " "),
"G", " "),
"K", " "),
"M", " "),
"T", " ")
UPPER() because then we don't need to also substitute small letters like "b" and "k".
LinkedIn
Twitter
