Trim first word in cell

[xbr088]

Hi gentlemen,

I would like to ask,

If i have column F, each row has 1 code in cell (different length), but sometimes after this code is many spaces and some other words or codes...

How to left only first world in cell by macro ?

thanks

[tigeravatar]

xbr088,

Something like this should work for you:

myVar = Split(Trim(Range("F1").Text), " ")(0)
MsgBox myVar

[xbr088]

Tigeravatar,

yes, this i need, but your formula only shows me first word in cell in popup window.

But how to left in whole column F only first word in row's cell ?

[Norie]

What separates the code from the rest of the cell contents?

Is it a space?

[xbr088]

yes, spaces are separating first word from other words in cell ( different amount of spaces)

I am going to try upper solution. moment

[tigeravatar]

The code I provided is a sample to show you how to get what you requested. You need to apply it to your current code. I imagine you have a loop setup already that goes through column F. If you don't, here is some more example code:

Sub tgr()
    
    Dim rngF As Range
    Dim FCell As Range
    Dim arrCodes() As Variant
    Dim CodeIndex As Long
    
    Set rngF = Range("F1", Cells(Rows.Count, "F").End(xlUp))
    ReDim arrCodes(1 To rngF.Rows.Count)
    
    For Each FCell In rngF.Cells
        CodeIndex = CodeIndex + 1
        arrCodes(CodeIndex) = Split(Trim(FCell.Text), " ")(0)
    Next FCell
    
    MsgBox Join(arrCodes, Chr(10))
    
    Set rngF = Nothing
    Set FCell = Nothing
    Erase arrCodes
    
End Sub

[xbr088]

Hmm, small confusing. i tried both of your codes. and both made same. Extract first word and shows me in popup window. That's great, but after i close this popup, everything stay same, nothing is trimmed. Where i made fault ?

Sorry for my dummy question But i need only keep first word in all row's cells in F column, and all others to delete (means others spaces, words)

[tigeravatar]

Here's an alternate way to do the same thing, but it would be harder to edit and is probably not very easy to follow/understand:

Sub tgr_v2()
    
    With Range("F1", Cells(Rows.Count, "F").End(xlUp))
        .Value = Evaluate("Index(Trim(Left(Substitute(Trim(" & .Address & "),"" "",Rept("" "",99)),99)),)")
    End With
    
End Sub

[tigeravatar]

Oh, you want to replace what is in column F with the codes that get retrieved, you can do that like so:

Sub tgr()
    
    Dim rngF As Range
    Dim FCell As Range
    Dim arrCodes() As Variant
    Dim CodeIndex As Long
    
    Set rngF = Range("F1", Cells(Rows.Count, "F").End(xlUp))
    ReDim arrCodes(1 To rngF.Rows.Count)
    
    For Each FCell In rngF.Cells
        CodeIndex = CodeIndex + 1
        arrCodes(CodeIndex) = Split(Trim(FCell.Text), " ")(0)
    Next FCell
    
    rngF.value = Application.Transpose(arrCodes)
    
    Set rngF = Nothing
    Set FCell = Nothing
    Erase arrCodes
    
End Sub

[xbr088]

Both solutions works, i will use shorter. thanks

[xbr088]

Ou thats it :-)

Simple question, but so difficult macro code. But anyhow is usefull. solved

THANX