CODEWITHSUNDEEP
google-sheets
user13698713
user13698713

Reputation:

Splitting text in Google Sheets converts numbers to dates, how could I prevent this?

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.sstatic.net/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.

Upvotes: 0

Views: 143

Answers (2)

player0
player0

Reputation: 1

try:

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

enter image description here

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

Upvotes: 0

Ping
Ping

Reputation: 911

image

Like this?

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

Extracted as requested:

image

=REGEXEXTRACT(A1,"^(\d+)\s(.+)\s(.+)\s(.+)")

Upvotes: 1

Related Questions