Condense text found in a cell

[TheHobbit81]

Hi All,

I've got a fairly simple problem, |I have a string of text separated by forward slashes "/" with about 5 different things (e.g. fathead-sports/college/cfb/Michigan Wolverines)

I want to have only the last two items listed in a new cell. (e.g. cfb/Michigan Wolverines)

Source Sheet: RawDate
Source Cell: U2

Result Sheet: Result
Result Cell: C2

Help please?

[mojo249]

I've added the following user defined function to your workbook:

Please Login or Register  to view this content.

To use it add the following to your target cell:

=extractstring(Source cell) (for example =extractstring(RawData!U2))

[Maistrye]

Hi,

The macro solution provided will work fine. The only thing to be aware of is it may not work properly if there are fewer than two /'s in the string, or if there are only two /'s and the first one is the very first character.

If you just want a pure formula method, here are a couple possibilities, which depend on how many /'s your data can have:

First, I assume that the tilde (~) character will never appear in this cell. If it does, you would need to use something else that doesn't appear and substitute it for the ~ in the below formulas.

1. If you are only ever going to have 3 /'s in your cell, the following will extract only the last bit:

=RIGHT(U2,LEN(U2)-FIND("~",SUBSTITUTE(U2,"/","~",2)))  

2. If you could have any number of /'s (as long as there are two or more), the following will only extract the last bit:

=RIGHT(U2,LEN(U2)-FIND("~",SUBSTITUTE(U2,"/","~",LEN(U2)-LEN(SUBSTITUTE(U2,"/",""))-1)))  

3. If you can have less than two /'s (ie. 1 or zero), you would need to encompass the above statement in an IF statement.

=IF(LEN(U2)-LEN(SUBSTITUTE(U2,"/",""))<2,U2,RIGHT(U2,LEN(U2)-FIND("~",SUBSTITUTE(U2,"/","~",LEN(U2)-LEN(SUBSTITUTE(U2,"/",""))-1))))  

Hope that helps,
S

[moorelgm]

Hi All,

I've got a fairly simple problem, |I have a string of text separated by forward slashes "/" with about 5 different things (e.g. fathead-sports/college/cfb/Michigan Wolverines)

I want to have only the last two items listed in a new cell. (e.g. cfb/Michigan Wolverines)

Source Sheet: RawDate
Source Cell: U2

Result Sheet: Result
Result Cell: C2

Help please?

Here you go:

Option Explicit

Sub ParseCell()
Dim SplitText As Variant
Dim i As Integer
Dim s As String

SplitText = Split(Sheets("RawData").Cells(2, 21), "/")
i = UBound(SplitText)
s = SplitText(i - 1) & "/" & SplitText(i)
Sheets("Result").Cells(2, 3) = s
End Sub

thanks,

Larry

[shg]

moorelgm,

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[TheHobbit81]

Thanks to both Mojo2498 and Maistrye for your replies.

I'm not very comfortable/ familiar with macros so I'm going to use Mojo's formula based answer.

Works great, used No. 2. Just had to change it to: RawData!U2 and it all worked fine.

Thanks all.

[TheHobbit81]

Thanks to both Mojo2498 and Maistrye for your replies.

I'm not very comfortable/ familiar with macros so I'm going to use Mojo's formula based answer.

Works great, used No. 2. Just had to change it to: RawData!U2 and it all worked fine.

Thanks all.

One last thing before I mark this as 'solved', what if I just wanted the last word? Sorry to be a pest.

[martindwilson]

=trim(right(substitute(RawData!U2," ",rept(" ",50)),25))