Trim-Parse troubleshoot (2003)

Topic

New Reply

I’m trying to troubleshoot a piece of code which parses the contents of a column (E) (in reverse) to show the data on the right side of the “/” in column © . If that char is not present, then the contents should be shown.
Can one of you wiz’s help me solve this without applying a UDF or VBA function?
Thanks

Reply | Quote

Replies

Will there ever be more than 3 owners?

Reply | Quote

More than likely…all I want is the last one to the right. Is it necessary to count every instance? I saw a post which mentioned INSTRREV (or something like that). Is it just a VBA function?

Reply | Quote

With more than 3 owners you really need a VBA solution. INSTRREV is indeed a VBA function.

Put this simple function in a standard module:

Function ExtractLast(strValue As String) As String
Dim intPos As Integer
ExtractLast = Trim(Mid(strValue, InStrRev(strValue, “/”) + 1))
End Function

And use like this

=ExtractLast(C2)

Reply | Quote

AWESOME! That rocks! You rock. Saved me hours of work!

Reply | Quote

Just for info, here is a non-VBA approach:
=IF(COUNTIF(C5,”*/*”)=0,C5,TRIM(RIGHT(C5,LEN(C5)-SEARCH(“~#@”,SUBSTITUTE(C5,”/”,”~#@”,LEN(C5)-LEN(SUBSTITUTE(C5,”/”,””))))+1)))
it assumes you will never have ~#@ as part of your text!

Reply | Quote

Another awesome solution!
Had I not had the ability to create or append a module, this would’ve saved my keester.
Cheers to you too!

Reply | Quote

[indent]

---

it assumes you will never have ~#@ as part of your text!

---

[/indent]
You mean, no comic-book cursing?

Reply | Quote

it would be an impressive curse to require all three at once! (I’m pretty sure South Park must have come up with a suitable one…)

Reply | Quote

For a maximum of three owners:

=IF(ISERROR(SEARCH(“/”,IF(ISERROR(SEARCH(“/”,C2)),C2,TRIM(MID(C2,SEARCH(“/”,C2)+1,1000))))),IF(ISERROR(SEARCH(“/”,C2)),C2,TRIM(MID(C2,SEARCH(“/”,C2)+1,1000))),TRIM(MID(IF(ISERROR(SEARCH(“/”,C2)),C2,TRIM(MID(C2,SEARCH(“/”,C2)+1,1000))),SEARCH(“/”,IF(ISERROR(SEARCH(“/”,C2)),C2,TRIM(MID(C2,SEARCH(“/”,C2)+1,1000))))+1,1000)))

Reply | Quote

Here’s an array formula that will find the last “/” no matter how many owners. Enter using Ctrl-Shift plus Enter

=RIGHT(C2,LEN(C2)-MAX(IF(MID(C2,ROW(INDIRECT(“1:”&LEN(C2))),1)=”/”,ROW(INDIRECT(“1:”&LEN(C2))),0)))

Ken

Reply | Quote

So what is the equivalent to the “search” function in VBA?

I was messing around with this thread and tried to make a function that would do the following. The cells contain two columns of data that need to be separated. A cell contains “leftside rightside” with a space between the two vaules.

Dim intLen As Integer
Dim intPos As Integer
intLen = Len(ActiveCell.Value)
intPos = Search(” “, ActiveCell.Value)
ActiveCell.Value = Right(ActiveCell.Value, intLen – intPos)
ActiveCell.Offset(1, 0).Select

This yields “Sub or function not defined” for “Search”. The first question is how do I “search” in VBA? The second question is, is there a better way to take data like this and return one side or the other? I can create a cell formula and create a new column, but I wanted to create a macro that could be used to change the data in place.

Thanks,

Andy

Reply | Quote

You can use InStr to locate text within other text, or you can use Split to create an array separating the elements at specified characters.
HTH.

Reply | Quote

Thanks Rory! Modified bit of code below:

Dim intLen As Integer
Dim intPos As Integer
intLen = Len(ActiveCell.Value)
intPos = InStr(ActiveCell.Value, ” “)
ActiveCell.Value = Right(ActiveCell.Value, intLen – intPos)
ActiveCell.Offset(1, 0).Select

Reply | Quote

You could also use

Dim intPos As Integer
intPos = InStr(ActiveCell, ” “)
ActiveCell = Mid(ActiveCell, intPos + 1)
ActiveCell.Offset(1, 0).Select

It would also be possible to avoid selecting cells; this is usually more efficient, but we’d need to know more about how your code loops.

Reply | Quote