Extract text and numbers from Cell

Is it fair to say that you want the numbers up to the first space?

Try
=LEFT(A1,FIND(" ",A1))+0

Got it, worked great. I actually thought of that answer later and came up with a formula like

=LEFT(B42,SEARCH(" ",B42,1)) but will use yours as it transforms to numbers from text.

Thanks!!

Thanks

Jonmo1 said:

Is it fair to say that you want the numbers up to the first space?

Try
=LEFT(A1,FIND(" ",A1))+0

Click to expand...

You're welcome...

Yours would be fine as well, just add the +0 at the end.


FYI, the only difference I'm aware of between Find and Search is that FIND is case sensitive.
But that's irrelevant here since the text you're looking for is a space.

Jonmo1 said:

FYI, the only difference I'm aware of between Find and Search is that FIND is case sensitive.

Click to expand...

It's also quite interesting that, with A1 empty (or containing the null string ""):

=FIND("",A1)

returns 1, whereas:

=SEARCH("",A1)

returns #VALUE!

Regards

That is interesting...and wierd because if anything, you'd think it would be the other way around since FIND is the more sensitive one...

Jonmo1 said:

FYI, the only difference I'm aware of between Find and Search is that FIND is case sensitive.

Click to expand...

SEARCH also permits wildcards and FIND does not.

Rick Rothstein said:

SEARCH also permits wildcards and FIND does not.

Click to expand...

Good to know, thanks.

And what is that?
(Bulls***t)

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='4' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='33pt'><col width='28,5pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' >8 8</td><td align='right' >2</td><td align='right' >2</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >88 8</td><td align='right' >3</td><td align='right' >3</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' >888 88</td><td align='right' >4</td><td align='right' >4</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' >88 888</td><td align='right' >3</td><td align='right' >3</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=FIND</Span><Span style='color:#0000DD'>(" ",A1)</Span><Span style='color:#222222'></Span></td></tr><tr><td>C1</td><td><Span style='color:#222222'>=SEARCH</Span><Span style='color:#0000DD'>(" ",A1)</Span><Span style='color:#222222'></Span></td></tr></table>

Matt Rogers said:

And what is that?
(Bulls***t)

	A	B	C
1	8 8	2	2
2	88 8	3	3
3	888 88	4	4
4	88 888	3	3

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="33pt"><col width="28,5pt"></colgroup><tbody>
</tbody>

Cell	Formula
B1	=FIND(" ",A1)
C1	=SEARCH(" ",A1)

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Click to expand...

Sorry - not sure I understand?

Regards