Separate number from text

Question

I am using excel 2010 and I am having the following text in one cell:

Hash Rates
44Th/s
485Mh/s
432Mh/s
7Th/s
365Mh/s
33Th/s
3.1Th/s
6Th/s
10Gh/s
240kh/s
24.5Th/s
160kh/s

I would like to separate the number from the text. For example for 44Th/s, I would like to get 44 and Th/s.

I tried creating a helper cell go get the position of the number and text with the following formula:

=MIN(FIND({0;1;2;3;4;5;6;7;8;9};E2&"0123456789"))

As a result I am now trying to separate the text from the number with:

=RIGHT(E2;LEN(E2)-L2+1)

However, I only get the initial text back:

Hash Rates
44Th/s
485Mh/s
432Mh/s

Any suggestions how to separate text from number?

I appreciate your replies!

https://stackoverflow.com/questions/43634174/formula-to-extract-numbers-from-a-text-string — Frank Ball, Sep 21 '18 at 13:59
@FrankBall none of those suggestions would work - all are Google Spreadsheet-related, except for the `TEXTJOIN` solution, which wouldn't work in Excel 2010 — BigBen, Sep 21 '18 at 14:01
I recommend power query in excel for this here is a example. https://stackoverflow.com/questions/72210897/excel-how-to-split-cells-by-comma-delimiter-into-new-cells — Shane S, Jan 06 '23 at 07:28

score 2 · Accepted Answer · answered Sep 21 '18 at 14:15

2

Try this to get the numbers:

=LEFT(A2,AGGREGATE(14,6,ROW($A$1:INDEX(A:A,LEN(A2)))/(ISNUMBER(--MID(A2,ROW($A$1:INDEX(A:A,LEN(A2))),1))),1))

Then this to get the text:

=SUBSTITUTE(A2,B2,"")

answered Sep 21 '18 at 14:15

Scott Craner

newacc2240 · Answer 2 · 2018-09-21T14:15:44.877

1

=MIN(FIND({0;1;2;3;4;5;6;7;8;9};E2&"0123456789")) returns 1, because that all your data starts with a number.

Try this instead:

=SUMPRODUCT(--(MID(E2;ROW(INDIRECT("1:"&LEN(E2)));1)={"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"."}))

This can be combined with your formula, so that goes to:

Number Part:

=--LEFT(E2;SUMPRODUCT(--(MID(E2;ROW(INDIRECT("1:"&LEN(E2)));1)={"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"."})))

Text Part

=RIGHT(E2;LEN(E2)-SUMPRODUCT(--(MID(E2;ROW(INDIRECT("1:"&LEN(E2)));1)={"0";"1";"2";"3";"4";"5";"6";"7";"8";"9";"."})))

edited Sep 21 '18 at 14:15

answered Sep 21 '18 at 14:10

newacc2240

I don't thing your sumproduct is working. as far as I understand the arrays, length of number and the numbers must be the same length. I get #REF! errors. – Scott Craner Sep 21 '18 at 14:53
Maybe it's due to the difference of `;` and `,` – newacc2240 Sep 21 '18 at 15:23
Yes when making the array horizontal with `,` it works but vertical with `;` does not work. – Scott Craner Sep 21 '18 at 15:26
I just changed all `,` into `;` in my formula to fit in OP's language setting. Not sure about how the array in OP's computer looks. – newacc2240 Sep 21 '18 at 15:30
1

I too am not sure what the delimiter is for a horizontal array when `;` is the standard delimiter for the formula. – Scott Craner Sep 21 '18 at 15:34

2 Answers2