The situation is: I have a about 15000 lines of data that needs to be cleansed. I need to change the Item description from e.g
Uncleansed = OFFSET TIB TRAY NEUTRAL ADAPTOR
Cleansed = ADAPTOR OFFSET TIB TRAY NEUTRAL is there a way I can do this automatically? with a macro?
thanks
bigdee008@yahoo.com,
Welcome to the forum!
If all you need is to put the last word as the first word, either of these should work for you:
Formula solution:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&" "&SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"")
Macro solution:
Sub tgr() With Range("A1", Cells(Rows.Count, "A").End(xlUp)) .Offset(, 1).Formula = "=TRIM(RIGHT(SUBSTITUTE(A" & .Row & ","" "",REPT("" "",99)),99))&"" ""&SUBSTITUTE(A" & .Row & ","" ""&TRIM(RIGHT(SUBSTITUTE(A1,"" "",REPT("" "",99)),99)),"""")" .Offset(, 1).Value = .Offset(, 1).Value End With End Sub
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks