CODEWITHSUNDEEP
regexparsinggoogle-sheets
ghozx
ghozx

Reputation: 25

parse text to cell row column google apps

I have the following lines of text:

Fred    A135B246C3
Barney  A2B15C126
Wilma   A4C4
Betty   A6B3C5

Basically what this says is the value "Fred" goes in Cells A1, A3, A5, B2, B4, B6 and C3. While Barney goes into cells A2, B1, B5, C1, C2 and C6. The same process goes for Wilma and Betty.

So I've tried the following functions with limited success: regex(), find(), query() etc. For example, =REGEXEXTRACT(F1,"A[0-9]*") yields A135 for Fred. In the next cell I put =REGEXEXTRACT(F1,"B[0-9]*"), move over a cell and continue.

This then yields:

screenshot of results.

I can then do another REGEXEXTRACT and get:

screenshot of extended results

I then do some filtering and finally end up with what you see in image 1 columns A-C and rows 1 to 6.

This seems like a lot of work. Can someone suggest another method?

Upvotes: 1

Views: 71

Answers (2)

Aurielle Perlmann
Aurielle Perlmann

Reputation: 5509

This is the simplest way to achieve it

=split(REGEXreplace(T7279,"(\w\d+)",",$1"),",")

Upvotes: 1

daniel
daniel

Reputation: 648

For the first part, I'd suggest something like

=split(REGEXREPLACE(A1,"([A-Z])",";$1"),";")

which would convert "A135B246C3" into "A135", "B246", and "C3"

You can't combine 2nd part with 1st part without creating a convoluted, difficult-to-debug formula.

Another option would be creating a custom function (using Google Apps Script) which would convert "A135B246C3" into "A1,A3,A5,B2,B4,B6,C3", but custom function isn't in your original question.

Upvotes: 1

Related Questions