I have multiple rows of string where the string is all wrong. Here is one row an an example of the geometry and output expected:

One example is as follows above. I need to get rid of all odd position commas and only keep the even position commas. So that the geometry can become output.

I tried doing a split(text.",") and concatenate however when the columns is blank it returns xxx,,,, which is not what I had in mind. Since some have more than 200 commas means that I need to have more than 200 columns, is there a simpler way like using regex?Someone please help.

CodePudding user response：

try:

=INDEX(REGEXREPLACE(QUERY(FLATTEN(SPLIT(A1, ",")&IF(ISODD(
 SEQUENCE(1, COLUMNS(SPLIT(A1, ",")))),, ",")),,9^9), ",$", ))

for array:

=INDEX(IFERROR(BYROW(A1:A3, LAMBDA(x, REGEXREPLACE(QUERY(FLATTEN(SPLIT(x, ",")&
 IF(ISODD(SEQUENCE(1, COLUMNS(SPLIT(x, ",")))),, ",")),,9^9), ",$", )))))

CodePudding user response：

If the second number is always negative, this is simple as replacing , - (comma, space, dash) with (space).

=REGEXREPLACE(B2,", -"," ")

If not,

=REGEXREPLACE(B2,"(-??\d \.?\d*),(\s*-?\d \.?\d*)","$1$2")

• Capture group #1: (-??\d \.?\d*)

  • -?? zero or one of literal dash followed by
  • \d one or more digits followed by
  • .? zero or one of literal .
  • \d* zero or more of digits

• literal ,

• Capture group #2 (\s*-?\d \.?\d*)

  • \s* zero or more of space characters
  • -? zero or one of literal dash followed by
  • \d one or more digits followed by
  • .? zero or one of literal .
  • \d* zero or more of digits

Replace with capture groups only: $1$2