Excel 2003 "Search" Function 1

[ekrouse]

I frequently use the "Search" function when extracting text from a cell as part of the "Left", "right" or "Mid" functions.

1) Does anyone know of a way to find the 2nd, 3rd, 4th or 5th occurance of text withing a cell like Lotus 1-2-3 used to allow?

2) Does anyone know of a relatively clean way to find the location of text starting from the right side of the cell (search and find start at the left side... position 1)?

For example, if I wanted to extract the First and Last Name from a cell, it would be helpful to know the location of the first blank in the cell and the last blank in the cell. The first blank I can do, it's the the last blank which stumps me.

For example:

a1 = "Joe Smith"
b1 = "Jane A. Jones"
c1 = "Tom and Sandy Franks"

to extract the first name I would use the formula =left(a1,search(" ",a1,1)-1)

If I could find the last blank then I could use the "right" function to get the last name. Nested search function could work in some cases but in this example the number of spaces varies.

 

[Gavona]

Does anyone know of a relatively clean way to find .....

I am not sure that you would describe this as "clean" or even "relatively clean" but without finding an addin to do the task or writing a user defined function it is the best i can come up with.

You can remove all the spaces in the text with
=SUBSTITUTE(A3," ","")
You can then see how the length has changed
=LEN(A3)-LEN(SUBSTITUTE(A3," ","")
This is the number of spaces in the original text.

You can then substitute a character for the last occurrence of space.
=SUBSTITUTE(A3," ","~",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))

The position of this character can be found using Find
=FIND("~",SUBSTITUTE(A3," ","~",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))

I would then use mid to extract the characters to the right of this:

=MID(A3,FIND("~",SUBSTITUTE(A3," ","~",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))))+1,99)

Gavin

 

[ekrouse]

Thanks Gavin. That's rather creative thinking. The "instance" part of the substitute formula is what I was hoping to find in a "search" function, but your approach solves the issue with just one extra step. Thanks,

-Eric