Hi all,

I have a column of cells containing text strings, and I want to omit the
first word from each cell, which are of varying character lengths. I can do
it the long winded way using a combination of the text functions (i.e. left,
mid etc..), but is there a quicker way to omit the first word? The character
lengths of the first word vary from 4 to about 9 characters. I have over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

Hi
Presumably there is a space between the words. If so, then in another column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than 255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:
Hi all,

I have a column of cells containing text strings, and I want to omit the
first word from each cell, which are of varying character lengths. I can do
it the long winded way using a combination of the text functions (i.e. left,
mid etc..), but is there a quicker way to omit the first word? The character
lengths of the first word vary from 4 to about 9 characters. I have over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

Thanks Roger,

Your formula didn't give the first word but worked from right to left, but
it put me on the right track, i used

=LEFT(C2,FIND(" ",C2))

Thanks again,

Bhupinder.
"Roger Govier" wrote:

Hi
Presumably there is a space between the words. If so, then in another column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than 255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:
Hi all,

I have a column of cells containing text strings, and I want to omit the
first word from each cell, which are of varying character lengths. I can do
it the long winded way using a combination of the text functions (i.e. left,
mid etc..), but is there a quicker way to omit the first word? The character
lengths of the first word vary from 4 to about 9 characters. I have over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

"Bhupinder Rayat" wrote in
message ...
Hi all,

I have a column of cells containing text strings, and I want to omit the
first word from each cell, which are of varying character lengths. I can
do
it the long winded way using a combination of the text functions (i.e.
left,
mid etc..), but is there a quicker way to omit the first word? The
character
lengths of the first word vary from 4 to about 9 characters. I have over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

As far as more than 10000 cells is concerned, I think it would
be quicker and easier to do the job with VBA code.
It it is ok for you, please tell me if you want the result in a new
column or if you want the original column modified.

Ciao
Bruno

You asked to omit the first word (ie miss it out) which Roger's solution
did. Your solution extracts the first word with the following space. Try
this to only extract the word.

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

--
Ian
--
"Bhupinder Rayat" wrote in
message ...
Thanks Roger,

Your formula didn't give the first word but worked from right to left, but
it put me on the right track, i used

=LEFT(C2,FIND(" ",C2))

Thanks again,

Bhupinder.
"Roger Govier" wrote:

Hi
Presumably there is a space between the words. If so, then in another
column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than 255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:
Hi all,

I have a column of cells containing text strings, and I want to omit
the
first word from each cell, which are of varying character lengths. I
can do
it the long winded way using a combination of the text functions (i.e.
left,
mid etc..), but is there a quicker way to omit the first word? The
character
lengths of the first word vary from 4 to about 9 characters. I have
over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

Ah!, if only you had said Extract instead of Omit<bg
Glad you worked it out, and thanks for the feedback.

Incidentally, your formula should be
=LEFT(C2,FIND(" ",C2)-1)
As it stands, it will pick up the trailing space after each word which could
give you a problem later if you were trying to do a Lookup or some other task.

If you have already carried out the task, then
=TRIM(A1) or whatever cell you had the formula in, and copied down would
remove any extraneous spaces.


Regards

Roger Govier


Bhupinder Rayat wrote:
Thanks Roger,

Your formula didn't give the first word but worked from right to left, but
it put me on the right track, i used

=LEFT(C2,FIND(" ",C2))

Thanks again,

Bhupinder.
"Roger Govier" wrote:


Hi
Presumably there is a space between the words. If so, then in another column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than 255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:

Hi all,

I have a column of cells containing text strings, and I want to omit the
first word from each cell, which are of varying character lengths. I can do
it the long winded way using a combination of the text functions (i.e. left,
mid etc..), but is there a quicker way to omit the first word? The character
lengths of the first word vary from 4 to about 9 characters. I have over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat

"Bruno Campanini" wrote in message
...

As far as more than 10000 cells is concerned, I think it would
be quicker and easier to do the job with VBA code.
It it is ok for you, please tell me if you want the result in a new
column or if you want the original column modified.

Ciao
Bruno

The following code does the job writing a new column:

================================
Sub FirstWordOnly()
Dim StartRange As Range, TargetRange As Range
Dim i, j As Long

' Definitions
' ------------------------------
Set StartRange = [Sheet2!A292]
Set TargetRange = [Sheet2!B292]
' ------------------------------

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
On Error GoTo ErrHandler

For Each i In Range(StartRange, StartRange.End(xlDown))
j = j + 1
TargetRange.Offset(j - 1, 0) = Left(i, InStr(1, i, " ") - 1)
Next

Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Procedu FirstWordOnly" & vbCrLf & _
ThisWorkbook.FullName
Resume Exit_Sub

End Sub
==============================

Ciao
Bruno

or =RIGHT(A1,LEN(A1)-FIND(" ",A1)). It doesn't care how many characters
there are in A1.
Roger Govier Wrote:
Hi
Presumably there is a space between the words. If so, then in another
column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than
255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:
Hi all,

I have a column of cells containing text strings, and I want to omit
the
first word from each cell, which are of varying character lengths. I
can do
it the long winded way using a combination of the text functions (i.e.
left,
mid etc..), but is there a quicker way to omit the first word? The
character
lengths of the first word vary from 4 to about 9 characters. I have
over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat


--
Bob Miller

Very true Bob. A much better solution, but regrettably the OP has since
posted to say he wanted to extract the word, not omit it so he has used the
LEFT function.

Regards

Roger Govier


Bob Miller wrote:
or =RIGHT(A1,LEN(A1)-FIND(" ",A1)). It doesn't care how many characters
there are in A1.
Roger Govier Wrote:

Hi
Presumably there is a space between the words. If so, then in another
column
=MID(A1,FIND(" ",A1)+1,255)
Change the 255 if necessary, i.e. if there are likely to be more than
255
characters after the first space.

Regards

Roger Govier


Bhupinder Rayat wrote:
Hi all,

I have a column of cells containing text strings, and I want to omit
the
first word from each cell, which are of varying character lengths. I
can do
it the long winded way using a combination of the text functions (i.e.
left,
mid etc..), but is there a quicker way to omit the first word? The
character
lengths of the first word vary from 4 to about 9 characters. I have
over
10000 cells to check.

Any help would be much appreciated.

Regards,


Bhupinder Rayat