Thread: maintain only first word in a cell

Everyone,

I am having a problem with achieving the following:

I have several rows in a column, wheras in each cell the conent is something like this

testing corp road 25, NY NY
TESTING inc road 35 DC
TESting ltd road 25

I now want to take the first word in the first row and past it in that specific first row and every other row. Hence, end result would be

testing
testing
testing

I would also be fine with just keeping the first word in every row, if you consider that making more sense (thus, I would skip the pasting into the first row and every other row). In that case it would look something like this

testing
TESTING
TESting

It would not look as nice, but I could life with that.

Please note that the number of rows can vary (anything between 50 to 500). But I am confident that I can get that done myself (without selecting the cells. I will try to make a loop).

As always, I would be more than grateful for any suggestions how to program a macro for this in VB.

thank you in advance.
regards,
tony

Here, try this:

=LOWER(IF(ISERROR(FIND(" ";A1)-1);A1;LEFT(A1;FIND(" ";A1)-1)))

Although the two answers are OK they do not answer to the requirement

I now want to take the first word in the first row and past it in that specific first row and every other row.

This is circular references and if you want to keep the calculation mode in Automatic, you must go to VBA., which is mentioned by patentprio twice.

My question is:

Please note that the number of rows can vary (anything between 50 to 500)

Should all of these be replaced with the first word in the first cell of these 50-500? Are there blank cells in between? What happens to those?

The following program does that.

Select al the cells (type A1:A500 in the namebox) and run the code

Sub replace1()

Dim SplitArray

With Selection
  SplitArray = Split(.Cells(1, 1).Value, " ")
  .Value = SplitArray(0)
End With
  
End Sub

Here is attempt of a solution wich doesn't working...

I hope DonkeyOte can help since my version is adapted his (working, of course) solution of this:

Converting words to UPPER letters:

Public Sub ConvertUpper()
With Range("A1:A1000")
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),UPPER(" & .Address & "),REPT(" & .Address & ",1))")
End With
End Sub

Now.. I've tried just to adopt it for extracting first word and make it LOWER:

Public Sub FirstWord2()
With Range("A1:A1000")
    .Value = Evaluate("IF(ISERROR(FIND("" ""," & .Address & ")-1),LOWER(" & .Address & "),LOWER(LEFT(" & .Address & ",FIND("" ""," & .Address & ")-1))")
    
End With
End Sub

Howeve, it doesn't working...
JBeaucaire aso tried to help me with this solution, but can't get it work either:

Public Sub FirstWord()
Dim RNG As Range
Set RNG = Range("A:A").SpecialCells(xlCellTypeConstants)
Debug.Print RNG.Address

    With RNG
        .Value = Evaluate("IF(ROW(1:" & .Address & "),LOWER(IF(ISERROR(FIND("" ""," & _
            .Address & ")-1)," & .Address & ",LEFT(" & .Address & ",FIND("" ""," & .Address & ")-1)))")
    End With

End Sub

DonkeyOte, your move for teaching lesson

Zbor,

I do not understand your attempt. Have you tested my code. Where is it not fullfilling the OP's requirements? ... and why the UPPER/LOWER. Don't see that in the OP's request.

Take the first word in the first row and place that word in all of the other rows.

I have no doubt in your solution.
I just can't figure why this doesn't work (That's the question in my post).

And about LOWER (not UPPER; that was solution somewhere else) I get from this:

Hence, end result would be

testing
testing
testing

...

testing
TESTING
TESting

It would not look as nice, but I could life with that.

Althought, I don't need to be right.

Ricardo, your loop solution is most likely just fine, I didn't test it because I trusted it worked.

The EVALUATE() trick is one we're still trying to get adept at applying, it allows an entire range of values to be processed all at once pretty much instantaneously. It's an awesome technique. But the syntax for coercing Excel to do it is nothing short of an Olympic hurdle.

I've been fiddling with it for a couple of months and have successfully applied it to about 3 of the 10 things I've tried. In one that it worked, it was doing 50,000 value changes in less than 1 second, without turning off screenupdating.

So, this is worth the effort. So far, Don is the only one here that seems to have mastered it, I keep coming up short on the application.

Sorry for not understanding the underlying reason. It is worth the challenge for efficiency reasons.

Evaluation is not my specialty, but is this what you need:

Sub Eval()

With Range("A1:A13")
  .Value = Evaluate("Left(" & .Address & ", Find("" "", " & .Address & ", 1) - 1)")
End With

End Sub

Still doesn't work for me...

From:

fdsfd fsfsd
fds f dsf
gfds r rgd
rg
44t4
ferfer ger
43
fref rfe r
tef 
  rfd
4t4 4
t 4t4 
34rt43  4

It gives me:

fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd
fdsfd

Same as both (mine and JB) solutions in post #7

Guys - interesting as this is (evaluate) the use of Evaluate in terms of zbor's example has little / no bearing on OP's question (at least not as I see it).

I interpret OP's request as "applying first word from first cell to all subsequent cells"

Ricardo solved this without iteration and I see no value in using Evaluate here (at all) - discussing it in the context of OP question doesn't really add any value and may confuse.

If there are questions pertaining to use of Evaluate (ie generating variable results without iteration) why not kick off your own thread ?

EDIT:

And FWIW Ricardo has also solved OP request using Evaluate

Then I would use this (I stepped away from the evaluate)

Sub Eval()

Dim MyWs As Worksheet
Dim MyRng As Range, Ccell As Range
Dim SplitArray

Set MyWs = Worksheets("Sheet1")

With MyWs
  Set MyRng = .Range(.Cells(1, 1), .Cells(.Cells.Rows.Count, 1).End(xlUp))
End With

SplitArray = Split(MyRng.Cells(1, 1).Value, " ")

For Each Ccell In MyRng
  If Not IsEmpty(Ccell) Then Ccell = SplitArray(0)
Next

End Sub

Note that MyRng starts with Cells(1,1) which is cell A1 !