CODEWITHSUNDEEP
google-sheetsformulaspreadsheet
little_code
little_code

Reputation: 216

How to separate numbers from string and the number should appear separately in separate cells in Google Sheets

I have a data in "A" column like as below starting from A2 cell.

7cz2
6789efg
abc890
34l1
78kk
88
63

What I need is
7
2
6
7
8
9
8
9
0
3
4
7
8
8
8
6
3.

I do have applied =VALUE(REGEXREPLACE(A2,"\D+", "")) but is is giving me data like

72
6789
890
34
78.

Is there a way to solve this.Thanks in advance.

Upvotes: 0

Views: 50

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Use 2 formulas.

  1. join and replace letters in cell C1:

=REGEXREPLACE(JOIN("",A1:A7),"[A-Za-z]","")

  1. split by symbol:

=TRANSPOSE(REGEXEXTRACT(C1,REPT("(.)",LEN(C1))))

The result:

enter image description here

Upvotes: 2

Related Questions