Solved

# Bring the "Right" portion of a cells value

Posted on 2011-04-26
Medium Priority
180 Views
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
0
Question by:easycapital
• 2
• 2

LVL 33

Accepted Solution

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," ",""))))),"")

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

LVL 30

Assisted Solution

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
``````

Sid
0

LVL 30

Expert Comment

ID: 35468940
xover

Sid
0

LVL 33

Expert Comment

ID: 35468945
:) LOLOL
0

Author Closing Comment

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

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.
###### Suggested Courses
Course of the Month15 days, 19 hours left to enroll