0

I have an address like this: 1111 Budapest Test Street 0339/1

I use regex with search and replace so it adds | characters, which I can use to split the address to different sections.

The problem is, when I split it, the 0339/1 turns into "-570142", others turn into numbers like "41675", but I have no idea why. Also, 1/1. in 1111|Budapest| Example |District|1/1.| becomes 44562 (which is the date 2022.01.01 if you change the format to it).

I've tried using every formatting on every single cell, but that didn't help.

I also tried come solutions I found here, like using the T formula and such, but those changed nothing.

Any idea would be appreciated, I'm kind of flatterned on this problem rn.

edit #1: Expected output: https://i.stack.imgur.com/oOagv.jpg

edit #2:

So I do it like this rn:

  1. use REGEX to match different parts of the addresses, so this: 1111 Budapest Test Street 0339/1 becomes this: 1111|Budapest|Test|Street|0339/1.

  2. Then I split it by the | character, so each 'values' are in different cells.

ADHDisDEV
  • 414
  • 4
  • 14

2 Answers2

1

image

Like this?

=REGEXEXTRACT(A1,"(^[0-9]+)\s(.+)\s([0-9]+.+)")

Extracted as requested:

image

=REGEXEXTRACT(A1,"^(\d+)\s(.+)\s(.+)\s(.+)")
Ping
  • 891
  • 1
  • 2
  • 10
  • It would be fine, but I need each "values" to different cells. So as I pasted the example, it should be like "postal code | city | street/district/etc | number | other things (like floor, door, etc). So it's a bit more complicated than that I assume, but the main goal is to keep the original format of *everything* after splitting. – ADHDisDEV Dec 06 '22 at 08:17
  • 2
    The only problem is what do you use as a seprator in REGEX, REGEX always return values as string, which should be able to keep the format as it was, it is overcomplited to do regexmatch than replace and split. For you example, simplely do ```=REGEXEXTRACT(A1,"^(\d+)\s(.+)\s(.+)\s(.+)")``` – Ping Dec 06 '22 at 08:58
1

try:

=SPLIT(SUBSTITUTE(A70&" ", " ", " ​"), " ")

enter image description here

see: https://stackoverflow.com/a/73967098/5632629

player0
  • 124,011
  • 12
  • 67
  • 124