remove number from a column in excel-CodePudding

I am having a column as below in excel. Consider it as a column of elements.

excel_data:

I need to remove from number in excel.

Desired output:-

Is there any formula for doing it?

CodePudding user response：

Try:

Formula in B1:

=@TEXTSPLIT(A1,SEQUENCE(10,,0))

Or, in a single go:

Formula in B1:

=TEXTSPLIT(A1:A4,SEQUENCE(10,,0))

Thanks to @Ike.

CodePudding user response：

The following will spill the results:

=BYROW(A1:A4,LAMBDA(b,LEFT(b,MIN(FIND({1,2,3,4,5,6,7,8,9,0},b&"1234567890")-1))))

CodePudding user response：

You may try this as well,

• Formula used in cell B1,

=LET(x,MIN(IFERROR(SEARCH(ROW($1:$10)-1,A1),"")),
IF(x<>0,REPLACE(A1,x,255,""),
A1))

Edit: Using TEXTBEFORE()

=TEXTBEFORE(A1,SEQUENCE(10,,0),,,1,A1)

Or,

=TEXTBEFORE(A1:A4,SEQUENCE(10,,0),,,1,A1:A4)