IF Function

G

gemispence

I'm trying to separate out this data that is stored in one field:

Sari
Panama - Pacific Point

I need to basically write a function that extracts Panama - Pacific
Point out and puts it into a new field but am not having any luck with
the help in Excel for this function. Can anyone help??
 
M

mr_ben

use the text to column tool


use the other section (after pressing the next button) to put the " -
in and hey presto, they are split...

HT
 
G

gemispence

Thanks...Where is the Text to Column Tool though? Also, there i
different types of projects in this field that i need to split out (i
- panama - project is just one of them). :
 
E

Elkar

Try this, assuming your data is in A1:

=MID(A1,FIND(CHAR(10),A1,1)+1,LEN(A1))

HTH,
Elkar
 
M

mr_ben

it's under the "data" menu

as long all have " - " (ie [space]-[space]) then it will wor
 
G

gemispence

That's really helpful thanks, but the problem is that the space between
the two values isn't always in the same spot, and the text to columns
command looks like it needs me to specify where the break should occur.
The problem is that b/c it's not always in the same spot then it will
break words at times.
 
M

mr_ben

gemispence said:
That's really helpful thanks, but the problem is that the space betwee
the two values isn't always in the same spot, and the text to column
command looks like it needs me to specify where the break should occur
The problem is that b/c it's not always in the same spot then it wil
break words at times.
Click to expand...


no, text to columns doesn't "care" where the characters are as long a
the appear in that form, if they appear more than once in that styl
then the text is split in more than 2 columns.

1: make sure you've selected delimited on the first step
2: in the next step in other type - (just found out that it onl
accpets one character :oops: )
3: then you can finish if you accept the next scree
 
G

gemispence

That almost worked, except that some cells are getting separated when I
don't want them to. I think what would be better would be to use some
conditional logic to extract the text that I want from the cells into a
new cell. Which goes back to my initial question. For example, here is
a copy of two of the cells:

" Sari
Allegro Palm"

or

" Sari
Panama - Pacific Point"

or

" Sari
Preserve at Temple Terrace"

so i need to construct an IF statement that says something like if
Preserve at Terrace, insert Preserve at Terrace into a new field. :)
 
Top