Left function with variable length

[vortec]

Hopefully this is an easy one for anyone that actually knows what they are doing.

I am trying to figure out how to extract a Text String from within a cell.

Example:
a1: aaaada, aaabb, aa
a2: ababs, aamnm, aaa
a3: aaabbnhadda, aammm, aacbb
a4: aaabb, annnn, aaaa

I just want to extract everything left of the first ",". The number of characters will vary from cell to cell.

I know it seems simple but I just cant seem to get it right. Any help would be appreciated.

 

[Andrew Poulsom]

That would be:

=LEFT(A1,FIND(",",A1)-1)

 

[DominicB]

Good afternoon vortec

Something like this should work OK :

=LEFT(A1,FIND(",",A1)-1)

HTH

DominicB

 

[kjharve]

If you are using VBA:

You can use the InStr function to return the position of a character within a string. E.g:

 InStr(1, "aaaada, aaabb, aa", ",")

Then use len to return the length of the whole string:

 Len("aaaada, aaabb, aa")

Subtracting the two will give you the number of characters you need to take:

reqChars = InStr(1, "aaaada, aaabb, aa", ",") - Len("aaaada, aaabb, aa")

Then use "left" to return those characters from the string:

 mystring = Left("aaaada, aaabb, aa", reqChars)

Obviously, you don't have to put the string directly into the code. You could put:

 Left(cells(1, 1).value, reqChars)