I have a 600 row worksheet were I need to remove the text to the left of the
first space in a cell, all are different lengths. Is there a formula for
that?
For example:
September 8464-005 pin
July 14386 brg
May 5028 washer

In the above I would need to remove the months from each cell. The only
commonality would be the space after the month. End result would be:
8464-005 pin
14386 brg
5028 washer

Any help is greatly appreciated!

Lee

Assuming the month is the first word.

Sub findblank()
For Each c In range("a2:a22") 'Selection
MsgBox Right(c, Len(c) - instr(c," "))
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gemi" wrote in message
...
I have a 600 row worksheet were I need to remove the text to the left of
the
first space in a cell, all are different lengths. Is there a formula for
that?
For example:
September 8464-005 pin
July 14386 brg
May 5028 washer

In the above I would need to remove the months from each cell. The only
commonality would be the space after the month. End result would be:
8464-005 pin
14386 brg
5028 washer

Any help is greatly appreciated!

Lee

hi
you could use a fomula in a helper column.
assuming the date in question is in column a.....

=MID(A2,LEN(LEFT(A2,FIND(" ",A2,1)))+1,999)

copy down as far as needed you could then copy the helper column and paste
special values over the old data.

regards
FSt1

"Gemi" wrote:

I have a 600 row worksheet were I need to remove the text to the left of the
first space in a cell, all are different lengths. Is there a formula for
that?
For example:
September 8464-005 pin
July 14386 brg
May 5028 washer

In the above I would need to remove the months from each cell. The only
commonality would be the space after the month. End result would be:
8464-005 pin
14386 brg
5028 washer

Any help is greatly appreciated!

Lee

A little shorter....

=MID(A1,FIND(" ",A1&" ")+1,99)

where the 99 just has to be a number that is larger than the maximum length
of any of the text in the cell (99 was my guess at that number).

--
Rick (MVP - Excel)


"FSt1" wrote in message
...
hi
you could use a fomula in a helper column.
assuming the date in question is in column a.....

=MID(A2,LEN(LEFT(A2,FIND(" ",A2,1)))+1,999)

copy down as far as needed you could then copy the helper column and
paste
special values over the old data.

regards
FSt1

"Gemi" wrote:

I have a 600 row worksheet were I need to remove the text to the left of
the
first space in a cell, all are different lengths. Is there a formula for
that?
For example:
September 8464-005 pin
July 14386 brg
May 5028 washer

In the above I would need to remove the months from each cell. The only
commonality would be the space after the month. End result would be:
8464-005 pin
14386 brg
5028 washer

Any help is greatly appreciated!

Lee

thanks for the tip
regards
FSt1

"Rick Rothstein" wrote:

A little shorter....

=MID(A1,FIND(" ",A1&" ")+1,99)

where the 99 just has to be a number that is larger than the maximum length
of any of the text in the cell (99 was my guess at that number).

--
Rick (MVP - Excel)


"FSt1" wrote in message
...
hi
you could use a fomula in a helper column.
assuming the date in question is in column a.....

=MID(A2,LEN(LEFT(A2,FIND(" ",A2,1)))+1,999)

copy down as far as needed you could then copy the helper column and
paste
special values over the old data.

regards
FSt1

"Gemi" wrote:

I have a 600 row worksheet were I need to remove the text to the left of
the
first space in a cell, all are different lengths. Is there a formula for
that?
For example:
September 8464-005 pin
July 14386 brg
May 5028 washer

In the above I would need to remove the months from each cell. The only
commonality would be the space after the month. End result would be:
8464-005 pin
14386 brg
5028 washer

Any help is greatly appreciated!

Lee