CODEWITHSUNDEEP
google-sheets
sayth
sayth

Reputation: 7048

Sheets - Split number right to left to maxium of 5 characters

If given a column of numbers and characters such as this.

2
x73374690x

How can I turn that into this using Google Sheets. I tried using numbers as the delimters in split without success

=iferror(SPLIT(C13,"0,1,2,3,4,5,6,7,8,9",FALSE),"NA")

Expected output

NA  NA  NA  NA  2
4   6   9   0   x

Upvotes: 0

Views: 26

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

This formula will work for big string:

=REGEXEXTRACT(RIGHT(A1,5),REPT("(.)?",5))

and smth like this should handle all cases:

=REGEXEXTRACT(REPT("-",5-len(RIGHT(A1,5)))&RIGHT(A1,5),REPT("(.)?",5))

The result:

x73374690x  4   6   9   0   x
2           -   -   -   -   2
222         -   -   2   2   2
222x        -   2   2   2   x

Upvotes: 2

Related Questions