Excel: Find the 2nd Text in a String 1

[Salut39]

Hi Everyone,

I have a text which I want to break with the formula:

Totsa Total Oil Trading SA, Geneva, Switzerland
=LEFT($C40,FIND(",",$C40)-1) - Breaks the first bit.

I know about Text to column thing, but I was wondering if you can search for the 2nd comma in a string using FIND?

Thanks
Yuri

 

[HarleyQuinn]

You could use:

=LEFT($C40,FIND(",",$C40,FIND(",",$C40)+1)-1)

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Salut39]

Thanks Andy, with your help:

1. =LEFT($C40,FIND(",",$C40)-1)

2. =MID($C40,FIND(",",$C40)+1,LEN($C40)-FIND(",",$C40)+1-(LEN($C40)-FIND(",",$C40,FIND(",",$C40)+1)+2))

3. =RIGHT($C40,LEN($C40)-FIND(",",$C40,FIND(",",$C40)+1)-1)

Yuri

 

[Loomah]

obviously the solution above is the way to go but i'm bored beyond reason so started to look at alternatives.....

1:
=LEFT(SUBSTITUTE(A1,",","|",2),FIND("|",SUBSTITUTE(A1,",","|",2))-1)

which is silly but nothing in comparison to

2:
=REPLACE(A1,FIND(",",A1,FIND(",",A1)+1),LEN(A1)-FIND(",",A1,FIND(",",A1)+1)+1,"")

just for the hell of it!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[HarleyQuinn]

Yuri - glad I could help, thanks for the star

Loomah - boredom sometimes breeds the most creative of solutions

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Salut39]

Great! It nice to have diferent options.

Yuri

 

[Loomah]

still bored and into the realms of silliness now...

=splitbysecondcomma(A1)

where splitbysecondcomma is

Function SplitBySecondComma(r As Range) As Variant
Dim sA() As String
    If r.Cells.Count > 1 Then
        SplitBySecondComma = CVErr(xlErrRef)
    Else
        sA = Split(r, ",")
        SplitBySecondComma = Join(Array(sA(0), sA(1)), ",")
    End If
End Function

and i don't even get a tv to watch at work ¦;}

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fumei]

You ARE bored. Wow. I can quite relate actually.

Gerry

 

[SkipVought]

Well, here it is, FRIDAY. Need I say more.

I took Loomah's excellent product of boredom and mused, "What if you could ALSO specify a Nbr indicating which delimiter to key on and the symbol to key on and whether to return the FIRST part or not???"

Function SplitBy(r As Range, nbr As Integer, sym As String, Optional bFirst As Boolean = True) As Variant
'basic code by Loomah
'addl parameters by SkipVought
    Dim sA() As String, i As Integer, bProcess As Boolean
    If r.Cells.Count > 1 Then
        SplitBy = CVErr(xlErrRef)
    Else
        sA = Split(r, ",")
        bProcess = bFirst
        For i = 0 To UBound(sA)
            Select Case i
                Case Is < nbr
                    If bFirst Then
                        SplitBy = SplitBy & sA(i)
                    End If
                Case Else
                    If Not bFirst Then
                        SplitBy = SplitBy & sA(i)
                        bProcess = True
                    Else
                        bProcess = False
                        Exit For
                    End If
            End Select
            If bProcess Then SplitBy = SplitBy & sym
        Next
    End If
    SplitBy = Trim(Left(SplitBy, Len(SplitBy) - 1))
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

aaaarrrrggggghhhhhhhhh
wasn't going to bother posting this but as i'd already done it and skip's possibly opened the floodgates for even more extreme solutions.....
what if.....

...you wanted to specify the delimiter to look for, the delimiter to replace it with and the number of occurrances to take to the solution.....

Function LeftByDelimiter(r As Range, Optional OldDelim As String = ",", _
                            Optional NewDelim As String = ",", Optional count As Integer = 2) As Variant
Dim sA() As String
Dim iCount As Integer
    If r.Cells.count > 1 Then
        LeftByDelimiter = CVErr(xlErrRef)
    Else
        sA = Split(r, OldDelim)
        For iCount = 0 To count - 1
        LeftByDelimiter = Join(Array(LeftByDelimiter, sA(iCount)), NewDelim)
        Next
    End If
LeftByDelimiter = Right(LeftByDelimiter, Len(LeftByDelimiter) - Len(NewDelim))
End Function

applied to:
srdfgsr, iowauehg, ipurehf, qw, qwerf, qwerqw, qwer, qwer ,qwer, frqwertqw, qtret eqwrwq,wer

=LeftByDelimiter(A1,",","++",5) gives
srdfgsr++ iowauehg++ ipurehf++ qw++ qwerf

only returns a left string

certainly is friday though - only 2 1/2 hours to the weekend!

happy daze

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[kwbMitel]

I developed Harley's Version some time back to look for spaces in a name. Works fine if the total is 1 or 2

If you want a version that looks for the last of a specific character (however many they may be) then this is the formula for you.

It substitutes a "Special Char" (in this case ~ tilde) in the last position found and then finds that character.

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.