0

I am cleaning up some data received from a client. The data structure is something like -

AdminUnit1Code<space>AdminUnit2Code<space>AdminUnit3Code<space>AdminUnitName<space>Data1<space>Data2<space>Data3

An example -

10 BIRAMPUR 44125 16425 27700
10 001 WARD NO. 01 902 434 468
10 001 172 DAKKHIN CHARKAI 502 203 299
10 001 183 DEBIPUR 400 231 169
10 002 WARD NO. 02 899 557 342

I need to replace the <spaces> in <tab> so I can import them into Excel or other data analysis tools properly. I have tried a few search and replace methods in Sublime Text (replacing the spaces to tbs using regular expressions). The challenge is the name of the Admin units contain <spaces> in them. In the example, "BIRAMPUR", "WARD NO. 01", and "DAKKHIN CHARKAI" all are admin unit names.

Is there any way replace spaces tabs selectively? Like replace the spaces after a number/digit to space only but keep all other spaces (after or before a text string) intact?

I can use other tools like Notepad++ if you have a solution there.

Thank you.

I tried the following regular expression.

Find what: (?:^.+?\K | (?=[0-9]+.+)) Replace: \t

Justinas
  • 41,402
  • 5
  • 66
  • 96
Ikhtiar Sobhan
  • 1
  • 1
  • _Define this_: how do you know (with algorithm and not _I know_) it's `WARD NO. 01` and not `NO. 01 902`? Output seems to be CSV, but there is no quotes around values, so it's impossible correctly tell what is what, unless it's always at the same position. – Justinas Aug 16 '23 at 05:35
  • 1. I know the names of the Administrative Units. It is "Ward No. 01", "Ward No. 02", "Ward No. 03" etc. 2. It is not in CSV, unfortunately. – Ikhtiar Sobhan Aug 16 '23 at 06:08
  • So do `content.replace("Ward No. 01", "Ward_No._01").replace("/\s/", "\t").replace("Ward_No._01", "Ward No. 01")` – Justinas Aug 16 '23 at 06:15
  • Thanks, this will help. However, as you can see, there are other Admin Unit names with multiple strings separated by spaces. What I really need is a Regex formula that replaces spaces between two number, but leave the spaces between text strings intact. – Ikhtiar Sobhan Aug 16 '23 at 06:49

0 Answers0