Find - Replace help help

E

edmos1

so vlookup needs an exact search to look up from:

if I have a value = "aaa-US"
but I am trying to look up a value for "aaa"
I would have to systematically strip off the "-US"
I know I can do a find "-US" or crtl F
but any ideas on how I can create a formlula so I would have
Column A Column B
aaa-US aaa

Thanks D
 
P

Pete_UK

if you always want to take the right hand 3 characters away, then this
formula will do it in B1:

=LEFT(A1,LEN(A1)-3)

Then copy the formula down. Hope this helps.

Pete
 
E

edmos1

What if all my data does not have the "-US"
if I have
aaa-US
bbb-ME
ccc

I would want to display
aaa
bbb
ccc

any thoughts?
 
E

edmos1

What if all my data does not have the "-US"
if I have
aaa-US
bbb-ME
ccc

I would want to display
aaa
bbb
ccc

any thoughts?
 
R

Ron Coderre

Try this:

For text in A1

B1: =IF(COUNTIF(A1,"*-*"),LEFT(A1,FIND("-",A1)-1),A1)

If the text in A1 contains a dash (-), the formula takes the characters
to the left of the dash. Otherwise, it takes all of the text.

Does that help?

Regards,
Ron
 
P

Pete_UK

If you always want the first 3 characters from the left, then the
formula that Chuck gave you will do this, i.e.:

=LEFT(A1,3)

Hope this helps.

Pete
 
E

edmos1

hmmmm ok

how about if it was aaa-41-US

I would like to find aaa-41

I have played around with a nested IF(replace) statement, but I have to
know the placement

could I create a IF (find (replace) or am I over confusing this?

thanks
D
 
E

edmos1

Thank Niek,
I modified yours a bit,

=IF(ISERROR(FIND("US",A1)),A1,LEFT(A1,FIND("US",A1)-2))

seems to work perfectly

I hav not used the iserror to much, looks like I need to brush off my
excel skills a bit

thank you all
D
 
R

Ron Coderre

Ok...I think we can accomodate that:

For text in A1 that may, or may not, contain dashes.

This formula returns all of the characters to the left of the last
instance of a dash:
B1:
=IF(COUNTIF(A1,"*-*"),LEFT(A1,LOOKUP(LEN(A1),FIND("-",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))-1),A1)


Does that do what you want?

Regards,
Ron
 
Top