find beginning of line to first space?

[Lance]

Hello all,

I have some cells that look like this

1 One
02 Two
003 Three
4 Four
000005 Five

and I am looking for a way to cut and paste the number into a new
column. I am guessing I should search and select anything from
the beginning of the line to the first space. I could then cut this
value and place it elsewhere.

I am not certain how to perform this kind of search in Excel though?
Could someone give me an example of how to search from the beginning of
a cell to the first space?

TIA,

Lance

 

[joeu2004]

I have some cells that look like this
1 One
02 Two
003 Three
4 Four
000005 Five
and I am looking for a way to cut and paste the number
into a new column.  I am guessing I should search and
select anything from the beginning of the line to the
first space.  I could then cut this value and place it
elsewhere.

You could do that. See below. But perhaps the easiest way is to use
the Text To Column feature. In XL2003, click on Data > Text To
Column, select an appropriate delimiter, click Next to move along, and
in the last menu, select Skip for the second column and select where
you would like the result.

If you want to retain the leading zeros, either mark the column as
Text before clicking Finish, or afterwards choose an appropriate
Custom format, e.g. 000000.

As for a function, you might use the following paradigm:

=LEFT(A1,FIND(" ",A1)-1)

or

=--LEFT(A1,FIND(" ",A1)-1)

The first form produces text, which preserves leading zeros. The
second form produces numeric results, which would require a Custom
format to display leading zeros.

Caveat: The blanks you see might be non-breaking spaces (HTML &nbsp
instead of normal spaces. In that case, replace " " with CHAR(160).

 

[Donald Guillett]

Hello all,

I have some cells that look like this

1 One
02 Two
003 Three
4 Four
000005 Five

and I am looking for a way to cut and paste the number into a new
column.  I am guessing I should search and select anything from
the beginning of the line to the first space.  I could then cut this
value and place it elsewhere.

I am not certain how to perform this kind of search in Excel though?
Could someone give me an example of how to search from the beginning of
a cell to the first space?

TIA,

Lance

Lots of ways:
data>text to columns>>
formula using FIND or SEARCH
macro using INSTR

 

[joeu2004]

Caveat:  The blanks you see might be non-breaking spaces
(HTML  )

Interesting: my broswer (at least) changed the text I wrote after
"HTML". I wrote ampersand nbsp semicolon, the HTML element
representing a non-breaking space.

 

[GS]

Hello all,

I have some cells that look like this

1 One
02 Two
003 Three
4 Four
000005 Five

and I am looking for a way to cut and paste the number into a new column. I
am guessing I should search and select anything from
the beginning of the line to the first space. I could then cut this value
and place it elsewhere.

I am not certain how to perform this kind of search in Excel though?
Could someone give me an example of how to search from the beginning of
a cell to the first space?

TIA,

Lance

Enter the following formula into the cell where you want the
results.

=LEFT(A1,FIND(" ",A1)-1)

Be sure to change the ref to 'A1' to match the 1st source cell, then
copy down (or to) other cells as desired.

 

[Lance]

=LEFT(A1,FIND(" ",A1)-1) works like a charm. Thanks very much.

Lance

Hello all,

I have some cells that look like this

1 One
02 Two
003 Three
4 Four
000005 Five

and I am looking for a way to cut and paste the number into a new
column. I am guessing I should search and select anything from
the beginning of the line to the first space. I could then cut this
value and place it elsewhere.

I am not certain how to perform this kind of search in Excel though?
Could someone give me an example of how to search from the beginning of
a cell to the first space?

TIA,

Lance

Enter the following formula into the cell where you want the results.

=LEFT(A1,FIND(" ",A1)-1)

Be sure to change the ref to 'A1' to match the 1st source cell, then
copy down (or to) other cells as desired.