Omit the first word from a cell contain text

[Bhupinder Rayat]

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

 

[Roger Govier]

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]

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.

 

[Bruno Campanini]

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

 

[Ian]

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)

 

[Roger Govier]

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

 

[Bruno Campanini]

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 & _
"Procedure: FirstWordOnly" & vbCrLf & _
ThisWorkbook.FullName
Resume Exit_Sub

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

Ciao
Bruno

 

[Bob Miller]

or =RIGHT(A1,LEN(A1)-FIND(" ",A1)). It doesn't care how many character
there are in A1.

 

[Roger Govier]

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