TRIM or LEFT Function - Extract number until first non-numerical character

[horizonflame]

Hi All

I have some raw data in one column. The format of the cells is a number followed by either a space or text. I am looking to extract only the first number with a formula.

Thank for the help!

 

[Eric W]

Try:

=MAX(IFERROR(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0,""))

with Contol+Shift+Enter.

 

[jtakw]

Hi,

Another option, entered normally:

Book1
	A	B
1	1 234	1
2	1 text	1
3	1234 3456	1234
4	1234text	1234
5	text1234
6	text 1234
Sheet373

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(9.99999999999999E+307,LEFT(A1,ROW($1:$99))+0),"")

Formula copied down.

 

[XOR LX]

Both of those will fail for e.g. 12MARKET or 3JANE.

=-LOOKUP(1,-(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))&"**0"))

Regards

 

[jtakw]

Original and Shorter version of my formula using your "catch" for possible Date Values:

Book1
	A	B	C
1	1 234	1	1
2	1 text	1	1
3	1234 3456	1234	1234
4	1234text	1234	1234
5	text1234
6	text 1234
7	12MARKET	12	12
8	3JANE	3	3
Sheet373

Cell Formulas
Range	Formula
B1	=IFERROR(-LOOKUP(9.99999999999999E+307,-(LEFT(A1,ROW($1:$99))&"**0")),"")
C1	=IFERROR(-LOOKUP(1,-(LEFT(A1,ROW($1:$99))&"**0")),"")

 

[horizonflame]

Hi jtakw,

Thanks very much. My source is a different spreadsheet so would I adapt the formula like this?

=IFERROR(-LOOKUP(9.99999999999999E+307,-(LEFT('[Spreadsheet2.xlsm]Sheet1'!$H6',ROW('[Spreadsheet2.xlsm]Sheet1'!$H6:$H$106))&"**0")),"")

Thanks

 

[Aladin Akyurek]

No need for a negation algebra...

=IFERROR(LOOKUP(9.99999999999999E+307,(LEFT('[Spreadsheet2.xlsm]Sheet1'!$H6,ROW($1:$99))&"**0")+0),"")

 

[horizonflame]

Thanks Aladin, do I need to modify the ($1:$99) with my range?

 

[Aladin Akyurek]

Thanks Aladin, do I need to modify the ($1:$99) with my range?

You are welcome. You don't need to modify that bit, as it might be expected to be sufficient, i.e. the length of the strings we are looking at won't exceed 99. Otherwise, we might need to invoke ROW(INDIRECT("1:"&LEN('[Spreadsheet2.xlsm]Sheet1'!$H6))) instead, not very appetizing prospect. (Note. I still hope Microsoft will come up with an appropriate function (ivec or intvector [as intimated long ago by Laurent Longré]).

 

[horizonflame]

Brilliant thanks, there won't be anywhere near 99 characters so all good cheers!