CODEWITHSUNDEEP
excel
carol
carol

Reputation: 171

copy last value of a cell into a cell of another column

In an Excel column, either there is one value (alphanumerical) that I should copy to the cell of the same row but in another column or there are more than one value separated by space and I should copy the last one in to the other column. The values have the same format, digits 5 or 6 followed by a character and three digits (8 or 9 chars). How can I copy correctly if there is one value in the other column and if there are more than one value the last one on 8 or 9 characters?

Thanks

enter image description here Carol

enter image description here

Upvotes: 1

Views: 1962

Answers (3)

MrSimpleMind
MrSimpleMind

Reputation: 8637

(updated after additional input and chat)

In one shot!

=IF( AND(ISERR(FIND(CHAR(10), A1)), ISERR(FIND(CHAR(32), A1))), A1, 
MID(A1, MAX(IFERROR(FIND("#",SUBSTITUTE(A1,CHAR(10),"#",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))),0),IFERROR(FIND("#",SUBSTITUTE(A1,CHAR(32),"#",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),"")))),0) )+1, LEN(A1)- MAX(IFERROR(FIND("#",SUBSTITUTE(A1,CHAR(10),"#",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),"")))),0),IFERROR(FIND("#",SUBSTITUTE(A1,CHAR(32),"#",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),"")))),0) ) ) 
)

In words...

if no line break or space is found then take the entire cell text, otherwise find the last occurrence of a linebreak (char 10) or space (char 32) and take the text from that index

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37135

Use following VBA sub to copy to another column.

    Public Sub CopyAnotherColumn()
    Dim LastUsedCell As Long
    Dim stCopyItIt As String

        LastUsedCell = Range("A1").End(xlDown).Row
        For Each rCell In Range("A1:A" & LastUsedCell)

            If InStr(1, rCell, " ", vbTextCompare) > 0 Then
                stCopyItIt = StrReverse(rCell.Value)
                 stCopyItIt = Left(stCopyItIt, InStr(1, stCopyItIt, " ", vbTextCompare))
                 ReturnLastWord = StrReverse(Trim(stCopyItIt))
                rCell.Offset(0, 2).Value = ReturnLastWord
            Else
                rCell.Offset(0, 2).Value = rCell.Value
            End If
        Next

    End Sub

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 37135

You can also use this formula.

=IF(ISNUMBER(SEARCH(" ",A1)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),A1)

enter image description here

Upvotes: 0

Related Questions