Right, Left, Mid

[bschulze]

I am looking for a right, left, mid function that goes up a a certain variable. For instance, I have a cell that needs to be broken out into two cells, The seperation indicator is "#". I have been playing around with it and can not get this to work. I know that it is simple but my brain is not functioning today.

Example:
Column A Column B
111#2222 I need 111 2222
3#1 I need 3 1


Sorry I am wasteing a post on this.

 

[barry houdini]

Try

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

and

=REPLACE(A1,1,FIND("#",A1),"")

or use Data > Text to Columns with # as delimiter

 

[Asala42]

Data/Text to Columns can do it if you don't mind a non-formula approach.

Formula Wise I'd go with a left(find) kind of thing:

=LEFT(A2,FIND("#",A2)-1)
=RIGHT(A2,LEN(A2)-LEN(B2)-1)

 

[bschulze]

it works great...thanks for your help