?
Solved

Bring the "Right" portion of a cells value

Posted on 2011-04-26
5
Medium Priority
?
180 Views
Last Modified: 2012-05-11
If the cell in column A starts with "Section code" and the word does not ends in "-part one", then bring the last word starting from right to left.  Place the answer in column c, per row.

Thanks,
JP bring-the-last-word-in-a-cell.xls aa
0
Comment
Question by:easycapital
  • 2
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 1000 total points
ID: 35468927
Here you go:

=IF(AND(LEFT(A2,12)="Section Code",RIGHT(A2,9)<>"-part one"),RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),"")

Please check the attached example.

jppinto
bring-the-last-word-in-a-cell.xls
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 1000 total points
ID: 35468931
Paste this code in a module and run it :)

Const StartText  As String = "SECTION CODE"
Const NotEndText  As String = "-PART ONE"

Sub Sample()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    
    Set ws = Sheets("Sheet1")
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
        If UCase(Left(Trim(Range("A" & i).Value), 12)) = StartText And _
        UCase(Right(Trim(Range("A" & i).Value), 9)) <> NotEndText Then
            Range("C" & i).Value = Replace(Replace(UCase(Range("A" & i).Value), _
            StartText, ""), NotEndText, "")
        End If
    Next i
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35468940
xover

Sid
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35468945
:) LOLOL
0
 

Author Closing Comment

by:easycapital
ID: 35468992
Great! Right on target.  Going with Jppinto, as it a non-macro solution.  Should have specified it.

Thank you both,
JP
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question